Query get item sold quantity for last four week per week

[01061454337]

please, i want your help in Query get item sold quantity for last four week per week

[Johan_H]

Hi,

MS SQL or HANA?

Regards,

Johan

[01061454337]

Thanks replying,

HANA DB

[azizelmir · Accepted solution]

Hi Mohamad,

Check this query if it helps, sure it will need some modification:

SELECT P."Code", P."CName",
[1] as 'Jan',
[2] as 'Feb',
[3] as 'Mar',
[4] as 'Apr',
[5] as 'May',
[6] as 'Jun',
[7] as 'Jul',
[8] as 'Aug',
[9] as 'Sep',
[10] as 'Oct',
[11] as 'Nov',
[12] as 'Dec'
FROM

(SELECT T0."Itemcode" as Code, T0."Dscription" as CName, T0."Quantity" as Qty,
MONTH(T0."docdate") as month
FROM dbo.inv1 T0 Inner Join OINV T1 ON T0."DocEntry"=T1."DocEntry"
WHERE Year(T1."docdate") = 2022
UNION
SELECT (T0."ItemCode") as Code, T0."Dscription" as CName, -1 * T0."Quantity" as Qty,
MONTH(T0."docdate") as month
FROM dbo.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

Thank you,

Aziz