web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Microsoft Dynamics GP (Archived)

YTD and LYTD Sales Comparison

(0) ShareShare
ReportReport
Posted on by 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)
  • sandipdjadhav Profile Picture
    18,306 on at
    RE: YTD and LYTD Sales Comparison

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans