Database
Enthusiast

## Re: Cumulative Sum by distinct customer ID by year

Hello All ,

Hi All ,

In the same example as above , I will have to calculate the column wise "SUM" as mentioned in 'Bold" in the example below.

(col1) (Date1) (Date2) (Sum for days1-10.) (Sum days11-20) (sumdays21-30)    Total

SUM                                533                          458                    543                1543

A.        Xx.     Vvv.          532                                                                             532

B.      Cc          Cv.                                           453                                            453

c.       Ft.         As.                                                                     543                  543

D       hh          xx              1                                                                              1

E        ff           mm                                             5                                             5

I want this SUM to be as the first row in the entire data set ,

I think of something like below that's in bold.... will this work? or is there any other function or workaround that I could use to calculate this , Please help

SELECT

CAST(NULL AS VARCHAR(20)) AS CUST,

CAST(NULL AS DATE FORMAT 'DD-MMM'YYYY') AS DT1,

CAST(NULL AS DATE FORMAT 'DD-MMM'YYYY') AS DT2,

SUM(SUM_1TO10) , SUM(SUM_11TO20),SUM(SUM_21TO30),SUM(SUM_TOT )

UNION

SEL

CUST, DT1, DT2,

CASE   WHEN DIFF >=1 AND DIFF <=10 THEN DIFF ELSE 0 END AS SUM_1TO10,

CASE   WHEN DIFF >=11 AND DIFF <=20 THEN DIFF ELSE 0 END AS SUM_11TO20,

CASE   WHEN DIFF >=21 AND DIFF <=30 THEN DIFF ELSE 0 END AS SUM_21TO30,

CASE   WHEN DIFF <1 OR  DIFF >30 THEN DIFF ELSE 0 END AS SUM_DEF,

SUM_1TO10+SUM_11TO20+SUM_21TO30+SUM_DEF AS SUM_TOT FROM

(SEL   CUST, DT1, DT2,

SUM(

CASE

WHEN DT1 > DT2 THEN DT1-DT2

WHEN DT2 > DT1 THEN DT2-DT1

ELSE 0

END) OVER (PARTITION BY CUST ORDER BY 1 ) AS DIFF

FROM

(

SEL * FROM   (SEL 'A' AS CUST, DATE '2001-01-01' AS DT1, DATE '2001-01-11' AS DT2) A

UNION ALL

SEL * FROM   (SEL 'B' AS CUST, DATE '2001-01-21' AS DT1, DATE '2001-01-01' AS DT2) B

UNION ALL

SEL * FROM   (SEL   'C' AS CUST, DATE '2001-01-01' AS DT1, DATE '2001-01-31' AS DT2) C

UNION ALL

SEL * FROM   (SEL 'A' AS CUST, DATE '2001-02-21' AS DT1, DATE '2001-01-11' AS DT2) A

) A

) A

Regards,

Shavyani