Home » SQL & PL/SQL » SQL & PL/SQL » How to create a Column that will "carry over" values
How to create a Column that will "carry over" values [message #217566] Fri, 02 February 2007 16:58 Go to next message
msisam
Messages: 3
Registered: February 2007
Junior Member
I have the first 5 colums of the following table and I would like to create the last column (Carryover). The calculation for carryover (current row) is PRIOR_DAYS_OF_SUPPLY(current row)-DAYS_BTW_REFILL(current row)-carryover (prior row)
This is tricky since the value is dependent on the prior row value of the SAME column. I have tried the window functions but still cannot get it to work.

Excel Calculations in the attached file for clarification.

P_ID RX_DATE DAYS_OF_SUPPLY PRIOR_DAYS_OF_SUPPLY DAYS_BTW_REFILL CARRYOVER
100 10/20/2005 30 0
100 11/28/2005 30 30 39 0
100 12/21/2005 30 30 23 7
100 1/13/2006 60 30 23 14
100 3/3/2006 30 60 49 25
100 4/14/2006 30 30 42 13
100 6/9/2006 30 30 56 0
100 7/7/2006 30 30 28 2
100 7/31/2006 30 30 24 8
200 10/20/2005 30 0
200 11/28/2005 30 30 30 0
200 12/21/2005 30 30 35 0
200 1/13/2006 60 30 25 5
200 3/3/2006 30 60 70 0
200 4/14/2006 45 30 32 0
200 6/9/2006 30 45 41 4
200 7/7/2006 30 30 30 4
200 7/31/2006 30 30 32 2


Any help would be greatly appreciated,
Sam
Re: How to create a Column that will "carry over" values [message #217567 is a reply to message #217566] Fri, 02 February 2007 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
1) you really should learn how to use "code" tags.
2) it is a bad idea to populate a column with computable values.
3) this can be done via a VIEW selecting against the table twice; once for current day & the other against previous day.
Re: How to create a Column that will "carry over" values [message #217569 is a reply to message #217567] Fri, 02 February 2007 17:22 Go to previous messageGo to next message
msisam
Messages: 3
Registered: February 2007
Junior Member
Thanks for your reply and apologies for the misuse of the tags. This is my first time submitting a question to a forum. As for the quesiton at hand, having this dine in a view is perfectly acceptable, I just cannot figure out how. I am not sure how I would get the answer by selecting the table twice. there is no problem with getting a prior row's value. I actually did this to get the PRIOR_DAYS_OF_SUPPLY
sum(DAYS_OF_SUPPLY) over (partition by p_id order by RX_DATE
                         rows between 1 PRECEDING AND 1 PRECEDING)
/*may not be ideal but it works*/
The problem that I am having is creating a calculation based off of a value in the SAME column.

Sam
Re: How to create a Column that will "carry over" values [message #217691 is a reply to message #217566] Sun, 04 February 2007 18:23 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

create table foo (P_ID number(3) ,R_DT date , DOS number(3) ,
PDOS number(3), DBTWR number(3), CO number(3));

insert into foo values (100,to_date('10/20/2005','mm/dd/yyyy'),'30',0,null,null );
insert into foo values (100,to_date('11/28/2005','mm/dd/yyyy'),'30','30','39',0);
insert into foo values (100,to_date('12/21/2005','mm/dd/yyyy'),'30','30','23',7);
insert into foo values (100,to_date('01/13/2006','mm/dd/yyyy'),'60','30','23',14);
insert into foo values (100,to_date('03/3/2006','mm/dd/yyyy'),'30','60','49',25);
insert into foo values (100,to_date('04/14/2006','mm/dd/yyyy'),'30','30','42',13);
insert into foo values (100,to_date('06/9/2006','mm/dd/yyyy'),'30','30','56',0);
insert into foo values (100,to_date('07/7/2006','mm/dd/yyyy'),'30','30','28',2);
insert into foo values (100,to_date('07/31/2006','mm/dd/yyyy'),'30','30','24',8);
insert into foo values (200,to_date('10/20/2005','mm/dd/yyyy'),'30',0,null,null);
insert into foo values (200,to_date('11/28/2005','mm/dd/yyyy'),'30','30','30',0);
insert into foo values (200,to_date('12/21/2005','mm/dd/yyyy'),'30','30','35',0);
insert into foo values (200,to_date('01/13/2006','mm/dd/yyyy'),'60','30','25',5);
insert into foo values (200,to_date('03/3/2006','mm/dd/yyyy'),'30','60','70',0);
insert into foo values (200,to_date('04/14/2006','mm/dd/yyyy'),'45','30','32',0);
insert into foo values (200,to_date('06/9/2006','mm/dd/yyyy'),'30','45','41',4);
insert into foo values (200,to_date('07/7/2006','mm/dd/yyyy'),'30','30','30',4);
insert into foo values (200,to_date('07/31/2006','mm/dd/yyyy'),'30','30','32',2);



================


Quote:
select p_id,r_dt,dos,pdos,dbtwr,cry
from foo
model return all rows
main grr
partition by ( p_id)
dimension by (row_number() over
(partition by p_id order by r_dt ) as rn )
measures (r_dt,dos,pdos,dbtwr,co, 0 as cry )
ignore nav rules sequential order
(
cry[any]= case when (pdos[cv()]-dbtwr[cv()] +cry[cv()-1])
< 1 then 0
else pdos[cv()]-dbtwr[cv()]+cry[cv()-1]
end
)
QL> /

P_ID R_DT DOS PDOS DBTWR CRY
--------- --------- ---------- ---------- ---------- ----------
100 20-OCT-05 30 0 0
100 28-NOV-05 30 30 39 0
100 21-DEC-05 30 30 23 7
100 13-JAN-06 60 30 23 14
100 03-MAR-06 30 60 49 25
100 14-APR-06 30 30 42 13
100 09-JUN-06 30 30 56 0
100 07-JUL-06 30 30 28 2
100 31-JUL-06 30 30 24 8
200 20-OCT-05 30 0 0
200 28-NOV-05 30 30 30 0
200 21-DEC-05 30 30 35 0
200 13-JAN-06 60 30 25 5
200 03-MAR-06 30 60 70 0
200 14-APR-06 45 30 32 0
200 09-JUN-06 30 45 41 4
200 07-JUL-06 30 30 30 4
200 31-JUL-06 30 30 32 2

18 rows selected.




Srini

Re: How to create a Column that will "carry over" values [message #217785 is a reply to message #217691] Mon, 05 February 2007 08:55 Go to previous message
msisam
Messages: 3
Registered: February 2007
Junior Member
Thanks Srini. I should have specified this before but I am on Oracle 9i. I believe that this functionality is new with 10g. Is there a solution that will work in 9i.
I appreciate your help on this an I hope that I did not cause too much unnecessary effort.
Sam
Previous Topic: calculate threshold value
Next Topic: ORA :01652
Goto Forum:
  


Current Time: Thu Dec 08 00:31:21 CST 2016

Total time taken to generate the page: 0.09969 seconds