How to get recordset within a record set

[amolik]

Hi All ,

I have following table with data

DIE_NO START_DAT END_DATE
------ --------- ---------
2458 01-DEC-03
2458 01-DEC-03
2458 02-DEC-03
2696 02-DEC-03
2696 03-DEC-03
2696 03-DEC-03
2696 03-DEC-03
2696 04-DEC-03
2458 05-DEC-03
2458 06-DEC-03
2458 06-DEC-03

I have die 2458 run on 1 st and 2 nd Dec and 5 th and 6 th dec.
I have null as end date right now. I want to update highest date in consecutive running of the die in end date.

This means I have Die 2458 run on 1 st and 2 nd then the End Date should be 2 nd for the first record and for 5 th and 6 th the end date should come 6 th DEC in the record where start date is 5 th.

I mean How I should achieve foll. output


DIE_NO START_DAT END_DATE
------ --------- ---------
2458 01-DEC-03 02-DEC-03
2458 01-DEC-03
2458 02-DEC-03
2696 02-DEC-03
2696 03-DEC-03
2696 03-DEC-03
2696 03-DEC-03
2696 04-DEC-03
2458 05-DEC-03 06-DEC-03
2458 06-DEC-03
2458 06-DEC-03

Is it possible in same table. Even if I have to take different table how will I fetch in that. Some search told me to use PRIOR but In this context how to use it.

Amol

[anandkl]

R u runing die 2458 on 2nd of Dec, if yes, then u can update the first run i.e 1st of Dec by
update table set end_date=start_date +1 where start_date='01-DEC-03' and similarly for the 5th Dec run.

Please let me know if this what ur looking at.

regards
anandkl

[amolik]

Anand Thanks for your reply, but if you see here it's not just 1 day difference issue. If I run die on 1 st 2 nd and 3 rd consecutive my end date should be 3 rd. If a die run on 5 th there is a blank date in between i.e. 4 th so the start date should be 1 st and end date should be 3 rd. Also for next record set my start date should be 5 th and end date should be 6 th dec. And this too is there in same table.

And that to0 appear in the first record i.e. on first.

I'll modify the expected output below so that my requirement should be more clear. ( Right now all end dates are null). I don't mind using PLSQL here or storing output in separate table but how to do it is the problem for me. I am not getting the basic query.

DIE_NO START_DAT END_DATE
------ --------- ---------
2458 01-DEC-03 02-DEC-03
2458 01-DEC-03
2458 02-DEC-03
2696 02-DEC-03 04-DEC-03
2696 03-DEC-03
2696 03-DEC-03
2696 03-DEC-03
2696 04-DEC-03
2458 05-DEC-03 06-DEC-03
2458 06-DEC-03
2458 06-DEC-03
2458 06-DEC-03

[anandkl]

Hi,
Try this,
update a.table_name set a.end_date =sysdate where a.rowid =
(select min(b.rowid) from table_name b where b.die = a.die);

I have choosen sysdate becuase it would be the current date u r runing the die.


SQL> select * from dbasupp;

DIE START_DATE END_DATE
---------- ----------- -----------
2485 01-DEC-2003
2485 01-DEC-2003
2485 02-DEC-2003


SQL> update dbasupp a set a.end_date=sysdate where a.rowid = (select min(b.rowid) from dbasupp b where b.die=a.die);

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dbasupp;

DIE START_DATE END_DATE
---------- ----------- -----------
2485 01-DEC-2003 12-JAN-2004
2485 01-DEC-2003
2485 02-DEC-2003

regards
anandkl

[DaPi]

Funny how varshanswamy has a similar problem in a different forum

[amolik]

She is my collegue ... but I think it does not matter as we are in same team but working on diffrent locations and I am not comfortable with her style ... so ... I guess I can get my answer to problem

Amol

[anandkl]

Did u check the above query..

regards
anandkl

[amolik]

Hi Anand I need to check it from start date only
Unfortunately my users are entering data after and that too in one shot ... so my programmers are getting only die_no and start date. They want us only to calculate end date based on the logic they gave us thats what is so much issue ... so I can't use sysdate here. I get data in die_no and start_date ( actually run date ) from them. I am only going to decide the start date and end date based on that.

I'll check your query on start date logic

Amol

[jrpm]

consider a pl/sql function that gets the max consecutive rundate.

Function foo in reckey in indate out outdate
as
curser for select dates from table where key = reckey
datebuf date
gotone char
begin
datebuf:= indate
gotone='N'
loop
if dates=datebuf
if no then
if dates=datebuf+1
datebuf=dates
gotone='Y'
end if
end if
end loop
return datebuf



The language isn't right, but as specs I hope it is understandable.

the statement would be
update table
set endate=foo(startdate)



Admittedly this might update all the records with the same key, but something must be left to solve.