I have the table with the following layout:

I am trying to calculate the past 12 month of sales per Customer given a current date.

At this moment I only know how to calculate the 12 trailing months. I came up with the first draft version of how I'd approach this:

SELECT Customer, Year, Month, SUM(Sales) as Total Sales per Customer INTO #temp
FROM Sales table
GROUP BY Customer, Year, Month
ORDER BY Year, Month

Once I get a table rolled up to Customer, Year, Month level I can apply the following:

SELECT SUM(Total Sales) 
       OVER (PARTITION BY Customer ORDER BY Year Month ROWS BETWEEN 11 PRECEDING and CURRENT ROW) AS Past 12 months Sales
FROM #temp

The output table will have the trailing 12 months. However, my goal is to get the current date with GETDATE(), look up the month and year in the table that corresponds to the current date, and calculate just the past 12 months from the current date.

I am figuring what SQL part I am missing to achieve my objective. I tried declare and set variables but they cannot be fed into the WHERE clause because the output is not value (like in Python).

Please advise on how to tackle this.

CodePudding user response：

First calculate the start date:

Declare @StartDate datetime = DATEADD(Month, -12, GETDATE())

Then add this where clause:

Where cast(Concat(Year, ' ', Month, ' 01') as Date) >= @StartDate

CodePudding user response：

with group by rollup() you can have all in one:

set language english /*to make sure the sql session language you're running on is the same than the datas, otherwise CONVERT will not work*/

select  
    [Year]  = isnull(cast(Year as char(5)), 'total') 
   ,[Month] = isnull(Month, 'total')
   ,Customer
   ,[Sales] = SUM(sales)
from Sales
where 
   CONVERT(date,'01 '   Month  ' '  CAST(Year as char(4)),113) > DATEADD(MONTH,-12,cast(GETDATE()-DAY(GETDATE()) as date))
group by ROLLUP((Year,Month))/*parenthesis means one unbreakable key*/,customer
order by customer, Year,Month

results:

2021    | March     | Customer #1   |  300
2022    | February  | Customer #1   |  200
2022    | March     | Customer #1   |  500
total   | total     | Customer #1   | 1000
2022    | January   | Customer #2   |  200
2022    | March     | Customer #2   | 1100
total   | total     | Customer #2   | 1300