-
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
-
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.
-
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..
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|