|
|
|
Forum Member
 
Group: Forum Members
Last Login: 8/20/2008 6:42:37 AM
Posts: 48,
Visits: 20
|
|
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.
Regards BS
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 4:54:41 PM
Posts: 534,
Visits: 285
|
|
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
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 8/20/2008 6:42:37 AM
Posts: 48,
Visits: 20
|
|
Dear dnoeth,
I try it. It work ok.
You are genius.
Thank You Very Much! ^_^
Regards BS
|
|
|
|