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

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.

How do I write a WHERE function showing the last 24 months (based on latest date in table not actual date)?
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  |

CodePudding user response：

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:

CodePudding user response：

Here is some oracle sql:

With data ( SalesDate,Product,SLSqty)as(
Select to_date('2018-01-24'),'Product A',25 from dual union all
Select to_date('2019-06-10'),'Product B',10 from dual union all
Select to_date('2019-10-07'),'Product C',4 from dual union all
Select to_date('2020-03-05'),'Product A',20 from dual union all
Select to_date('2021-09-01'),'Product A',50 from dual union all
Select to_date('2021-09-01'),'Product B',10 from dual union all
Select to_date('2021-09-02'),'Product C',3 from dual union all
Select to_date('2021-09-04'),'Product A',50 from dual union all
Select to_date('2021-09-07'),'Product B',10 from dual),
theLatest(SalesDate) as(
select max(SalesDate) from data
)

select to_char(d.SalesDate,'YYYY-MM'),d.Product, sum(SLSqty)
from data d
Join theLatest on d.SalesDate >= add_months(theLatest.SalesDate,-24)
group by to_char(d.SalesDate,'YYYY-MM'),d.Product
order by to_char(d.SalesDate,'YYYY-MM')