June 30, 2018 at 4:25 am
Comments posted to this topic are about the item Using a Derived Table to Compare Data with Values from a Previous Year
August 2, 2018 at 5:57 am
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
August 2, 2018 at 6:36 am
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())
August 2, 2018 at 7:51 am
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.
August 2, 2018 at 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
August 2, 2018 at 9:47 am
service 87594 - Thursday, August 2, 2018 8:48 AMWhat 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...
August 2, 2018 at 1:58 pm
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
August 2, 2018 at 2:50 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply