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

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

  • 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

  • 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())

  • 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.

  • 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

  • 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...

  • 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

  • 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