Running 3Mth total BY multiple columns -Teradata

Question

I am currently working on monthly customer data where the goal is to get a 3mth running total by month and customer (CUST_NUM+CUST_SEQ_NUM). I went the CTE route and was able to produce the expected result when looking at a single customer. Unfortunately this fails when I bring in other customers. I decided to try a cross join but I'm not having luck implementing it. I also went down the case statement path but that didn't end up being fruitful. I think it is this cross join method that will be the ticket but as I'm the only one on my team that works with CTE (And I'm still learning) Help would be much appreciated!

The Data:

MONTH	cust_num	CUST_SEQ_NUM	NUM_OF_ENTRIES
10/1/2021	222	8	4
11/1/2021	222	8	2
11/1/2021	222	8	3
04/1/2022	222	8	2
10/1/2021	222	3	6
11/1/2021	222	3	2
12/1/2021	222	3	3
05/1/2022	222	3	2

Expected Output:

MONTH	cust_num	CUST_SEQ_NUM	NUM_OF_ENTRIES	3MTH ROLLING TTL
10/1/2021	222	8	4	4
11/1/2021	222	8	2	6
12/1/2021	222	8	3	10
04/1/2022	222	8	2	2
10/1/2021	222	3	6	6
11/1/2021	222	3	2	8
12/1/2021	222	3	3	11
05/1/2022	222	3	2	2

SQL THAT WORKS FOR A SINGLE CUSTOMER:

WITH T1 AS(
    SEL S.*
    , MONTHS_BETWEEN((SEL MIN(EVENT_MTH) FROM TABLE.EVENTS), EVENT_MTH)*-1  AS DIFF_IN_MTHS
    , TD_MONTH_BEGIN(EVENT_MTH) AS MTH_START
    
    FROM TABLE.EVENTS S
    WHERE S.CUST_NUM =  '222'
    AND CUST_SEQ_NUM = 8
    ),
T2 AS (
        SELECT 
        DIFF_IN_MTHS, MTH_START, SUM(NUM_OF_ENTRIES) AS TTL_E_AMT
        FROM T1
        GROUP BY 1,2
        )
SEL DIFF_IN_MTHS
    , MTH_START 
    , TTL_SESS_AMT 
    ,(SELECT SUM(TTL_E_AMT) FROM T2 AS INNER_AMT
      WHERE INNER_AMT.DIFF_IN_MTHS BETWEEN OUTER_AMT.DIFF_IN_MTHS-2 AND OUTER_AMT.DIFF_IN_MTHS) AS 3MTH_TTL
FROM T2 AS OUTER_AMT;

WHERE I'M TRYING TO GO FOR MULTIPLE CUSTOMERS:

WITH T1 AS(
    SEL S.*
    , MONTHS_BETWEEN((SEL MIN(EVENT_MTH) FROM TABLE.EVENTS), EVENT_MTH)*-1  AS DIFF_IN_MTHS
    , TD_MONTH_BEGIN(EVENT_MTH) AS MTH_START
    
    FROM TABLE.EVENTS S
    WHERE S.CUST_NUM =  '222'
    AND CUST_SEQ_NUM = 8
    ),
T2 AS (
        SELECT 
        DIFF_IN_MTHS, MTH_START, SUM(NUM_OF_ENTRIES) AS TTL_E_AMT
        FROM T1
        GROUP BY 1,2
        )
CROSS JOIN
(SEL   CUST_ID
    , CUST_SEQ_NUM
    , DIFF_IN_MTHS
    , MTH_START 
    , TTL_SESS_AMT 
    , (SELECT SUM(TTL_SESS_AMT) FROM T2 AS INNER_AMT
      WHERE INNER_AMT.DIFF_IN_MTHS BETWEEN OUTER_AMT.DIFF_IN_MTHS-2 AND OUTER_AMT.DIFF_IN_MTHS) AS RUNNING_3MTH_TTL
      
FROM T2 AS OUTER_AMT) T3

WHERE T2.CUST_SEQ_NUM = T3.CUST_SEQ_NUM
AND T2.CUST_NUM = T3.CUST_NUM

Running 3Mth total BY multiple columns -Teradata

0 Answers0