Home » SQL & PL/SQL » SQL & PL/SQL » Running Total
Running Total [message #218443] Thu, 08 February 2007 04:09 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to get running total out of some data.
Please see the data below.
STYLE	        FCST	Week Day	DUE DATE  QTY	TOH	WeekOnHand
PALENCIA	13349		  		        142225	10.7
PALENCIA		2/4/2007			128876	9.7
PALENCIA		2/11/2007			115527	8.7
PALENCIA		2/18/2007			102178	7.7
PALENCIA		2/25/2007	3/1/2007 100000	188829	14.1
PALENCIA		3/4/2007			175480	13.1
PALENCIA		3/11/2007			162131	12.1
PALENCIA		3/18/2007			148782	11.1
PALENCIA		3/25/2007			135433	10.1
PALENCIA		4/1/2007			122084	9.1
PALENCIA		4/8/2007			108735	8.1
PALENCIA		4/15/2007			95386	7.1
PALENCIA		4/22/2007			82037	6.1
PALENCIA		4/29/2007			68688	5.1


Please help me as how can I work on the running total.
You can use the two tables created for this.
 create table tab1(
  STYLE VARCHAR2(10),
  FCST NUMBER(10),
  duedate varchar2(10),
  QTY NUMBER(10),
  TOH NUMBER(10),
  WOH NUMBER(7,2))

insert into tab1 values ('PALENCIA','13349','3/1/2007 ','100000','142225','10.7')


create table wk as select
 to_char(TRUNC((SYSDATE-7),'DAY') + p.pivot, 'MM-DD-YY') sdate
 from dual d,
 (select rownum pivot from all_objects) p
 where
 (select trunc((sysdate-7),'day') from dual) + p.pivot <= (select trunc((sysdate-7),'day') from dual) + 91
 and
 to_number(to_char((select trunc((sysdate-7),'day') from dual) + p.pivot, 'D')) in (1)

The first row is fixed and based on that value I have to generate the values of the later rows using running total.
Please guide me on this.



If you use the select statement as :
 select STYLE, fcst,sdate,
 decode(trunc(TO_DATE(duedate,'mm-dd-yy'),'day'),TO_DATE(sdate,'mm-dd-yy'),duedate,NULL)dt,
 decode(trunc(TO_DATE(duedate,'mm-dd-yy'),'day'),TO_DATE(sdate,'mm-dd-yy'),qty,NULL) qt,
 toh,woh from wk, tab1

You will find that 3/1/2007, 100000 falls under the week 02-25-07 which is Sunday and first day of the week for the duedate 3/1/2007.
So the display needs to be that the due date and qty needs to be displayed only for the week in which it falls.

Now the running total needs to be worked out. Right now the last two columns are coming all equal.
But it needs to follow the formula for TOH and WOH.
The column TOH is calculated as SUM(TOH+QTY-FCST)
and the column weekonhand is calculated as SUM(WOH/FCST)

So the output needs to be come as :
STYLE	        FCST	Week Day	DUE DATE  QTY	TOH	WeekOnHand
PALENCIA	13349		  		        142225	10.7
PALENCIA		2/4/2007			128876	9.7
PALENCIA		2/11/2007			115527	8.7
PALENCIA		2/18/2007			102178	7.7
PALENCIA		2/25/2007	3/1/2007 100000	188829	14.1
PALENCIA		3/4/2007			175480	13.1
PALENCIA		3/11/2007			162131	12.1
PALENCIA		3/18/2007			148782	11.1
PALENCIA		3/25/2007			135433	10.1
PALENCIA		4/1/2007			122084	9.1
PALENCIA		4/8/2007			108735	8.1
PALENCIA		4/15/2007			95386	7.1
PALENCIA		4/22/2007			82037	6.1
PALENCIA		4/29/2007			68688	5.1


Please guide me on same.

Regards,
Mona

[Updated on: Thu, 08 February 2007 22:38]

Report message to a moderator

Re: Running Total [message #218447 is a reply to message #218443] Thu, 08 February 2007 04:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You'll get better responses to your query if you don't post parts of it in proprietary data formats (particularly ones with dubious security reputations)
Re: Running Total [message #218448 is a reply to message #218447] Thu, 08 February 2007 04:50 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks JRow,
I have removed the excel sheet. It was only to give a better idea of the question.
I have pasted the data on the screen only and hope to get some good response.

Thanks,
Mona
Re: Running Total [message #218470 is a reply to message #218448] Thu, 08 February 2007 06:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Where does the value QTY come from:
 the column TOH is calculated as SUM(TOH+QTY-FCST)
Re: Running Total [message #218475 is a reply to message #218448] Thu, 08 February 2007 06:21 Go to previous messageGo to next message
ashuj20
Messages: 12
Registered: October 2005
Location: new delhi
Junior Member
Hi Mona,

Please explain the problem more clearly. i am not clear with your question.

what i can understand is :

You are inserting records in four coulmns using:
insert into tab1 values ('PALENCIA','13349','142225','10.7');

after that you want to update two more columns with some calculations.


tell me if i am right, if yes then i will reply with answer else please explain.


Thanks
Ashish



Re: Running Total [message #218601 is a reply to message #218470] Thu, 08 February 2007 22:40 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi JRow,
I have made some changes to my question. It explain things better now. I have also edited regarding QTY.

PLease guide me on this.

Regards,
Mona
Re: Running Total [message #218650 is a reply to message #218601] Fri, 09 February 2007 03:06 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
@monasingh: Why is duedate column created as varchar2 instead of proper date?
Re: Running Total [message #218674 is a reply to message #218650] Fri, 09 February 2007 04:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, your calculation for thw Weekon Hand doesn't match the data - the data show it as a simple ratio of TOH to FCST, so that's what I've given you.
SQL> select style
  2        ,fcst
  3        ,sdate
  4        ,qt
  5        ,toh - 
  6         sum(fcst) over ( order by sdate rows between unbounded preceding and current row) +
  7         sum(nvl(qt,0)) over ( order by sdate rows between unbounded preceding and current row) toh
  8        ,round((toh - 
  9                sum(fcst) over ( order by sdate rows between unbounded preceding and current row) +
 10                sum(nvl(qt,0)) over ( order by sdate rows between unbounded preceding and current row))
 11                / fcst,1) woh
 12  from (
 13  select STYLE
 14        ,fcst
 15        ,sdate
 16        ,decode(trunc(TO_DATE(duedate,'mm-dd-yy'),'day'),TO_DATE(sdate,'mm-dd-yy'),duedate,NULL) dt
 17        ,decode(trunc(TO_DATE(duedate,'mm-dd-yy'),'day'),TO_DATE(sdate,'mm-dd-yy'),qty,NULL) qt
 18        ,toh
 19        ,woh 
 20  from wk, tab1)
 21  order by sdate;

STYLE            FCST SDATE            QT        TOH        WOH
---------- ---------- -------- ---------- ---------- ----------
PALENCIA        13349 02-05-07                128876        9.7
PALENCIA        13349 02-12-07                115527        8.7
PALENCIA        13349 02-19-07                102178        7.7
PALENCIA        13349 02-26-07     100000     188829       14.1
PALENCIA        13349 03-05-07                175480       13.1
PALENCIA        13349 03-12-07                162131       12.1
PALENCIA        13349 03-19-07                148782       11.1
PALENCIA        13349 03-26-07                135433       10.1
PALENCIA        13349 04-02-07                122084        9.1
PALENCIA        13349 04-09-07                108735        8.1
PALENCIA        13349 04-16-07                 95386        7.1
PALENCIA        13349 04-23-07                 82037        6.1
PALENCIA        13349 04-30-07                 68688        5.1

13 rows selected.
Re: Running Total [message #218679 is a reply to message #218674] Fri, 09 February 2007 04:58 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks a lot JRow for looking into this and solving the problem.
This is what I was looking for.

Regards,
Mona
Re: Running Total [message #219832 is a reply to message #218674] Fri, 16 February 2007 04:32 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Jrow,
Thanks for your help.
If I insert three rows in the table tab1,
create table tab1(
  STYLE VARCHAR2(10),
  FCST NUMBER(10),
  duedate varchar2(10),
  QTY NUMBER(10),
  TOH NUMBER(10),
  WOH NUMBER(7,2))

insert into tab1 values ('PALENCIA','13349','3/19/2007 ','5000','142225','10.7')
insert into tab1 values ('PALENCIA','13349','3/1/2007 ','100000','142225','10.7')
insert into tab1 values ('PALENCIA','13349','3/19/2007 ','6000','142225','10.7')


And then select the query using
select DISTINCT STYLE, fcst,sdate,
 decode(trunc(TO_DATE(duedate,'mm-dd-yy'),'day'),TO_DATE(sdate,'mm-dd-yy'),duedate,NULL)dt,
 decode(trunc(TO_DATE(duedate,'mm-dd-yy'),'day'),TO_DATE(sdate,'mm-dd-yy'),qty,NULL) qt,
 toh,woh from wk, tab1


The Create table command for wk is given above.

The data I get is of 16 rows. WHile it should have been only 14 rows.
I am getting 2 extra rows that is :
PALENCIA        13349 02-25-07                           142225       10.7
PALENCIA        13349 03-18-07                           142225       10.7

I am getting the date 02-25-07 and 03-18-07 repeated even when it already has ben shown with data.
What should I do to eliminate this data.

Please advice.

Thanks,
Mona

[Updated on: Fri, 16 February 2007 04:32]

Report message to a moderator

Previous Topic: guys oracle 10g migration problem..pls help :-(!!
Next Topic: Data Migration Problem
Goto Forum:
  


Current Time: Fri Dec 02 18:34:15 CST 2016

Total time taken to generate the page: 0.47379 seconds