Share
ReportSuggestions requested on comparing fiscal YTD to last fiscal YTD sales by date range.
*This post is locked for comments
You’re offline. This is a read only version of the page.
170
Suggestions requested on comparing fiscal YTD to last fiscal YTD sales by date range.
*This post is locked for comments
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (