Zapewne każdy kto przygotowywał kiedyś raport oparty o dane pobierane z bazy MSSQL natrafił na problem nieciągłości danych. Ile razy zdarzyło Ci się drogi czytelniku, że dane pobrane do raportu nie zawierały wszystkich dni miesiąca, czy miesięcy w roku? Czy można coś na to zaradzić nie odwołując się do żadnych „sztuczek” związanych z konkretnym silnikiem raportów? Odpowiedzią może być mechanizm rekurencji, o którym już pisałem w innym wpisie.

Aby lepiej zrozumieć istotę problemu przygotujmy sobie plac zabaw. Zacznijmy od utworzenia tabeli.

create table dbo.transakcje
(
	idTransakcji int identity (1,1) primary key clustered,
	dataTransakcji date,
	nazwa nvarchar(100),
	kwota money
);

Teraz wypełnijmy naszą tabelkę przykładowymi danymi. Nie będziemy zbytnio kombinować – dla celów prezentacyjnych wystarczy kilka transakcji z jednego miesiąca.

insert into dbo.transakcje (dataTransakcji, nazwa, kwota) values
('20190501', 'Sprzedaż komponentu', 170.23),
('20190503', 'Sprzedaż zestawu', 1478.00),
('20190504', 'Sprzedaż usługi', 100.00),
('20190507', 'Usługa serwisu', 269.99),
('20190510', 'Sprzedaż podzespołu', 367.40),
('20190510', 'Sprzedaż usługi', 300.00),
('20190513', 'Serwis pogwarancyjny', 430.00),
('20190515', 'Sprzedaż zestawu', 2670.50),
('20190516', 'Sprzedaż usługi montażu', 400.00),
('20190520', 'Sprzedaż komponentu', 1700.00),
('20190521', 'Sprzedaż usługi serwisowej', 300.00),
('20190523', 'Sprzedaż zestawu', 3470.70);

Jak widać dane nie są ciągłe. Gdybyśmy teraz chcieli pobrać sobie zestawienie sumy kwot dla poszczególnych dni w miesiącu mogłoby to wyglądać następująco.

-- Paramtry raportu (rok i miesiąc)
declare @rok int = 2019;
declare @miesiac int = 5;

select dataTransakcji, sum(kwota) as sumaTransakcji
from dbo.transakcje
where year(dataTransakcji) = @rok
and month(dataTransakcji) = @miesiac
group by dataTransakcji
order by dataTransakcji;

W efekcie naszym oczom ukaże się następujący widok.

Naszym celem jest jednak przedstawienie dat w sposób ciągły, który będzie uwzględniał wszystkie dni w miesiącu. Aby to osiągnąć konieczne będzie odwołanie się do wspomnianej rekurencji.

Na początek skupmy się na przygotowaniu kolekcji wszystkich dni w miesiącu. Posłuży nam do tego następujące zapytanie.

declare @rok int = 2019;
declare @miesiac int = 5;

with dni as (
	select datefromparts(@rok, @miesiac, 1) as dzien
	union all
	select dateadd(day, 1, dzien) as dzien
	from dni
	where dateadd(day, 1, dzien) < dateadd(month, 1, datefromparts(@rok, @miesiac, 1))
)
select *
from dni

Wynikiem działania tego zapytania będzie następująca kolekcja wierszy.

No dobrze, ale jak to właściwie działa? Prześledźmy to krok po kroku.

W poprzednim wpisie dotyczącym rekurencji w TSQL wyjaśniałem ogólną zasadę działania. Tutaj jest bardzo podobnie. Przede wszystkim również mamy sekcję CTE, której tutaj nadałem nazwę dni. Wewnątrz niej mamy również dwa zapytania połączone wyrażeniem union all. O ile jednak w poprzednim wpisie skupiałem się na hierarchii to tutaj z niczym takim nie mamy jednak do czynienia. Przyjrzyjmy się po kolei poszczególnym składowym naszego CTE.

select datefromparts(@rok, @miesiac, 1) as dzien

Powyższe zapytanie, jak nietrudno się domyślić, zwraca nam pierwszy dzień wskazanego miesiąca. Ciekawsze jest jednak drugi zapytanie.

select dateadd(day, 1, dzien) as dzien
from daty
where year(dateadd(day, 1, dzien)) = @rok and month(dateadd(day, 1, dzien)) = @miesiac

To zapytanie realizuje aż dwa zadania. Po pierwsze realizuje dodawanie kolejnego dnia do wyniku. Drugim działaniem jest pilnowanie aby nie generowanie kolejnych dni nie wyszło poza zakres miesiąca.

Spróbujmy teraz połączyć oba zapytania w jedno pamiętając, że dane z raportu będziemy dobudowywać do szkieletu opartego o kolekcję dni.

with dni as (
	select datefromparts(@rok, @miesiac, 1) as dzien
	union all
	select dateadd(day, 1, dzien) as dzien
	from dni
	where year(dateadd(day, 1, dzien)) = @rok and month(dateadd(day, 1, dzien)) = @miesiac
)
select d.dzien, sum(kwota) as sumaTransakcji
from dni as d
left join dbo.transakcje as t on t.dataTransakcji = d.dzien
group by d.dzien
order by d.dzien;

W efekcie działania tego zapytania naszym oczom ukaże się następująca tabela.

Czy czegoś jej brakuje? Moim zdaniem w oczy kłują te mało estetyczne wartości NULL. Aby się ich pozbyć podpowiemy zapytaniu jak sobie poradzić za pomocą funkcji coalesce.

with dni as (
	select datefromparts(@rok, @miesiac, 1) as dzien
	union all
	select dateadd(day, 1, dzien) as dzien
	from dni
	where year(dateadd(day, 1, dzien)) = @rok and month(dateadd(day, 1, dzien)) = @miesiac
)
select d.dzien, coalesce(sum(kwota), 0) as sumaTransakcji
from dni as d
left join dbo.transakcje as t on t.dataTransakcji = d.dzien
group by d.dzien
order by d.dzien;

Tak przygotowane zestawienie można już ze spokojnym sumieniem pokazać w formie raportu 🙂

Jak widać rekurencja w TSQL to nie tylko narzędzie do odzwierciedlania hierarchii. To również świetny sposób na budowanie pętli w ramach zwykłych zapytań do pobierania danych.