Using a Derived Table to Compare Data with Values from a Previous Year

  • Adam Aspin

    SSCarpal Tunnel

    Points: 4795

    Comments posted to this topic are about the item Using a Derived Table to Compare Data with Values from a Previous Year

  • John_P

    SSC Veteran

    Points: 235

    Hi Adam,

    I like your article, but I would recommend changing how you filter your dates.  Using the MONTH and YEAR functions can cause SQL to ignore any indexes you have on this column.  I would calculate the date ranges at the top of your query and then using these values in the query.  Something like:

    DECLARE @ThisYearFirstDayOfMonth DATETIME = DATEADD(month, DATEDIFF(month, 0, GetDate()), 0)
    DECLARE @ThisYearLastDayOfMonth DATETIME = DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
    DECLARE @LastYearFistDayOfMonth DATETIME = DATEADD(year, -1, @ThisYearFirstDayOfMonth)
    DECLARE @LastYearLastDayOfMonth DATETIME = DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, @LastYearFistDayOfMonth) + 1, 0))

    Inner query: WHERE  SA.SaleDate BETWEEN @LastYearFistDayOfMonth AND @LastYearLastDayOfMonth
    Outer query: WHERE  SA.SaleDate BETWEEN @ThisYearFirstDayOfMonth AND @ThisYearLastDayOfMonth

    Just a thought...

    Thanks!

    John

  • cstringham

    SSC Journeyman

    Points: 93

    Ignoring the very valid point made my John_P, is there a reason you used a derived table instead of using a CTE?

    ;With SalesData (Color, SalesYear, SalesMonth, AverageMonthSales) as (
     SELECT Color
       ,SalesYear = YEAR(SA.SaleDate)
       ,SalesMonth = MONTH(SA.SaleDate)
       ,AVG(SA.TotalSalePrice) AS AverageMonthSales
      FROM Data.Make AS MK
       INNER JOIN Data.Model AS MD
        ON MK.MakeID = MD.MakeID
       INNER JOIN Data.Stock AS ST
        ON ST.ModelID = MD.ModelID
       INNER JOIN Data.SalesDetails SD
        ON ST.StockCode = SD.StockID
       INNER JOIN Data.Sales AS SA
        ON SA.SalesID = SD.SalesID
      GROUP BY Color
         ,YEAR(SA.SaleDate)
         ,MONTH(SA.SaleDate)
     )
    Select cy.Color
      ,cy.AverageMonthSales
      ,py.AverageMonthSales as AveragePreviousYearMonthSales
     FROM SalesData cy
      LEFT JOIN SalesData py
       ON py.Color = cy.Color AND
        py.SalesYear = cy.SalesYear - 1 AND
        py.SalesMonth = cy.SalesMonth
     WHERE cy.SalesYear = YEAR(GETDATE()) AND
       cy.SalesMonth = MONTH(GETDATE())

  • Mike Good

    SSCertifiable

    Points: 7356

    Well done.  CTE vs derived table is purely aesthetic choice I think.  One typo/mistake: "select DATEADD(yy, GETDATE(), - 1)" has 2nd and 3rd params reversed.

  • service 87594

    SSC Enthusiast

    Points: 182

    What if a three year comparison were needed? Would it look something like this where the left outer joins are at the same indent level?
    Inner Join (current Year) cytbl
        Left Outer Join (previous year)
        )derived table 1 (pytbl)
        on cytbl = py1tbl
        Left Outer Join (2yrs prev)
        )derived table2 (py2tbl)
        on cytbl = py2tbl
    Where...

    Our folks usually look at three to five year comparisons all in the same period range, not just prev year.  So they may ask, "What are the last five years sales for the period of October through December for account x."

    Thank you

  • John_P

    SSC Veteran

    Points: 235

    service 87594 - Thursday, August 2, 2018 8:48 AM

    What if a three year comparison were needed? Would it look something like this where the left outer joins are at the same indent level?
    Inner Join (current Year) cytbl
        Left Outer Join (previous year)
        )derived table 1 (pytbl)
        on cytbl = py1tbl
        Left Outer Join (2yrs prev)
        )derived table2 (py2tbl)
        on cytbl = py2tbl
    Where...

    Our folks usually look at three to five year comparisons all in the same period range, not just prev year.  So they may ask, "What are the last five years sales for the period of October through December for account x."

    Thank you

    I'd pull all the data back in an excel spreadsheet (data driven from a query).  Then look at the data in a pivot table, group by year and month...

  • drew.allen

    SSC Guru

    Points: 76492

    I think that there is a much better way to do this using windowed functions.  The approach depends on whether there are gaps in the data, which I haven't had a chance to look at.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen

    SSC Guru

    Points: 76492

    Here is an approach that uses LAG to determine the previous amount.  I haven't done any comparisons, but I believe that it will perform faster, because it requires fewer reads and I've modified the date selection to be SARGable.

    WITH Sales_Compare AS

    (

    SELECT

    ST.Color

    , AVG(SA.TotalSalePrice) AS AverageMonthSales

    , LAG(AVG(SA.TotalSalePrice)) OVER(PARTITION BY St.Color ORDER BY DT.StartDate) AS AveragePreviousYearMonthSales

    , MAX(PeriodStatus) AS PeriodStatus

    FROM Data.Make AS MK

    INNER JOIN Data.Model AS MD ON MK.MakeID = MD.MakeID

    INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID

    INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID

    INNER JOIN Data.Sales AS SA ON SA.SalesID = SD.SalesID

    CROSS APPLY

    (

    VALUES

    (DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0), 'Current')

    , (DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-12, 0), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-11, 0), 'Previous')

    ) Dt(StartDate, EndDate, PeriodStatus)

    WHERE SA.SaleDate >= Dt.StartDate AND SA.SaleDate < Dt.EndDate -- Using half-closed intervals

    GROUP BY ST.Color, DT.StartDate

    )

    SELECT sc.Color, sc.AverageMonthSales, sc.AveragePreviousYearMonthSales

    FROM Sales_Compare sc

    WHERE sc.PeriodStatus = 'Current'

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply