Skip to Content
0
Former Member
Sep 12, 2014 at 01:41 PM

Salas Analysis Query including Credit Memo

24 Views

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