How to get recordset within a record set
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to get recordset within a record set

  1. #1
    Join Date
    Dec 2000
    Posts
    255

    How to get recordset within a record set

    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

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  3. #3
    Join Date
    Dec 2000
    Posts
    255
    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

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    Last edited by anandkl; 01-12-2004 at 04:08 AM.
    anandkl

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Thumbs down

    Funny how varshanswamy has a similar problem in a different forum

  6. #6
    Join Date
    Dec 2000
    Posts
    255
    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

  7. #7
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Did u check the above query..

    regards
    anandkl
    anandkl

  8. #8
    Join Date
    Dec 2000
    Posts
    255
    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

  9. #9
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    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.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width