YTD and LYTD Sales Comparison

(0) Share

Report

Posted on by Steve Burney

170

Suggestions requested on comparing fiscal YTD to last fiscal YTD sales by date range.

*This post is locked for comments

I have the same question (0)

All responses (1)

Answers (0)

sandipdjadhav 18,333 on at

Like (0)

Report
Copy link

Link copied!

Steve,

Below is SQL Query for same. I think have got it from Victoria Yudin blog-I don't remember correctly but this will solve your problem.

********************************************************************************

SELECT T.CUSTNMBR AS [Customer ID], C.CUSTNAME AS [Customer Name], C.CUSTCLAS AS [Class ID], SUM(CASE WHEN YEAR(T .DOCDATE)

= 2013 THEN T .Amount ELSE 0 END) AS [2013 Sales], SUM(CASE WHEN YEAR(T .DOCDATE) = 2012 THEN T .Amount ELSE 0 END) AS [2012 Sales],

SUM(CASE WHEN YEAR(T .DOCDATE) = 2011 THEN T .Amount ELSE 0 END) AS [2011 Sales], SUM(CASE WHEN YEAR(T .DOCDATE)

= 2010 THEN T .Amount ELSE 0 END) AS [2010 Sales], SUM(CASE WHEN YEAR(T .DOCDATE) = 2009 THEN T .Amount ELSE 0 END) AS [2009 Sales],

SUM(CASE WHEN YEAR(T .DOCDATE) = 2008 THEN T .Amount ELSE 0 END) AS [2008 Sales], SUM(CASE WHEN YEAR(T .DOCDATE)

= 2007 THEN T .Amount ELSE 0 END) AS [2007 Sales], SUM(T.Amount) AS [Total Sales]

FROM (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, CASE WHEN RMDTYPAL IN (1, 3, 4) THEN (SLSAMNT + MISCAMNT - TRDISAMT) WHEN RMDTYPAL IN (7,

8) THEN - 1 * (SLSAMNT + MISCAMNT - TRDISAMT) ELSE 0 END AS Amount

FROM dbo.RM20101

WHERE (VOIDSTTS = 0)

UNION ALL

SELECT CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, CASE WHEN RMDTYPAL IN (1, 3, 4) THEN (SLSAMNT + MISCAMNT - TRDISAMT) WHEN RMDTYPAL IN (7,

8) THEN - 1 * (SLSAMNT + MISCAMNT - TRDISAMT) ELSE 0 END AS Amount

FROM dbo.RM30101

WHERE (VOIDSTTS = 0)) AS T LEFT OUTER JOIN

dbo.RM00101 AS C ON T.CUSTNMBR = C.CUSTNMBR

GROUP BY T.CUSTNMBR, C.CUSTNAME, C.CUSTCLAS

*******************************************************************************************************

Feel free to modify it as per your requirement.

Thanks

Sandip

Was this reply helpful? Yes No