Good Morning all,
With help from a member of the SAP community I was able to prepare a report which showed the current stock quantity, followed by monthly sales (6 months) for each item.
See the query below;
Declare @Year Numeric
Set @Year='2015'
SELECT T0.ItemCode, (T0.[OnHand] - T0.[IsCommited]) + T0.OnOrder AS 'Available',
sum(Case DATENAME(month,T2.DocDate) when 'June' then T1.Quantity else 0 end) as 'June Qty',
sum(Case DATENAME(month,T2.DocDate) when 'July' then T1.Quantity else 0 end) as 'July Qty',
sum(Case DATENAME(month,T2.DocDate) when 'August' then T1.Quantity else 0 end) as 'AugQty',
sum(Case DATENAME(month,T2.DocDate) when 'September' then T1.Quantity else 0 end) as 'Sept Qty',
sum(Case DATENAME(month,T2.DocDate) when 'October' then T1.Quantity else 0 end) as 'Oct Qty',
sum(Case DATENAME(month,T2.DocDate) when 'November' then T1.Quantity else 0 end) as 'Nov Qty',
sum(Case DATENAME(month,T2.DocDate) when 'December' then T1.Quantity else 0 end) as 'Dec Qty'
FROM dbo.OITM T0
INNER JOIN INV1 T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry
WHERE DATENAME(YEAR ,T1.DocDate )=@Year AND T0.ItmsGrpCod = '[%0]'
GROUP BY T0.ItemCode, T0.ItemName,T0.OnHand,T0.[IsCommited], T0.OnOrder
We now need to draw data for sales in 2016, however we need to keep the later months of 2015 on the report.
My question is whether there is any way of combining data from two separate years on one report?
Many thanks in advance
Tadhg
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.