Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Salas Analysis Query including Credit Memo

Hi Experts,

Can anyone help me out in preparing the query for Sales Analysis Report.

I need Itemwise Sales Analysis Report by inculding Credit Memos.

Below i tried but not including Credit Memo Quantity from Forum

Declare @Year Numeric

Set @Year='2014'

SELECT Distinct @Year,T0.ItemCode, T0.ItemName, T0.OnHand,

                        sum(Case DATENAME(month,T2.DocDate) when 'January' then T1.Quantity   else 0 end) as [January Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'February' then T1.Quantity   else 0 end) as [February Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'March' then T1.Quantity   else 0 end) as [March Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'April' then T1.Quantity   else 0 end) as [April Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'May' then T1.Quantity   else 0 end) as [May Qty],

                        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 [August Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'September' then T1.Quantity   else 0 end) as [September Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'October' then T1.Quantity   else 0 end) as [October Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'November' then T1.Quantity   else 0 end) as [November Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'December' then T1.Quantity   else 0 end) as [December Qty],

                        'Max Sales'

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

GROUP BY T0.ItemCode, T0.ItemName, T0.OnHand

After Month December I need Highest Quantity sold among 12 months . Suppose in March 1000 sold and April 1200 Sold then 1200 should come in the Max Sales Column

Thanks

Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question