Home » SQL & PL/SQL » SQL & PL/SQL » Monthly Running Totals (merged)
| Monthly Running Totals (merged) [message #406647] |
Fri, 05 June 2009 03:09  |
pjp1
Messages: 3
Registered: June 2009
|
Junior Member |
|
|
Hi,
I am trying to understand the best way to create some cumulative running totals.
My data looks something like this :-
client order_no order_dt order_amt
------ -------- -------- ---------
001 001 01-JAN-2009 100.00
001 002 15-JAN-2009 100.00
001 003 21-MAR-2009 200.00
001 003 31-DEC-2009 100.00
002 001 01-MAY-2009 100.00
I need to create output in the following format :-
client 200901 200902 200903 200904 200905 200906 200907 200908 200910 200911 200912
------ -------- --------- -------- -------- -------- -------- -------- -------- -------- -------- ---------
001 200.00 200.00 400.00 400.00 400.00 400.00 400.00 400.00 400.00 400.00 500.00
002 0.00 0.00 0.00 0.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00
I can create the answer I need with a series of in-line views and date ranges etc but this involves multiple passes of the data which on large data sets will be quite expensive.
It feels like there might be some creative way i could use windowing functions to get the same result but all my attempts thus far have failed.
Does anyone have any ideas about how to do this ?
Any help greatly appreciated
Phil
|
|
|
|
|
|
| Re: Monthly Running Totals (merged) [message #407465 is a reply to message #406647] |
Wed, 10 June 2009 05:15  |
pjp1
Messages: 3
Registered: June 2009
|
Junior Member |
|
|
ok i got so far...
I have data that looks like :-
CUSTOMER_ID ORDER_ID ORDER_DT ORDER_AMT
----------- -------- --------- ---------
1 1 01-FEB-09 100
1 2 01-MAR-09 200
1 3 30-MAY-09 100
And I want to create output that looks like :-
CUSTOMER_ID 200906 200905 200904 200903 200902 200901
------------ ------ ------ ------ ------ ------ ------
1 400 400 300 300 100 (null)
The code below appears to work and give the required results but
it takes a long time when i increase the number of months and number
of records. Its the second half of the union clause the problem
as without this it runs fast but returns results like this:-
CUSTOMER_ID 200906 200905 200904 200903 200902 200901
----------- ------ ------ ------ ------ ------ ------
1 (null) 400 (null) 300 100 (null)
My questions are :-
1) Is the logic ok or is there a better way ?
2) If I remove the second part of the union is there a way to
populate the the missing data above ?
Any suggestions would be appreciated
Phil
CREATE TABLE ORDERS1
( CUSTOMER_ID NUMBER,
ORDER_ID NUMBER,
ORDER_DT DATE,
ORDER_AMT NUMBER
) ;
Insert into ORDERS1 (CUSTOMER_ID,ORDER_ID,ORDER_DT,ORDER_AMT) values (1,1,to_date('01-FEB-09','DD-MON-RR'),100);
Insert into ORDERS1 (CUSTOMER_ID,ORDER_ID,ORDER_DT,ORDER_AMT) values (1,2,to_date('01-MAR-09','DD-MON-RR'),200);
Insert into ORDERS1 (CUSTOMER_ID,ORDER_ID,ORDER_DT,ORDER_AMT) values (1,3,to_date('30-JUN-09','DD-MON-RR'),100);
with
dt_range as (select start_dt,end_dt, to_char(start_dt, 'YYYYMM')mth
from( select add_months(m, -level) start_dt, add_months(m,-level+1)-1 end_dt
from (select trunc(add_months(sysdate,1), 'MONTH') m from dual)
connect by level <= 6 )
),
ord_ttl_mths as (select customer_id,sum(order_amt)order_ttl,mth
from
(select customer_id,order_amt,mth
from ORDERS1,dt_range
where order_dt between start_dt and end_dt
union all
select customer_id, 0 order_amt,mth
from ORDERS1,dt_range
where order_dt < start_dt
)
group by customer_id ,mth),
ord_sum_mths as ( select customer_id ,mth, sum(order_ttl) over (partition by customer_id order by mth) order_ttl
from ord_ttl_mths)
select customer_id,
sum(decode(mth,'200906',order_ttl))"200906",
sum(decode(mth,'200905',order_ttl))"200905",
sum(decode(mth,'200904',order_ttl))"200904",
sum(decode(mth,'200903',order_ttl))"200903",
sum(decode(mth,'200902',order_ttl))"200902",
sum(decode(mth,'200901',order_ttl))"200901"
from
ord_sum_mths
group by customer_id ;
|
|
|
|
|
|
| Re: Monthly Running Totals (merged) [message #407527 is a reply to message #406652] |
Wed, 10 June 2009 07:11 |
pjp1
Messages: 3
Registered: June 2009
|
Junior Member |
|
|
ok any better ?
Does anyone have anything useful to say or should i just keep trying to get the formatting of this post correct 
ok i got so far...
I have data that looks like :-
CUSTOMER_ID ORDER_ID ORDER_DT ORDER_AMT
----------- -------- --------- ---------
1 1 01-FEB-09 100
1 2 01-MAR-09 200
1 3 30-MAY-09 100
And I want to create output that looks like :-
CUSTOMER_ID 200906 200905 200904 200903 200902 200901
------------ ------ ------ ------ ------ ------ ------
1 400 400 300 300 100 (null)
The code below appears to work and give the required results but
it takes a long time when i increase the number of months and number
of records. Its the second half of the union clause the problem
as without this it runs fast but returns results like this:-
CUSTOMER_ID 200906 200905 200904 200903 200902 200901
----------- ------ ------ ------ ------ ------ ------
1 (null) 400 (null) 300 100 (null)
My questions are :-
1) Is the logic ok or is there a better way ?
2) If I remove the second part of the union is there a way to
populate the the missing data above ?
Any suggestions would be appreciated
Phil
CREATE TABLE ORDERS1
( CUSTOMER_ID NUMBER,
ORDER_ID NUMBER,
ORDER_DT DATE,
ORDER_AMT NUMBER
) ;
Insert into ORDERS1 (CUSTOMER_ID,ORDER_ID,ORDER_DT,ORDER_AMT) values (1,1,to_date('01-FEB-09','DD-MON-RR'),100);
Insert into ORDERS1 (CUSTOMER_ID,ORDER_ID,ORDER_DT,ORDER_AMT) values (1,2,to_date('01-MAR-09','DD-MON-RR'),200);
Insert into ORDERS1 (CUSTOMER_ID,ORDER_ID,ORDER_DT,ORDER_AMT) values (1,3,to_date('30-JUN-09','DD-MON-RR'),100);
with
dt_range as (select start_dt,end_dt, to_char(start_dt, 'YYYYMM')mth
from( select add_months(m, -level) start_dt, add_months(m,-level+1)-1 end_dt
from (select trunc(add_months(sysdate,1), 'MONTH') m from dual)
connect by level <= 6 )
),
ord_ttl_mths as (select customer_id,sum(order_amt)order_ttl,mth
from
(select customer_id,order_amt,mth
from ORDERS1,dt_range
where order_dt between start_dt and end_dt
union all
select customer_id, 0 order_amt,mth
from ORDERS1,dt_range
where order_dt < start_dt
)
group by customer_id ,mth),
ord_sum_mths as ( select customer_id ,mth, sum(order_ttl) over (partition by customer_id order by mth) order_ttl
from ord_ttl_mths)
select customer_id,
sum(decode(mth,'200906',order_ttl))"200906",
sum(decode(mth,'200905',order_ttl))"200905",
sum(decode(mth,'200904',order_ttl))"200904",
sum(decode(mth,'200903',order_ttl))"200903",
sum(decode(mth,'200902',order_ttl))"200902",
sum(decode(mth,'200901',order_ttl))"200901"
from
ord_sum_mths
group by customer_id ; |
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 06:02:10 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
