Home » SQL & PL/SQL » SQL & PL/SQL » Monthly Running Totals (merged)
Monthly Running Totals (merged) [message #406647] Fri, 05 June 2009 03:09 Go to next message
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 #406652 is a reply to message #406647] Fri, 05 June 2009 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do it with the followings:
- SUM analytical function
- row generator for calendar
- pivot technics

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Monthly Running Totals (merged) [message #407465 is a reply to message #406647] Wed, 10 June 2009 05:15 Go to previous messageGo to next message
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 #407495 is a reply to message #407465] Wed, 10 June 2009 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't read post that is not formatted.

Regards
Michel
Re: Monthly Running Totals (merged) [message #407527 is a reply to message #406652] Wed, 10 June 2009 07:11 Go to previous messageGo to next message
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 Laughing
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 #408073 is a reply to message #406647] Sat, 13 June 2009 10:29 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Kevin Meade's Orafaq

A Simple Example of Oracle Analytics: Running Totals

Example of Data Pivots in SQL (rows to columns and columns to rows)

The SQL WITH Clause, (Order Your Thoughts, Reuse Your Code)

These OraFAQ articles cover the topics mentioned by Michel.

Good luck, Kevin
Previous Topic: int to varchar problem ?
Next Topic: How to create Charts ino Excel using stored procedure
Goto Forum:
  


Current Time: Sun Dec 04 08:39:42 CST 2016

Total time taken to generate the page: 0.22595 seconds