CODEWITHSUNDEEP
sqloracle-databaserolling-computation
NewTechGuy
NewTechGuy

Reputation: 17

Rolling Sum over 90 Calendar date (look back) from given current date in SQL/ Oracle

**Calender_Date**   **Customer_ID**     **Amount**
01-Jan-21          20211003              59
01-Jan-21          20211005              100
27-Jan-21          20211003              25
25-Feb-21          20211003              188
23-Mar-21          20211005              67
24-Apr-21          20211009              43
25-May-21          20211013              21
26-May-21          20211013              89
11-Jul-21          20211009              65
28-Aug-21          20211003              90

Needed: Considering Customer_ID - Rolling sum over 90 Calendar look back date (from each given current date).

OutPut Required or Desired Result

**Calender_Date**   **Customer_ID** **Amount**  **SumofLookbackRolling90DayAmount**
1-Jan-21           20211003            59           59
1-Jan-21           20211005            100          100
27-Jan-21          20211003            25           84
25-Feb-21          20211003            188          272
23-Mar-21          20211005            67           167
24-Apr-21          20211009            43           43
25-May-21          20211013            21           21
26-May-21          20211013            89           110
11-Jul-21          20211009            65           108
28-Aug-21          20211003            90           90

Upvotes: 1

Views: 807

Answers (1)

MT0
MT0

Reputation: 167962

You can use an analytic function with a range window:

SELECT t.*,
       SUM(amount) OVER (
         PARTITION BY customer_id
         ORDER BY calendar_date
         RANGE BETWEEN INTERVAL '90' DAY PRECEDING
                   AND INTERVAL '0' DAY FOLLOWING
       ) AS rolling_sum
FROM   table_name t

Which, for your sample data:

CREATE TABLE table_name (Calendar_Date, Customer_ID, Amount) AS
SELECT DATE '2021-01-01', 20211003,  59 FROM DUAL UNION ALL
SELECT DATE '2021-01-01', 20211005, 100 FROM DUAL UNION ALL
SELECT DATE '2021-01-27', 20211003,  25 FROM DUAL UNION ALL
SELECT DATE '2021-02-25', 20211003, 188 FROM DUAL UNION ALL
SELECT DATE '2021-03-23', 20211005,  67 FROM DUAL UNION ALL
SELECT DATE '2021-04-24', 20211009,  43 FROM DUAL UNION ALL
SELECT DATE '2021-05-25', 20211013,  21 FROM DUAL UNION ALL
SELECT DATE '2021-05-26', 20211013,  89 FROM DUAL UNION ALL
SELECT DATE '2021-07-11', 20211009,  65 FROM DUAL UNION ALL
SELECT DATE '2021-08-28', 20211003,  90 FROM DUAL;

Outputs:

CALENDAR_DATE CUSTOMER_ID AMOUNT ROLLING_SUM
01-JAN-21 20211003 59 59
27-JAN-21 20211003 25 84
25-FEB-21 20211003 188 272
28-AUG-21 20211003 90 90
01-JAN-21 20211005 100 100
23-MAR-21 20211005 67 167
24-APR-21 20211009 43 43
11-JUL-21 20211009 65 108
25-MAY-21 20211013 21 21
26-MAY-21 20211013 89 110

db<>fiddle here

Upvotes: 5

Related Questions