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 @asPemda = 1, @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:
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 @asPemda = 1, @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:
