Results 1 to 6 of 6

Thread: Pl/sql

  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Post Unanswered: Pl/sql

    I have the following records in one of my database tables,

    Row # Start Date End Date Rating
    1 01-JAN-2000 31-DEC-2000 3
    2 01-JAN-2001 31-DEC-2001 3
    3 01-JAN-2002 31-DEC-2002 27
    4 01-JAN-2003 31-DEC-2003 27
    5 01-JAN-2004 31-DEC-2004 3
    6 01-JAN-2005 31-DEC-2005 27
    7 01-JAN-2006 31-DEC-2006 3

    My requirement is to show output as,

    Row # Start Date End Date Rating
    1 01-JAN-2000 31-DEC-2001 3
    2 01-JAN-2002 31-DEC-2003 27
    3 01-JAN-2004 31-DEC-2004 3
    4 01-JAN-2005 31-DEC-2005 27
    5 01-JAN-2006 31-DEC-2006 3

    Can this be achieved in a single PL/SQL statement?

    - Rohit.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    what the heck is the requirement/rule?
    Sometimes you skip a year for the end date and
    other times you only go one full year.

    in your example Jan 2000 start, then Dec 2001 end,
    BUT you also have
    Jan 2006 start and Dec 2006 end ...

    not consistent

    PHP Code:

    12
    :21:32 kod:platformselect t1t2, (select rating from test where date2 t2) as rating
    2  from 
    (
    3  select t1, (select min(date2)  
    from test where date2 t1+366) as t2 from (
    4  select date1 as t1 from test));

    T1        T2        RA
    --------- --------- --
    01-JAN-00 31-DEC-01 3
    01
    -JAN-01 31-DEC-02 27
    01
    -JAN-02 31-DEC-03 27
    01
    -JAN-03 31-DEC-04 3
    01
    -JAN-04 31-DEC-05 27
    01
    -JAN-05 31-DEC-06 3
    01
    -JAN-06 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Oct 2003
    Posts
    12

    Post PL/SQL

    I need to group together the records based on the rating field change. It's not inconsistent as the Rating is valid only between the Start Date and End Date which not necessarily be a period of an year.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Oh!

    Why didn't you say so?
    So, if the rating changes, then it is a new interval/end_date?

    They could have helped you by at least changing the rating each time instead of going back to 3 again. Not very nice.

    You can still use my original query to help you out.
    I can probably give you a correct query tomorrow.
    time to go home!

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Oct 2003
    Posts
    12

    Post PL/SQL

    Your first query can not be used as the rating could change at any time of the year, not necessarily at the year end. For example, the data could be,

    Row # Start Date End Date Rating
    1 01-JAN-2000 31-DEC-2000 3
    2 01-JAN-2001 30-JUN-2001 3
    3 01-JUL-2001 31-DEC-2001 27
    4 01-JAN-2002 31-DEC-2002 27
    5 01-JAN-2003 31-DEC-2003 27
    6 01-JAN-2004 31-MAR-2004 3
    7 01-APR-2004 31-DEC-2004 27
    8 01-JAN-2005 31-DEC-2005 3
    9 01-JAN-2006 31-DEC-2006 3

    Then the required output should be,

    Row # Start Date End Date Rating
    1 01-JAN-2000 30-JUN-2001 3
    2 01-JUL-2001 31-DEC-2003 27
    3 01-JAN-2004 31-MAR-2004 3
    4 01-APR-2004 31-DEC-2004 27
    5 01-JAN-2005 31-DEC-2006 3

    Appreciate any help!

    Thanks,
    Rohit.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    well, I KNOW it doesn't work as you want it, but I only was saying that it could lead you down the path to solving this yourself.

    at this point, all you need to do is order by start-date and then select the group with that rating and then select the min(start_date) and max(end_date) for that interval.

    so, start out just trying to get the interval blocks. Once you have that, you are golden.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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