SQL Help

[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..

[KenEwald]

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

[slimdave]

The simplest solution would invlove creating another table to hold a list of years, sothat you could ...

Select
   school_cd,
   name,
   year
From
   my_table,
   list_of_years
Where
   year between start_year and end_year;

Alternatively, you could use ...

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..

[Kishore]

Thanks for you sql and will check with that