Skip to Content
-1
Jun 22, 2022 at 01:34 PM

Sales report daily, monthly, quarterly.

64 Views

I was thinking as my boss wants me to make report based on sales where the dollar amount of daily, monthly and quarterly is shown. How would I tweak it?

Select T2.SLPName, T4.ItemCode, T4.ItemName,
SUM(CASE WHEN YEAR(T1.DocDate) = YEAR(GetDAte())-1 THEN ISNULL(T0.LineTotal,0) ELSE 0 END) as 'Last Year',
SUM(CASE WHEN YEAR(T1.DocDate) <> YEAR(GetDAte())-1 THEN ISNULL(T0.LineTotal,0) ELSE 0 END) as 'This Year'

FROM RDR1 T0
INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OSLP T2 ON T1.SlpCode = T2.SlpCode
INNER JOIN OCRD T3 ON T1.CardCode = T3.CardCode
INNER JOIN OITM T4 ON T4.Itemcode = T0.itemCode
WHERE T2.SlpName = [%0] AND ((T1.DocDate >= [%1] and T1.DocDate <= [%2]) OR
(T1.DocDate >= DateAdd(YEAR, -1, [%1]) and T1.DocDate <= DateAdd(YEAR, -1, [%2]))) 
AND T1.DocType = 'I' AND T1.Canceled = 'N'
GROUP BY T2.SLPName, T4.ItemCode, T4.ItemName