4 Minutes      November 11, 2021      Programming Programming/SQL

Generate Date Series and Joining to Another Table in SQL Server

There are cases when you’ll need to generate sample data or a data series of records to join in SQL Server for reporting purposes. A data series is a collection of data that begins at one point and ends at another. In SQL Server, it’s very simple to build date series, execute store procedure, aggregate them with sum and group clauses, and then join them to other tables.

Here’s an example that I commonly use:

-- parameter variables
declare @tglmulai date = '2021-01-01'; -- start date
declare @tglselesai date = '2021-01-05'; -- end date

-- variable for storing data returned from store procedure
declare @bb table(ID int, TglBukti date, NoBukti varchar(50), Ket varchar(255), D money, K money, Sal money);

-- execute store procedure
insert into @bb exec akt.spReportBukuBesar @Akun = '110101010002', @T1 = @tglmulai, @T2 = @tglselesai;

with daterange (tgl) as (
	-- generate date series from start date to end date
	select @tglmulai tgl
    union all
    select dateadd(dd, 1, tgl) from daterange where tgl <= dateadd(dd, -1, @tglselesai)
),
bukubesar as (
	-- aggregate data
	select
    	TglBukti tgl, sum(D) debet, sum(K) kredit,
        (select sum(D - K) from @bb b where a.TglBukti >= b.TglBukti) saldo -- get saldo, debet - kredit
    from @bb a group by TglBukti
)

-- done
select
	a.tgl,
    datename(dw, a.tgl) hari, -- day name
    b.debet, b.kredit, b.saldo
from daterange a left join bukubesar b on a.tgl = b.tgl

Sample result:

Date Series and Join in SQL Server


Extended version for my case :) add comparasion with bank account:

-- parameter variables
declare @tglmulai date = '2021-01-01'; -- start date
declare @tglselesai date = '2021-01-31'; -- end date
declare @rekening varchar(3) = '138' -- kode rekening koran (100, 103, 138)

-- set akun based by rekening koran
declare @akunrekening varchar(12) = case
	when @rekening = '103' then '110101010001'
	when @rekening = '138' then '110101010002'
    else '110101010003'
end;

-- variable for storing data returned from store procedure
declare @bb table(ID int, TglBukti date, NoBukti varchar(50), Ket varchar(255), D money, K money, Sal money);

-- execute store procedure
insert into @bb exec akt.spReportBukuBesar @Akun = @akunrekening, @T1 = @tglmulai, @T2 = @tglselesai;

with daterange (tgl) as (
	-- generate date series from start date to end date
	select @tglmulai tgl
    union all
    select dateadd(dd, 1, tgl) from daterange where tgl <= dateadd(dd, -1, @tglselesai)
),
bukubesar as (
	-- aggregate data
	select
    	TglBukti tgl, sum(D) debet, sum(K) kredit,
        (select sum(D - K) from @bb b where a.TglBukti >= b.TglBukti) saldo -- get saldo, debet - kredit
    from @bb a group by TglBukti
),
rekeningkoran as (
	-- rekening koran from BIBS
	select
        cast(right(Tgl, 4) + '/' +
        case
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Januari' then '01'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Februari' then '02'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Maret' then '03'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'April' then '04'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Mei' then '05'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Juni' then '06'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Juli' then '07'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Agustus' then '08'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'September' then '09'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Oktober' then '10'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'November' then '11'
            else '12'
        end
        + '/' + left(Tgl, 2) as date) tgl,
        sum(DebetKredit) saldo
    from akt.RekeningKoran
    where Rekening = @rekening and cast(right(Tgl, 4) + '/' +
        case
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Januari' then '01'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Februari' then '02'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Maret' then '03'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'April' then '04'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Mei' then '05'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Juni' then '06'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Juli' then '07'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Agustus' then '08'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'September' then '09'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'Oktober' then '10'
            when left(right(Tgl, len(Tgl) - 3), len(Tgl) - 8) = 'November' then '11'
            else '12'
        end
        + '/' + left(Tgl, 2) as date) between @tglmulai and @tglselesai
    group by Tgl
)

-- done
select
	a.tgl,
    datename(dw, a.tgl) hari, -- day name
    b.debet, b.kredit, b.saldo,
    case
    	when c.saldo is null then null -- no transaction in rekening koran
        else (select sum(d.saldo) from rekeningkoran d where a.tgl >= d.tgl) -- get true saldo of rekening koran
    end saldorekening
from daterange a
left join bukubesar b on a.tgl = b.tgl
left join rekeningkoran c on a.tgl = c.tgl
order by a.tgl
option (maxrecursion 0) -- fix error: the statement terminated. the maximum recursion 100 has been exhausted before statement completion.

Extended result:

Date Series and Join in SQL Server Extended

Arti/Makna Efisiensi