Rolling Sum Previous 3 Months SQL Server

Rolling sum previous 3 months SQL Server

Another approach using Outer JOIN

;WITH cte
     AS (SELECT year,
                period,
                country,
                Sum(value) AS sumvalue
         FROM   Yourtable 
         GROUP  BY year,
                   period,
                   country)
SELECT a.Year,
       a.Period,
       a.Country,
       a.sumvalue + Isnull(Sum(b.sumvalue), 0) as [3M Value]
FROM   cte a
       LEFT JOIN cte b
              ON a.Country = b.Country
                 AND Datefromparts(b.[Year], b.Period, 1) IN ( Dateadd(mm, -1, Datefromparts(a.[Year], a.Period, 1)), Dateadd(mm, -2, Datefromparts(a.[Year], a.Period, 1)) )
GROUP  BY a.Year,
          a.Period,
          a.Country,
          a.sumvalue 

• Live Demo

SQL Running total previous 3 months by date and id

Something like this might work:

SELECT a.Partno, a.EndofMonth, SUM(b.AA) as AA
FROM q3 a, q3 b
WHERE a.Partno = b.Partno
    AND DATEDIFF(month, b.endOfMonth, a.endOfMonth) < 4
GROUP BY a.Partno, b.Partno

This assumes that endOfMonth is in datetime format, if it is not you will have to use convert(). Note that you might have to replace DATEDIFF() depending on what implementation you are using.

I haven't tested this, so I might be way off. It has been a while since I worked with SQL. Hopefully you can get it working by messing around with it a bit, and if not then maybe it will inspire you to write something better. Let me know how it goes!

Last 3 months Sales rolling SQl, MYSQL

thanks for your help, i found an answer to the question

SELECT
Region, Date, Sales,
lag(Sales,0) OVER (
PARTITION BY Region
ORDER BY Date ) + lag(Sales,1) OVER (
PARTITION BY Region
ORDER BY Date ) + lag(Sales,2) OVER (
PARTITION BY Region
ORDER BY Date ) L3M_Sales
FROM
Table;

How to SUM() for past 3 months for every field in a column

Try a self-join on Item and t2.Date <= t1.Date and t2.Date >= dateadd("m",-3,t1.Date).

SELECT
    t1.Date,
    t1.Item,
    SUM(t2.Count) three_sum
FROM
    Table1 t1
    JOIN Table1 t2 
        ON t1.Item = t2.Item
        AND t2.Date <= t1.Date
        AND t2.Date >= dateadd("m",-3,t1.Date)
GROUP BY t1.Date, t1.Item
ORDER BY t1.Date ASC

SQL Server - Cumulative Sum over Last 12 Months, but starting from the Last Month (SQL Server 18)

You seem to understand window functions pretty well. You just have to adjust the window frame:

SUM(Amount) OVER (PARTITION BY ID
                  ORDER BY Date_Month
                  ROWS BETWEEN 12 PRECEDING AND 1 PRECEDING
                 ) 

Rolling sum previous 12 months per month (SQL- Snowflake)

Seems to work - but you need an additional nested query to build the running sum and a filter value to remove the first 12 months:

WITH
-- your input, don't use in final query ..
indata(dt,New_Customers) AS (
          SELECT DATE '2021-04-01',4 UNION ALL SELECT DATE '2021-05-01',1
UNION ALL SELECT DATE '2021-06-01',2 UNION ALL SELECT DATE '2021-07-01',6
UNION ALL SELECT DATE '2021-08-01',3 UNION ALL SELECT DATE '2021-09-01',2
UNION ALL SELECT DATE '2021-10-01',3 UNION ALL SELECT DATE '2021-11-01',8
UNION ALL SELECT DATE '2021-12-01',3 UNION ALL SELECT DATE '2022-01-01',4
UNION ALL SELECT DATE '2022-02-01',0 UNION ALL SELECT DATE '2022-03-01',3
UNION ALL SELECT DATE '2022-04-01',3 UNION ALL SELECT DATE '2022-05-01',2
UNION ALL SELECT DATE '2022-06-01',3 UNION ALL SELECT DATE '2022-07-01',1
UNION ALL SELECT DATE '2022-08-01',3 UNION ALL SELECT DATE '2022-09-01',2
UNION ALL SELECT DATE '2022-10-01',3 UNION ALL SELECT DATE '2022-11-01',1
UNION ALL SELECT DATE '2022-12-01',6 UNION ALL SELECT DATE '2023-01-01',8
UNION ALL SELECT DATE '2023-02-01',4
)
-- real query starts here, replace following comma with "WITH"
,
-- need to build the row number and the running sum first in a subselect ...
olap AS (
  SELECT
    dt
  , SUM(new_customers) OVER(ORDER BY dt ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS running_cust_count
  , ROW_NUMBER()       OVER(ORDER BY dt                                          ) AS rownum
  FROM indata
) 
SELECT
  dt AS rep_date
, running_cust_count
FROM olap
WHERE rownum >= 12
;
-- out   rep_date  | running_cust_count 
-- out ------------+--------------------
-- out  2022-03-01 |                 39
-- out  2022-04-01 |                 38
-- out  2022-05-01 |                 39
-- out  2022-06-01 |                 40
-- out  2022-07-01 |                 35
-- out  2022-08-01 |                 35
-- out  2022-09-01 |                 35
-- out  2022-10-01 |                 35
-- out  2022-11-01 |                 28
-- out  2022-12-01 |                 31
-- out  2023-01-01 |                 35
-- out  2023-02-01 |                 39

---

Related Topics