CSUM or SUM: Can work with this?

Analytics

CSUM or SUM: Can work with this?

Dear Who Have The Idea:

Currently I have a table with content like this:
*************************************************************
Cust_Id || Start_Date || End_Date || GRP || (End_Dt - Start_Dt)
12158574 || 2007-04-22 || 2007-04-22 || 1 || 1
12158574 || 2007-04-23 || 2007-04-23 || 1 || 1
12158574 || 2007-04-24 || 2007-04-25 || 1 || 2
12158574 || 2007-04-26 || 2007-04-26 || 2 || 1
12158574 || 2007-04-28 || 2007-04-28 || 1 || 1
12158574 || 2007-04-29 || 2007-05-01 || 1 || 3
12158574 || 2007-05-02 || 2007-05-03 || 1 || 2
12158574 || 2007-05-04 || 2007-05-07 || 1 || 4
12158574 || 2007-05-08 || 2007-06-02 || 1 || 26
*************************************************************

I need to Accumulate_Sum(End_Dt - Start_Dt) partition by Cust_Id, ascending order by Start_Date and Partition again by GRP.
The result should look like this:
*************************************************************
Cust_Id || Start_Date || End_Date || GRP || (End_Dt-Start_Dt) || CUMM_SUM
12158574 || 2007-04-22 || 2007-04-22 || 1 || 1 || 1
12158574 || 2007-04-23 || 2007-04-23 || 1 || 1 || 2
12158574 || 2007-04-24 || 2007-04-25 || 1 || 2 || 4
12158574 || 2007-04-26 || 2007-04-26 || 2 || 1 || 1
12158574 || 2007-04-28 || 2007-04-28 || 1 || 1 || 1
12158574 || 2007-04-29 || 2007-05-01 || 1 || 3 || 4
12158574 || 2007-05-02 || 2007-05-03 || 1 || 2 || 6
12158574 || 2007-05-04 || 2007-05-07 || 1 || 4 || 10
12158574 || 2007-05-08 || 2007-06-02 || 1 || 26 || 36
*************************************************************

But unfortunately, CSUM and SUM unable to do this.

Could any one can help?

Many Thanks in Advance.
2 REPLIES
N/A

Re: CSUM or SUM: Can work with this?

Hi Benjamin,
OLAP again ;-)
But a bit more complicated, because you want a reset if the cust_id changes *OR* grp changes within ordered rows by cust_id, start_date

SELECT
Cust_Id, Start_Date, End_Date, GRP, #days,
SUM(#days) OVER (PARTITION BY Cust_Id, rn1 - rn2 ORDER BY start_date
ROWS UNBOUNDED PRECEDING)

FROM
(
SELECT
Cust_Id, Start_Date, End_Date, GRP, End_date - start_date + 1 AS #days,
ROW_NUMBER() OVER (PARTITION BY Cust_Id ORDER BY start_date) AS rn1,
ROW_NUMBER() OVER (PARTITION BY Cust_Id ORDER BY grp, start_date) AS rn2
FROM bsee
) dt
ORDER BY 1,2;

Dieter

Re: CSUM or SUM: Can work with this?

Dear dnoeth,

I try it. It work ok.

You are genius.

Thank You Very Much! ^_^