CODEWITHSUNDEEP
sql
user2100632
user2100632

Reputation: 121

SQL Difference Between Current Year and Last Year. If Last Year Data Does Not Exist Include Current Year

In a previous post I got help finding incremental sales. The query works great. I added the breakout by product. The issue I’m having is that I need to show new products being sold. If the product did not exist last year, but we are selling it this year; then it should show up in the data table.

I tried use a CASE statement in the WHERE, but it was causing a lot of duplication of the data. I was thinking something like what is below. How do I go about including items that are only in the current year? Thank you for your help, its greatly appreciated.

Not Working Where Clause

      WHERE
        Ym.Project = 
        CASE
            WHEN ymprev.Project IS NULL THEN ym.Project
        ELSE ymprev.Project
        END

Below is the working query. 

        WITH ym as(
                    SELECT
                        Product
                        ,SUM(Sales) AS Sales
                        ,MONTH(Date) AS Month
                        ,YEAR(Date) AS Year

                    FROM SalesTable

                    GROUP BY
                        YEAR(Date)
                        ,MONTH(Date)
                        ,Product

                    )
        SELECT
            ymprev.Project AS PrevProject
            ,ym.Product
            ,ym.Sales
            ,ymprev.Sales AS PreviousSales
            ,(ym.Sales - ymprev.Sales) AS IncrementalSales
            ,ymprev.Month AS PreviousMonth
            ,ymprev.Year AS PreviousYear
            ,ym.Month
            ,ym.Year

        FROM ym 

        JOIN ym ymprev on
            ymprev.Year = ym.Year
            AND ymprev.Month =  ym.Month
            AND ymprev.Product = ym.Product

        ORDER BY
        ym.Year
        ,ym.Month

Upvotes: 1

Views: 2189

Answers (1)

AHiggins
AHiggins

Reputation: 7227

Your query is implicitly using an INNER JOIN - this means that you will only see values that have a match in both datasets, just as you describe.

Try changing your FROM clause to 

FROM ym 
 LEFT JOIN 
ym ymprev on
    ymprev.Year = ym.Year
    AND ymprev.Month =  ym.Month
    AND ymprev.Product = ym.Product

You will also need to incorporate similar logic in any values that include data elements from the previous year's query. For example, ,(ym.Sales - ymprev.Sales) AS IncrementalSales will need to be turned into ,(ym.Sales - ISNULL(ymprev.Sales,0)) AS IncrementalSales or it will return NULL for any records that only exist in the current year.

Your posted query doesn't include the Project field in your CTE, so I can't tell exactly how that works, but the posted data should get you started.

Upvotes: 1

Related Questions