Wiem, że tego typu raport da się łatwo uzyskać w Comarch Business Intelligence. Nie każdy ma jednak Comarch BI.
Wiem też, że istnieje w SQL składnia PIVOT, która powinna sobie z tym poradzić. Ona nie działa w mojej opinii.
To opracowanie adresuję do osób, które samodzielnie tworzą raporty w SQL i chcieliby rozszerzyć swoją wiedzę.
Co to jest CTE
Common Table Expression (CTE) jest to specyficzny sposób tworzenia zapytań wprowadzony przez Microsoft po raz pierwszy w wersji MS SQL 2005.
Umożliwia tworzenie w ramach jednego zapytania SQL dowolnej ilości zapytań, które są przechowywane w pamięci oraz możliwości odwołania się do tych zapytań w obrębie tego samego zapytania. Taka konstrukcja zapewnia kilka korzyści:
1. Upraszcza kod eliminując potrzebę korzystania z T-SQL.
Przykładowo zamiast:
SELECT TrN_KntNumer, Kwota=SUM(TrN_NettoR) INTO #ObrotyKontrahentowMiesieczne FROM CDN.TraNag WHERE TrN_RokMiesiac=201405 GROUP BY TrN_KntNumer SELECT Knt_GIDNumer, Knt_Akronim, Knt_Nazwa1 INTO #WybraniKontrahenci FROM CDN.KntKarty WHERE Knt_Miasto='Łódź' SELECT Knt_Akronim, Knt_Nazwa1, Kwota=ISNULL(Kwota,0.00) FROM #WybraniKontrahenci LEFT JOIN #ObrotyKontrahentowMiesieczne ON Knt_GIDNumer=TrN_KntNumer DROP TABLE #ObrotyKontrahentowMiesieczne DROP TABLE #WybraniKontrahenci
Można zrobić:
WITH WybraniKontrahenci AS (SELECT Knt_GIDNumer, Knt_Akronim, Knt_Nazwa1 FROM CDN.KntKarty WHERE Knt_Miasto='Łódź'), ObrotyKontrahentowMiesieczne AS (SELECT TrN_KntNumer, Kwota=SUM(TrN_NettoR) FROM CDN.TraNag WHERE TrN_RokMiesiac=201405 GROUP BY TrN_KntNumer) SELECT Knt_Akronim, Knt_Nazwa1, Kwota=ISNULL(Kwota,0.00) FROM WybraniKontrahenci LEFT JOIN ObrotyKontrahentowMiesieczne ON Knt_GIDNumer=TrN_KntNumer
2. Optymalizuje szybkość działania zapytań.
Zamiast:
SELECT TrN_GIDNumer, WynikFunkcji=dbo.bardzoSkomplikowanaFunkcja (TrN_GIDNumer) FROM CDN.TraNag WHERE TrNRokMiesiac=201405 AND (dbo.bardzoSkomplikowanaFunkcja(TrN_GIDNumer)=1 OR dbo.bardzoSkomplikowanaFunkcja(TrN_GIDNumer)=2)
Można zrobić:
WITH ZapytanieWyliczajaceFunkcje AS SELECT TrN_GIDNumer, WynikFunkcji=dbo.bardzoSkomplikowanaFunkcja (TrN_GIDNumer) FROM CDN.TraNag WHERE TrN_RokMiesiac=201405 SELECT * FROM ZapytanieWyliczajaceFunkcje WHERE WynikFunkcji=1 OR WynikFunkcji=2
3. Umożliwia wielopoziomowe rekurencyjne tworzenie zapytań bazując na poprzednich zapytaniach.
Można zrobić:
WITH ObrotyWszystkie AS (SELECT TrN_KntNumer, Rok=TrN_RokMiesiac/100, Miesiac=TrN_RokMiesiac%100, Kwota=TrN_NettoR FROM CDN.TraNag WHERE TrN_RokMiesiac=201405), ObrotyMiesiacami AS (SELECT TrN_KntNumer, Rok, Miesiac, Kwota=SUM(Kwota) FROM ObrotyWszystkie GROUP BY TrN_KntNumer, Rok, Miesiac), ObrotyLatami AS (SELECT TrN_KntNumer, Rok, Kwota=SUM(Kwota) FROM ObrotyMiesiacami GROUP BY TrN_KntNumer, Rok) SELECT * FROM ObrotyLatami
Łącząc te trzy cechy można więc tworzyć zapytania proste i wydajne, a nawet rekurencyjne (choć z narzuconą liczbą rekurencji).
Korzystając ze składni CTE stworzymy w kolejnych krokach raport wyliczający obroty na dokumentach handlowych (w uproszczeniu zakup i sprzedaż) z podziałem na kwartały w układzie tabeli przestawnej.
Krok 1 – podstawowe dane dla raportu
Na pierwszym poziomie zestawienia znajdą się wszystkie niezbędne dane w klasycznym kolumnowym układzie tabeli. W każdym kolejnym kroku będziemy wyświetlać nowe wyniki.
WITH Krok1 AS (SELECT DokTyp=OB_Skrot, Rok=TrN_RokMiesiac/100, Miesiac=trn_RokMiesiac%100, Obrot=TrN_NettoR+TrN_NettoP FROM CDN.TraNag JOIN CDN.Obiekty ON TrN_GIDTyp=OB_GIDTyp WHERE TrN_TrNRok=2014) SELECT * FROM Krok1
Krok 2 – wyliczenie pola Kwartał
Numer kwartału nie jest zapisany w tabeli bezpośrednio – trzeba wyliczyć go na podstawie numeru miesiąca. Można to wprawdzie było zrobić już w kroku pierwszym, ale skomplikowałoby to kod i dołożyło procesorowi dodatkowej pracy z wielokrotnym obliczaniem miesiąca w warunkach CASE. Zróbmy to w kroku 2:
WITH Krok1 AS (SELECT DokTyp=OB_Skrot, Rok=TrN_RokMiesiac/100, Miesiac=trn_RokMiesiac%100, Obrot=TrN_NettoR+TrN_NettoP FROM CDN.TraNag JOIN CDN.Obiekty ON TrN_GIDTyp=OB_GIDTyp WHERE TrN_TrNRok=2014), Krok2 AS (SELECT DokTyp, Rok, Kwartal=CASE WHEN Miesiac BETWEEN 1 AND 3 then 1 WHEN Miesiac BETWEEN 4 AND 6 then 2 WHEN Miesiac BETWEEN 7 AND 9 then 3 WHEN Miesiac BETWEEN 10 AND 12 then 4 END, Miesiac, Obrot FROM Krok1) SELECT * FROM Krok2
Krok 3 – agregacja danych
Dane do pól mamy już wyliczone, a zestaw danych zawężony. Wykonanie agregacji w tym kroku będzie więc znacznie szybsze. Dodajemy agregację w kroku 3:
WITH Krok1 AS (SELECT DokTyp=OB_Skrot, Rok=TrN_RokMiesiac/100, Miesiac=trn_RokMiesiac%100, Obrot=TrN_NettoR+TrN_NettoP FROM CDN.TraNag JOIN CDN.Obiekty ON TrN_GIDTyp=OB_GIDTyp WHERE TrN_TrNRok=2014), Krok2 AS (SELECT DokTyp, Rok, Kwartal=CASE WHEN Miesiac BETWEEN 1 AND 3 then 1 WHEN Miesiac BETWEEN 4 AND 6 then 2 WHEN Miesiac BETWEEN 7 AND 9 then 3 WHEN Miesiac BETWEEN 10 AND 12 then 4 END, Miesiac, Obrot FROM Krok1), Krok3 AS (SELECT DokTyp, Rok, Kwartal, Obrot=SUM(Obrot) FROM Krok2 GROUP BY DokTyp, Rok, Kwartal) SELECT * FROM Krok3
Krok 4 – PIVOT danych
Na koniec przeniesiemy kolumnę Kwartal do czterech oddzielnych kolumn. Każda kolumna to obroty tylko dla jednego kwartału ograniczonego warunkiem WHERE. To oznacza potrzebę dodania czterech odrębnych zapytań. Wyświetlając wyniki połączymy te zapytania przez FULL JOIN, aby zapewnić kompletność danych, gdyby w jakimś kwartale ich zabrakło.
WITH Krok1 AS (SELECT DokTyp=OB_Skrot, Rok=TrN_RokMiesiac/100, Miesiac=trn_RokMiesiac%100, Obrot=TrN_NettoR+TrN_NettoP FROM CDN.TraNag JOIN CDN.Obiekty ON TrN_GIDTyp=OB_GIDTyp WHERE TrN_TrNRok=2014), Krok2 AS (SELECT DokTyp, Rok, Kwartal=CASE WHEN Miesiac BETWEEN 1 AND 3 then 1 WHEN Miesiac BETWEEN 4 AND 6 then 2 WHEN Miesiac BETWEEN 7 AND 9 then 3 WHEN Miesiac BETWEEN 10 AND 12 then 4 END, Miesiac, Obrot FROM Krok1), Krok3 AS (SELECT DokTyp, Rok, Kwartal, Obrot=SUM(Obrot) FROM Krok2 GROUP BY DokTyp, Rok, Kwartal), Krok4Kwartal1 AS (SELECT DokTyp, Rok, Kwartal, Obrot FROM Krok3 WHERE Kwartal=1), Krok4Kwartal2 AS (SELECT DokTyp, Rok, Kwartal, Obrot FROM Krok3 WHERE Kwartal=2), Krok4Kwartal3 AS (SELECT DokTyp, Rok, Kwartal, Obrot FROM Krok3 WHERE Kwartal=3), Krok4Kwartal4 AS (SELECT DokTyp, Rok, Kwartal, Obrot FROM Krok3 WHERE Kwartal=4) SELECT * FROM Krok4Kwartal1 FULL JOIN Krok4Kwartal2 ON Krok4Kwartal1.DokTyp=Krok4Kwartal2.DokTyp FULL JOIN Krok4Kwartal3 ON Krok4Kwartal2.DokTyp=Krok4Kwartal3.DokTyp FULL JOIN Krok4Kwartal4 ON Krok4Kwartal3.DokTyp=Krok4Kwartal4.DokTyp
Krok 5 – oswajanie wyników
Końcowe zapytanie zwraca wszystkie kolumny ze wszystkich czterech zapytań, co jest mało czytelne. Proponuję nieco je zmodyfikować, aby raport stał się bardziej przyjazny (zmiana tylko w zakresie składni SELECT):
WITH Krok1 AS (SELECT DokTyp=OB_Skrot, Rok=TrN_RokMiesiac/100, Miesiac=trn_RokMiesiac%100, Obrot=TrN_NettoR+TrN_NettoP FROM CDN.TraNag JOIN CDN.Obiekty ON TrN_GIDTyp=OB_GIDTyp WHERE TrN_TrNRok=2009), Krok2 AS (SELECT DokTyp, Rok, Kwartal=CASE WHEN Miesiac BETWEEN 1 AND 3 then 1 WHEN Miesiac BETWEEN 4 AND 6 then 2 WHEN Miesiac BETWEEN 7 AND 9 then 3 WHEN Miesiac BETWEEN 10 AND 12 then 4 END, Miesiac, Obrot FROM Krok1), Krok3 AS (SELECT DokTyp, Rok, Kwartal, Obrot=SUM(Obrot) FROM Krok2 GROUP BY DokTyp, Rok, Kwartal), Krok4Kwartal1 AS (SELECT DokTyp, Rok, Kwartal, Obrot FROM Krok3 WHERE Kwartal=1), Krok4Kwartal2 AS (SELECT DokTyp, Rok, Kwartal, Obrot FROM Krok3 WHERE Kwartal=2), Krok4Kwartal3 AS (SELECT DokTyp, Rok, Kwartal, Obrot FROM Krok3 WHERE Kwartal=3), Krok4Kwartal4 AS (SELECT DokTyp, Rok, Kwartal, Obrot FROM Krok3 WHERE Kwartal=4) SELECT [Typ dokumentu]=COALESCE (Krok4Kwartal1.DokTyp, Krok4Kwartal2.DokTyp, Krok4Kwartal3.DokTyp, Krok4Kwartal4.DokTyp), [Rok]=COALESCE (Krok4Kwartal1.Rok, Krok4Kwartal2.Rok, Krok4Kwartal3.Rok, Krok4Kwartal4.Rok), [Obroty I]=ISNULL(Krok4Kwartal1.Obrot, 0.00), [Obroty II]=ISNULL(Krok4Kwartal2.Obrot, 0.00), [Obroty III]=ISNULL(Krok4Kwartal3.Obrot, 0.00), [Obroty IV]=ISNULL(Krok4Kwartal4.Obrot, 0.00) FROM Krok4Kwartal1 FULL JOIN Krok4Kwartal2 ON Krok4Kwartal1.DokTyp=Krok4Kwartal2.DokTyp FULL JOIN Krok4Kwartal3 ON Krok4Kwartal2.DokTyp=Krok4Kwartal3.DokTyp FULL JOIN Krok4Kwartal4 ON Krok4Kwartal3.DokTyp=Krok4Kwartal4.DokTyp
Wynik zapytania:
Zastosowanie CTE w Comarch ERP XL
Z użyciem składni CTE możesz tworzyć szybkie i wydajne raporty bez potrzeby sięgania do Transact SQL. Nie ma potrzeby tworzyć dla nich uciążliwych procedury SQL, co upraszcza proces dodawania nowych raportów i modyfikacji istniejących. Zapytania CTE mogą być wykorzystane bezpośrednio w Comarch ERP XL na poziomie Raportów typu wykres, Szybkich raportów SQL, polecenia Command w obrębie Crystal Reports, a nawet jako zaawansowany warunek EXISTS dla filtrów.
Ponadto zastosowanie mechanizmu rekurencji umożliwia zbieranie danych ustrukturyzowanych, takich jak drzewo towarów, kontrahentów, czy technologii produkcji.
Chcesz wyliczyć koszt produktu? Możesz sięgnąć do kosztów materiałów na tym produkcie. A co jeśli materiały też są półproduktami? A jeśli półprodukty składają się z innych półproduktów? Dzięki zastosowaniu CTE sięgniesz do dowolnego poziomu technologii bez tworzenia skomplikowanych tasiemców SQL.
Ten przykład został zaczerpnięty ze szkolenia Comarch ERP XL Średniozaawansowany SQL.
Jeśli chcesz dowiedzieć się więcej na temat szkoleń prowadzonych przeze mnie i nasz zespół to zapraszam Cię do odwiedzenia naszego Centrum Szkoleniowego Prospeo.
