Câu truy vấn bằng SAP HANA:
Select "ItemCode", SUM("Jan") as "Jan", SUM("Feb") as "Feb", SUM("Mar") as "Mar" ,
SUM("April") as "April", SUM("May") as "May", SUM("June") as "June" ,
SUM("July") as "July",SUM("Aug") as "Aug", SUM("Sep") as "Sep" ,
SUM("Oct") as "Oct", SUM("Nov") as "Nov", SUM("Dec") as "Dec"
from (
select T1."ItemCode",
(case when month(T0."DocDate") = '1' then Sum(T1."Quantity") else NULL end) as "Jan",
(case when month(T0."DocDate") = '2' then Sum(T1."Quantity") else NULL end) as "Feb",
(case when month(T0."DocDate") = '3' then Sum(T1."Quantity") else NULL end) as "Mar",
(case when month(T0."DocDate") = '4' then Sum(T1."Quantity") else NULL end) as "April",
(case when month(T0."DocDate") = '5' then Sum(T1."Quantity") else NULL end) as "May",
(case when month(T0."DocDate") = '6' then Sum(T1."Quantity") else NULL end) as "June",
(case when month(T0."DocDate") = '7' then Sum(T1."Quantity") else NULL end) as "July",
(case when month(T0."DocDate") = '8' then Sum(T1."Quantity") else NULL end) as "Aug",
(case when month(T0."DocDate") = '9' then Sum(T1."Quantity") else NULL end) as "Sep",
(case when month(T0."DocDate") = '10' then Sum(T1."Quantity") else NULL end) as "Oct",
(case when month(T0."DocDate") = '11' then Sum(T1."Quantity") else NULL end) as "Nov",
(case when month(T0."DocDate") = '12' then Sum(T1."Quantity") else NULL end) as "Dec"
from OINV T0 inner join INV1 T1 on T0."DocEntry" = T1."DocEntry"
where year(T0."DocDate") = 2022
group by T1."ItemCode", T0."DocDate", T1."Quantity"
having Sum(T1."Quantity") <> 0
)
GROUP BY "ItemCode";
Câu truy vấn bằng SQL
SELECT P.CustCode, P.CustName, P.ItemNo, P.Name, ISNULL([1],0) as 'Jan', ISNULL([2],0) as 'Feb', ISNULL([3],0) as 'Mar', ISNULL([4],0) as 'Apr', ISNULL([5],0) as 'May', ISNULL([6],0) as 'Jun', ISNULL([7],0) as 'Jul', ISNULL([8],0) as 'Aug', ISNULL([9],0) as 'Sept', ISNULL([10],0) as 'Oct', ISNULL([11],0) as 'Nov', ISNULL([12],0) as 'Dec' FROM (SELECT T1.CardCode as CustCode, T1.CardName as CustName, (T0.ItemCode) as ItemNo, T0.Dscription as Name, T0.Quantity as Qty, MONTH(T1.docdate) as month FROM INV1 T0 Inner Join OINV T1 ON T0.DocEntry=T1.DocEntry WHERE Year(T1.docdate) = 2022 UNION SELECT T1.CardCode as CustCode, T1.CardName as CustName, (T0.ItemCode) as ItemNo, T0.Dscription as Name, T0.Quantity as Qty, MONTH(T1.docdate) as month FROM RIN1 T0 Inner Join ORIN T1 ON T0.DocEntry=T1.DocEntry WHERE Year(T1.docdate) = 2022) S PIVOT (SUM(Qty) FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P Group by P.CustCode, P.CustName, P.ItemNo, P.Name,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
