How do I summarize sales data in SQL by month for last 24months?

I have big number of rows with sales for different products on various days. I want to retrieve the sum for each product and per month. For the last 24months.

1. How do I write a WHERE function showing the last 24 months (based on latest date in table not actual date)?
2. How is that summarized and shown by month instead of individual days like 2018-01-24?

**Sample Data Table**
| SalesDate   | Product     | SLSqty |
| 2018-01-24  | Product A    | 25 |
| 2019-06-10  | Product B    | 10 |
| 2019-10-07  | Product C    | 4  |
| 2020-03-05  | Product A    | 20 |
| 2021-09-01  | Product A    | 50 |
| 2021-09-01  | Product B    | 10 |
| 2021-09-02  | Product C    | 3  |
| 2021-09-04  | Product A    | 50 |
| 2021-09-07  | Product B    | 10 |

**Expected Result**
| SalesMONTH  | Product      | SLSqty |
| 2019-10-31  | Product C    | 4  |
| 2020-03-31  | Product A    | 20 |
| 2021-09-30  | Product A    | 100|
| 2021-09-30  | Product A    | 20 |
| 2021-09-30  | Product B    | 3  |

Answer

I would make a parameter that stores the value of the latest date in your table. Then you can impute the parameter in you WHERE clause.

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP(
    [SalesDate]             DATE
    ,[product]              NVARCHAR(20)
    ,[SLSqty]               INT
)
INSERT INTO #TEMP([SalesDate],[product],[SLSqty])
VALUES('2018-01-24','Product A',25)
    ,('2019-06-10','Product B',10)
    ,('2019-10-07','Product C',4 )
    ,('2020-03-05','Product A',20)
    ,('2021-09-01','Product A',50)
    ,('2021-09-01','Product B',10)
    ,('2021-09-02','Product C',3 )
    ,('2021-09-04','Product A',50)
    ,('2021-09-07','Product B',10)

DECLARE @DATEVAR AS DATE = (SELECT MAX(#TEMP.SalesDate) FROM #TEMP)

The last line declares the variable. If you select @DATEVAR, you get the output of a single date defined by the select statement:

Then you impute it into a where clause. Since you want 24 months prior to the latest date, I would use a DATEDIFF(MONTH,,) function in your where clause. It outputs an integer of months and you simply constrain it to be 24 months or less.

SELECT #TEMP.SalesDate
    ,#TEMP.product
    ,#TEMP.SLSqty
    ,DATEDIFF(MONTH,#TEMP.SalesDate,@DATEVAR) [# of months Diff]
FROM #TEMP
WHERE DATEDIFF(MONTH,#TEMP.SalesDate,@DATEVAR) <= 24

OUTPUT:

Now you have to aggregate the sales grouped by the year-month and product. I compute year-month by calculating an integer like 202109 (Sept. 2021)

SELECT --#TEMP.SalesDate --(YOU HAVE TO TAKE THIS OUT FOR THE GROUP BY)
    YEAR(#TEMP.SalesDate)*100+MONTH(#TEMP.SalesDate) [year-month for GROUP BY]
    ,#TEMP.product
    ,SUM(#TEMP.SLSqty) SLSqty
--  ,DATEDIFF(MONTH,#TEMP.SalesDate,@DATEVAR) [# of months Diff] --(YOU HAVE TO TAKE THIS OUT FOR THE GROUP BY)
FROM #TEMP
WHERE DATEDIFF(MONTH,#TEMP.SalesDate,@DATEVAR) <= 24
GROUP BY YEAR(#TEMP.SalesDate)*100+MONTH(#TEMP.SalesDate)
    ,#TEMP.product

Output: