item group wise sales report by comparing previous and current month?

[karthick_s8]

Hi all,

i want item group wise sales report by comparing previous and current month

herre i have mentioned my query ,but its not retrieve correct results

kindly help

SELECT T3.ItmsGrpNam,

(SELECT SUM(T1.QUANTITY) FROM INV1 T1  WHERE MONTH(T0.DOCDATE) =  MONTH(GETDATE()) AND T1.ItemCode=T2.ItemCode)  AS 'CURRENT MONTH QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1  WHERE MONTH(T0.DOCDATE) =  MONTH(GETDATE()-30)AND T1.ItemCode=T2.ItemCode)  AS 'PREVIOUS MONTH QTY',

(SELECT SUM(T1.LineTotal) FROM INV1 T1  WHERE MONTH(T0.DOCDATE) =  MONTH(GETDATE())AND T1.ItemCode=T2.ItemCode)  AS 'CURRENT SALES',

(SELECT SUM(T1.LineTotal) FROM INV1 T1  WHERE MONTH(T0.DOCDATE) =  MONTH(GETDATE()-30)AND T1.ItemCode=T2.ItemCode)  AS 'PREVIOUS SALES'

FROM

OINV T0 INNER JOIN  INV1 T1 ON T0.DocEntry=T1.DocEntry

left outer join OITM T2 ON T1.ItemCode = T2.ItemCode 

LEFT OUTER JOIN OITB T3 ON T2.ItmsGrpCod=T3.ItmsGrpCod

GROUP BY T3.ItmsGrpNam, T2.ItemCode,T0.DOCDATE

[frank_wang6 · Accepted solution]

try this please.

WITH T AS (SELECT T3.ItmsGrpNam, CONVERT(VARCHAR(6), T0.DocDate, 112) YearMonth,

SUM(T1.Quantity) TotalQty, SUM(T1.LineTotal) TotalAmt

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

WHERE T0.DocDate >= DATEADD(m, -1, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/' + '1')

GROUP BY T3.ItmsGrpNam, CONVERT(VARCHAR(6), T0.DocDate, 112)

)

SELECT DISTINCT T.ItmsGrpNam,

(SELECT TotalQty FROM T TX WHERE TX.ItmsGrpNam = T.ItmsGrpNam AND TX.YearMonth = CONVERT(VARCHAR(6), GETDATE(), 112)) CurrentMonthQty,

(SELECT TotalQty FROM T TX WHERE TX.ItmsGrpNam = T.ItmsGrpNam AND TX.YearMonth = CONVERT(VARCHAR(6), DATEADD(m, -1, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/' + '1'), 112)) PreviousMonthQty,

(SELECT TotalAmt FROM T TX WHERE TX.ItmsGrpNam = T.ItmsGrpNam AND TX.YearMonth = CONVERT(VARCHAR(6), GETDATE(), 112)) CurrentMonthSales,

(SELECT TotalAmt FROM T TX WHERE TX.ItmsGrpNam = T.ItmsGrpNam AND TX.YearMonth = CONVERT(VARCHAR(6), DATEADD(m, -1, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/' + '1'), 112)) PreviousMonthSales

FROM T

[karthick_s8]

thank you Frank,its awesome....

Regards

Karthick