I have a table with the columns Sales_Date and Sales. I am looking for a solution to get Sales for the last year from the Sales_Date Column. Sales_Date column has values from the year 2015 onwards.

For example:

I am looking for the below result set:

CodePudding user response：

1. Filter all data to this year (WHERE YEAR(Sales.Sales_Date) = 2016).

2. LEFT JOIN to the same table, combining each date with the same date one year prior (Sales LEFT JOIN Sales AS Sales_LastYear ON Sales_LastYear.Sales_Date = DATEADD(year, -1, Sales.Sales_Date)).

3. SELECT the fields that you want (SELECT Sales.Sales_Date, Sales_LastYear.Sales_Date AS LYear_Sales_Date, ...).

Replace the LEFT JOIN with an INNER JOIN, if you want only those records that have a matching last-year record.

CodePudding user response：

Seems like LAG would work here. Assuming you are always wanting the for the same (day and) month:

WITH CTE AS(
    SELECT Sales_Date,
           Sales,
           LAG(Sales_Date) OVER (PARTITION BY DAY(Sales_Date), MONTH(Sales_Date) ORDER BY YEAR(Sales_Date)) AS LYear_Sales_Date,
           LAG(Sales) OVER (PARTITION BY DAY(Sales_Date), MONTH(Sales_Date) ORDER BY YEAR(Sales_Date)) AS LYear_Sales
    FROM dbo.YourTable)
SELECT Sales_Date,
       Sales,
       LYear_Sales_Date,
       LYear_Sales
FROM CTE
WHERE Sales_Date >= '20160101'
  AND Sales_Date < '20170101';