I am working on a query to get sum of (current last 2 month) QTY based on a date field in the table.

CREATE table #temp_Sales
( Client varchar(10),
Sale_Month Date,
Qty int)

Insert into #temp_Sales VALUES
  ( 'AAAA', '2022-06-01',  5 ),
  ( 'AAAA', '2022-05-01',  10 ),
  ( 'AAAA', '2022-05-01',  2 ),
  ( 'AAAA', '2022-04-01',  5 ),
  ( 'AAAA', '2022-02-01',  15),
  ( 'BBBB', '2022-05-01',  2 ),
  ( 'BBBB', '2022-04-01',  4),
  ( 'BBBB', '2022-03-01', 6 ),
  ( 'BBBB', '2022-03-01', 10 ),
  ( 'BBBB', '2022-01-01', 6 ),
  ( 'BBBB', '2021-10-01', 10),
  ( 'BBBB', '2021-09-01', 2 ),
  ( 'BBBB', '2021-11-01', 4 ),
  ( 'BBBB', '2021-08-01', 6),
  ( 'BBBB', '2021-07-01',  8 ),
  ( 'CCCC', '2021-11-01', 2 ),
  ( 'CCCC', '2021-10-01', 3 ),
  ( 'CCCC', '2021-09-01', 30 ),
  ( 'CCCC', '2021-06-01', 4 )

Sample data:

Expected Output:

The Sale_month is not consecutive and same month can appear more than once for a client in the table.

Example : For the Client AAAA and Sale Month 2022-06-01 the qty should include the sum(QTY) of current and last 2 months ( 2022-06-01,2022-05-01 and 2022-04-01) for that client. QTY = 5 10 2 5 = 22

For the client BBBB and Sale month 2022-03-01 . QTY = 6 10 6 = 22

  ;With da AS
  (SELECT *, DATEADD(MM,-2,Sale_month)as last_two_Months FROM #temp_Sales)

  Select Client,Sale_month,Sum(qty) from da
  WHERE Sale_month Between last_two_Months and Sale_month
  GROUP BY Client,Sale_month
  Order by client

Tried the above query. But not working as expected not sure how to group by using last_two_Months and Sale_month. Any help is much appreciated.

CodePudding user response：

Here is one way you could tackle this.

select Sale_Month
    , client
    , Last3Months = max(x.PeriodSales)
from #temp_Sales s
cross apply
(
    select PeriodSales = sum(Qty)
    from #temp_Sales s2
    where s2.Sale_Month >=  dateadd(month, -2, s.Sale_Month)
        and s2.Sale_Month <= s.Sale_Month
        and s2.Client = s.Client
) x
group by Sale_Month
    , client
order by Client
    , Sale_Month desc

CodePudding user response：

group on qty to eliminate duplicates, then join to self like this:

;WITH CTE AS(
    select
        [Client],
        [Sale_Month],
        SUM(QTY) QTY
    from #tmp
    group by 
        [Client],
        [Sale_Month]
)
select a.[Client], a.[Sale_Month], SUM(b.QTY) QTY
from 
CTE a
INNER JOIN CTE b on 
    a.Client = b.Client and 
    DATEDIFF(mm, b.[Sale_Month], a.[Sale_Month]) between 0 and 3
group by 
    a.[Client], a.[Sale_Month]

CodePudding user response：

Firstly, if you don't already have a Calendar Table invest in one.

After that, you can create the data set you need by JOINing data from your Sales table to your calendar table, then getting the sales via a LEFT JOIN and aggregating them. Then you can use a windowed SUM for the last 3 months. Finally, as you seem to not want months that don't exist, filter those back out:

WITH Clients AS(
    SELECT Client,
           MIN(Sale_Month) AS MinMonth,
           MAX(Sale_Month) AS MaxMonth
    FROM dbo.Sales
    GROUP BY Client),
ClientDates AS(
    SELECT C.Client,
           CT.CalendarDate
    FROM Clients C
         JOIN dbo.CalendarTable CT ON C.MinMonth <= CT.CalendarDate
                                  AND C.MaxMonth >= CT.CalendarDate
    WHERE CT.CalenderDay = 1),
ClientSales AS(
    SELECT CD.Client,
           CD.CalendarDate,
           SUM(S.Qty) AS Qty
    FROM ClientDates CD
         LEFT JOIN dbo.Sales S ON CD.Client = S.Client
                              AND CD.CalendarDate = S.Sale_Month
    GROUP BY CD.Client,
             CD.CalendarDate),
Last3 AS(
    SELECT CS.Client,
           CS.CalendarDate,
           CS.Qty,
           SUM(CS.Qty) OVER (PARTITION BY CS.Client ORDER BY CS.CalendarDate
                             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Last3Months
    FROM ClientSales CS)
SELECT L3.Client,
       L3.CalendarDate AS Sale_Month,
       L3.Last3Months
FROM Last3 L3
WHERE L3.Qty IS NOT NULL
ORDER BY Client,
         Sale_Month DESC;