Add 'monthly total' to query

[former_member947612]

Hello SAP experts,

I just worked out a query which could give me total quantity consumed in issue for production (IGE1) by item, within a selected date range. But on top of that, it would be more helpful if it could also give me monthly totals (also by item) also within that selected date range.

Below is the current query that I worked out:

SELECT T0.[ItemCode], T0.[Dscription], SUM(T0.[Quantity]) FROM IGE1 T0 INNER JOIN OIGE T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[DocDate] >=[%0] AND T1.[DocDate] <=[%1] GROUP BY T0.[ItemCode], T0.[Dscription]

Could someone please help to figure out how to add monthly total on top of this?

Much appreciated!! Thank you for your time!!

Ashley

[former_member491159]

SELECT DISTINCT
	T0.[ItemCode]
   ,T0.[Dscription]
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 1 THEN (T0.[Quantity]) ELSE 0 END) AS Jan
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 2 THEN (T0.[Quantity]) ELSE 0 END) AS Fev
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 3 THEN (T0.[Quantity]) ELSE 0 END) AS Mar
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 4 THEN (T0.[Quantity]) ELSE 0 END) AS Abr
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 5 THEN (T0.[Quantity]) ELSE 0 END) AS Mai
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 6 THEN (T0.[Quantity]) ELSE 0 END) AS Jun
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 7 THEN (T0.[Quantity]) ELSE 0 END) AS Jul
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 8 THEN (T0.[Quantity]) ELSE 0 END) AS Ago
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 9 THEN (T0.[Quantity]) ELSE 0 END) AS 'Set'
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 10 THEN (T0.[Quantity]) ELSE 0 END) AS 'Out'
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 11 THEN (T0.[Quantity]) ELSE 0 END) AS Nov
   ,SUM(CASE WHEN MONTH(T1.DocDate) = 12 THEN (T0.[Quantity]) ELSE 0 END) AS Dez
FROM IGE1 T0
INNER JOIN OIGE T1
	ON T0.[DocEntry] = T1.[DocEntry]


WHERE T1.[DocDate] >= [%0] 
AND T1.[DocDate] <= [%1] 
GROUP BY T0.[ItemCode], T0.[Dscription] 

[former_member947612]

Hello César,

Thank you so much for the help! It worked!

Except that it didn't give me the total but I just added SUM(T0.[Quantity]) at the end of the months and it worked and gives me exactly what I need 😛

Thanks again for your help and time!! 🙂

Ashley