SQL Help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL Help

  1. #1
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317

    SQL Help

    school_cd, name, start_year end_year
    01 abc 2000 2002
    02 mnx 2000 2003
    03 xyz 2000 2001




    i need a sql stmt to show: (a row for each range)

    01 abc 2000 2001
    01 abc 2001 2002

    02 mnx 2000 2001
    02 mnx 2001 2002
    02 mnx 2002 2003

    03 xyz 2000 2001



    Can any one help me in getting this..
    Thanks
    Kishore Kumar

  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    This is a really crude first iteration... fixed range

    select school_cd,name,start_year,start_year+1
    from school
    where start_year <= to_number(to_char(sysdate,'YYYY'))-3
    and end_year > to_number(to_char(sysdate,'YYYY'))-3
    union all
    select school_cd,name,start_year+1,start_year+2
    from school
    where start_year <= to_number(to_char(sysdate,'YYYY'))-2
    and end_year > to_number(to_char(sysdate,'YYYY'))-2
    union all
    select school_cd,name,start_year+2,start_year+3
    from school
    where start_year <= to_number(to_char(sysdate,'YYYY'))-1
    and end_year > to_number(to_char(sysdate,'YYYY'))-1
    order by 1,3,4
    Last edited by KenEwald; 08-25-2003 at 05:17 PM.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: SQL Help

    The simplest solution would invlove creating another table to hold a list of years, sothat you could ...
    Code:
    Select
       school_cd,
       name,
       year
    From
       my_table,
       list_of_years
    Where
       year between start_year and end_year;
    Alternatively, you could use ...

    Code:
    Select
       school_cd,
       name,
       year
    From
       my_table,
       (select
           1950+rownum year
        from
           user_objects)list_of_years
    Where
       year between start_year and end_year;

    Originally posted by Kishore
    school_cd, name, start_year end_year
    01 abc 2000 2002
    02 mnx 2000 2003
    03 xyz 2000 2001




    i need a sql stmt to show: (a row for each range)

    01 abc 2000 2001
    01 abc 2001 2002

    02 mnx 2000 2001
    02 mnx 2001 2002
    02 mnx 2002 2003

    03 xyz 2000 2001



    Can any one help me in getting this..
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Thanks for you sql and will check with that
    Thanks
    Kishore Kumar

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