Muhammad Ahmad Imran
asked on
Date Range
--Here are the scripts for data
--
create table TERM_DATES
(
IDX NUMBER,
STARTDATE DATE,
TERMNAME VARCHAR2(13),
ENDDATE DATE
)
;
prompt Loading TERM_DATES...
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (5, to_date('31-08-2008', 'dd-mm-yyyy'), '2ND TERM 2008', to_date('30-11-2008', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (1, to_date('01-04-2007', 'dd-mm-yyyy'), '1ST TERM 2007', to_date('02-09-2007', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (2, to_date('02-09-2007', 'dd-mm-yyyy'), '2ND TERM 2007', to_date('16-12-2007', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (6, to_date('30-11-2008', 'dd-mm-yyyy'), '3RD TERM 2008', to_date('29-03-2009', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (7, to_date('29-03-2009', 'dd-mm-yyyy'), '1ST TERM 2009', to_date('06-09-2009', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (3, to_date('16-12-2007', 'dd-mm-yyyy'), '3RD TERM 2007', to_date('30-03-2008', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (4, to_date('30-03-2008', 'dd-mm-yyyy'), '1ST TERM 2008', to_date('31-08-2008', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (8, to_date('06-09-2009', 'dd-mm-yyyy'), '2ND TERM 2009', to_date('06-12-2009', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (9, to_date('06-12-2009', 'dd-mm-yyyy'), '3RD TERM 2009', to_date('28-03-2010', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (10, to_date('28-03-2010', 'dd-mm-yyyy'), '1ST TERM 2010', to_date('05-09-2010', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (11, to_date('05-09-2010', 'dd-mm-yyyy'), '2ND TERM 2010', to_date('05-12-2010', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (12, to_date('05-12-2010', 'dd-mm-yyyy'), '3RD TERM 2010', to_date('03-04-2011', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (13, to_date('03-04-2011', 'dd-mm-yyyy'), '1ST TERM 2011', to_date('04-09-2011', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (14, to_date('04-09-2011', 'dd-mm-yyyy'), '2ND TERM 2011', to_date('04-12-2011', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (15, to_date('04-12-2011', 'dd-mm-yyyy'), '3RD TERM 2011', to_date('01-04-2012', 'dd-mm-yyyy'));
commit;
--
select * from term_dates order by idx;
IDX STARTDATE TERMNAME ENDDATE
---------- --------- ------------- ---------
1 01-APR-07 1ST TERM 2007 02-SEP-07
2 02-SEP-07 2ND TERM 2007 16-DEC-07
3 16-DEC-07 3RD TERM 2007 30-MAR-08
4 30-MAR-08 1ST TERM 2008 31-AUG-08
5 31-AUG-08 2ND TERM 2008 30-NOV-08
6 30-NOV-08 3RD TERM 2008 29-MAR-09
7 29-MAR-09 1ST TERM 2009 06-SEP-09
8 06-SEP-09 2ND TERM 2009 06-DEC-09
9 06-DEC-09 3RD TERM 2009 28-MAR-10
10 28-MAR-10 1ST TERM 2010 05-SEP-10
11 05-SEP-10 2ND TERM 2010 05-DEC-10
12 05-DEC-10 3RD TERM 2010 03-APR-11
13 03-APR-11 1ST TERM 2011 04-SEP-11
14 04-SEP-11 2ND TERM 2011 04-DEC-11
15 04-DEC-11 3RD TERM 2011 01-APR-12
NOW
what i am try to get..
Based on current system date, i want to retrieve start date of first term date.
for example for today I want 28-MAR-10 and I want the same date until 03-APR-11.
and so on
--
create table TERM_DATES
(
IDX NUMBER,
STARTDATE DATE,
TERMNAME VARCHAR2(13),
ENDDATE DATE
)
;
prompt Loading TERM_DATES...
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (5, to_date('31-08-2008', 'dd-mm-yyyy'), '2ND TERM 2008', to_date('30-11-2008', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (1, to_date('01-04-2007', 'dd-mm-yyyy'), '1ST TERM 2007', to_date('02-09-2007', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (2, to_date('02-09-2007', 'dd-mm-yyyy'), '2ND TERM 2007', to_date('16-12-2007', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (6, to_date('30-11-2008', 'dd-mm-yyyy'), '3RD TERM 2008', to_date('29-03-2009', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (7, to_date('29-03-2009', 'dd-mm-yyyy'), '1ST TERM 2009', to_date('06-09-2009', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (3, to_date('16-12-2007', 'dd-mm-yyyy'), '3RD TERM 2007', to_date('30-03-2008', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (4, to_date('30-03-2008', 'dd-mm-yyyy'), '1ST TERM 2008', to_date('31-08-2008', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (8, to_date('06-09-2009', 'dd-mm-yyyy'), '2ND TERM 2009', to_date('06-12-2009', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (9, to_date('06-12-2009', 'dd-mm-yyyy'), '3RD TERM 2009', to_date('28-03-2010', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (10, to_date('28-03-2010', 'dd-mm-yyyy'), '1ST TERM 2010', to_date('05-09-2010', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (11, to_date('05-09-2010', 'dd-mm-yyyy'), '2ND TERM 2010', to_date('05-12-2010', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (12, to_date('05-12-2010', 'dd-mm-yyyy'), '3RD TERM 2010', to_date('03-04-2011', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (13, to_date('03-04-2011', 'dd-mm-yyyy'), '1ST TERM 2011', to_date('04-09-2011', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (14, to_date('04-09-2011', 'dd-mm-yyyy'), '2ND TERM 2011', to_date('04-12-2011', 'dd-mm-yyyy'));
insert into TERM_DATES (IDX, STARTDATE, TERMNAME, ENDDATE)
values (15, to_date('04-12-2011', 'dd-mm-yyyy'), '3RD TERM 2011', to_date('01-04-2012', 'dd-mm-yyyy'));
commit;
--
select * from term_dates order by idx;
IDX STARTDATE TERMNAME ENDDATE
---------- --------- ------------- ---------
1 01-APR-07 1ST TERM 2007 02-SEP-07
2 02-SEP-07 2ND TERM 2007 16-DEC-07
3 16-DEC-07 3RD TERM 2007 30-MAR-08
4 30-MAR-08 1ST TERM 2008 31-AUG-08
5 31-AUG-08 2ND TERM 2008 30-NOV-08
6 30-NOV-08 3RD TERM 2008 29-MAR-09
7 29-MAR-09 1ST TERM 2009 06-SEP-09
8 06-SEP-09 2ND TERM 2009 06-DEC-09
9 06-DEC-09 3RD TERM 2009 28-MAR-10
10 28-MAR-10 1ST TERM 2010 05-SEP-10
11 05-SEP-10 2ND TERM 2010 05-DEC-10
12 05-DEC-10 3RD TERM 2010 03-APR-11
13 03-APR-11 1ST TERM 2011 04-SEP-11
14 04-SEP-11 2ND TERM 2011 04-DEC-11
15 04-DEC-11 3RD TERM 2011 01-APR-12
NOW
what i am try to get..
Based on current system date, i want to retrieve start date of first term date.
for example for today I want 28-MAR-10 and I want the same date until 03-APR-11.
and so on
Can you explain this a little more? I'm not seeing how you get mar 28 2010 from today's date.
ASKER
this is a school calendar year starts from nearest Monday(i insert dates for Sundays for some reasons) to 1st April every year.
each year consists of three terms as
1ST TERM 2007 01-APR-07 02-SEP-07
2ND TERM 2007 02-SEP-07 16-DEC-07
3RD TERM 2007 16-DEC-07 30-MAR-08
what i want based on current date, the startdate of this user defined calendar.
for calculating pupils attendance i need two dates, term start date, year start date.
SQL> select startdate term_start from term_dates WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE;
TERM_STAR
---------
05-DEC-10
--and now this is what i want.
SQL> select startdate year_start from term_dates WHERE SYSDATE BETWEEN (...) and (...)
2 /
YEAR_STAR
---------
28-MAR-10
You may give me a solution in above scenario but i have done this in some other way.
I changed my table as
SQL> SELECT * FROM TERMS order by idx
2 /
IDX YEAR FST_TERM SCND_TERM TRD_TERM TERMEND
---------- ---- --------- --------- --------- ---------
1 2007 01-APR-07 02-SEP-07 16-DEC-07 30-MAR-08
2 2008 30-MAR-08 31-AUG-08 30-NOV-08 29-MAR-09
3 2009 29-MAR-09 06-SEP-09 06-DEC-09 28-MAR-10
4 2010 28-MAR-10 05-SEP-10 05-DEC-10 03-APR-11
5 2011 03-APR-11 04-SEP-11 04-DEC-11 01-APR-12
SQL> drop table term_dates;
Table dropped.
SQL> create view term_dates as
2 SELECT termname,startdate,enddate FROM
3 (select '1ST TERM '||YEAR TERMNAME,year,fst_term STARTDATE,scnd_term ENDDATE from terms
4 union all
5 select '2ND TERM '||YEAR ,year,scnd_term,trd_term from terms
6 union all
7 select '3RD TERM '||YEAR,year,trd_term,term end from terms)
8 ORDER BY YEAR
9 /
View created.
SQL> select startdate term_start from term_dates WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE;
TERM_STAR
---------
05-DEC-10
SQL> select fst_term year_start_date from terms where sysdate between fst_term and termend
2 /
YEAR_STAR
---------
28-MAR-10
SQL>
each year consists of three terms as
1ST TERM 2007 01-APR-07 02-SEP-07
2ND TERM 2007 02-SEP-07 16-DEC-07
3RD TERM 2007 16-DEC-07 30-MAR-08
what i want based on current date, the startdate of this user defined calendar.
for calculating pupils attendance i need two dates, term start date, year start date.
SQL> select startdate term_start from term_dates WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE;
TERM_STAR
---------
05-DEC-10
--and now this is what i want.
SQL> select startdate year_start from term_dates WHERE SYSDATE BETWEEN (...) and (...)
2 /
YEAR_STAR
---------
28-MAR-10
You may give me a solution in above scenario but i have done this in some other way.
I changed my table as
SQL> SELECT * FROM TERMS order by idx
2 /
IDX YEAR FST_TERM SCND_TERM TRD_TERM TERMEND
---------- ---- --------- --------- --------- ---------
1 2007 01-APR-07 02-SEP-07 16-DEC-07 30-MAR-08
2 2008 30-MAR-08 31-AUG-08 30-NOV-08 29-MAR-09
3 2009 29-MAR-09 06-SEP-09 06-DEC-09 28-MAR-10
4 2010 28-MAR-10 05-SEP-10 05-DEC-10 03-APR-11
5 2011 03-APR-11 04-SEP-11 04-DEC-11 01-APR-12
SQL> drop table term_dates;
Table dropped.
SQL> create view term_dates as
2 SELECT termname,startdate,enddate
3 (select '1ST TERM '||YEAR TERMNAME,year,fst_term STARTDATE,scnd_term ENDDATE from terms
4 union all
5 select '2ND TERM '||YEAR ,year,scnd_term,trd_term from terms
6 union all
7 select '3RD TERM '||YEAR,year,trd_term,term
8 ORDER BY YEAR
9 /
View created.
SQL> select startdate term_start from term_dates WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE;
TERM_STAR
---------
05-DEC-10
SQL> select fst_term year_start_date from terms where sysdate between fst_term and termend
2 /
YEAR_STAR
---------
28-MAR-10
SQL>
ASKER
and now
also suggest which is the best approach for designing this table,
1st as posted in question.
or
2nd as posted in my last comment.
thanks and Merry Christmas to everyone here on board at EE
also suggest which is the best approach for designing this table,
1st as posted in question.
or
2nd as posted in my last comment.
thanks and Merry Christmas to everyone here on board at EE
<<<<<<<<<<<<<<<<<"--and now this is what i want.
SQL> select startdate year_start from term_dates WHERE SYSDATE BETWEEN (...) and (...)
2 /
YEAR_STAR
---------
28-MAR-10
" >>>>>>>>>>>>>>>>>>>
You can just simple run the below right ?
select min(startdate) year_start
from term_dates WHERE to_char(sysdate,'YYYY') = to_char(startdate,'YYYY');
-- this query works by selecting all records which belong to the year of the given date and then picks up the minimum of the startdate which is the start of the year. In this case, i have used the given/parameter date as sysdate but we can give any date we need.
Not sure why you want to setup a view and complicate it unless i did not understand your requirement clearly.
Thanks
SQL> select startdate year_start from term_dates WHERE SYSDATE BETWEEN (...) and (...)
2 /
YEAR_STAR
---------
28-MAR-10
" >>>>>>>>>>>>>>>>>>>
You can just simple run the below right ?
select min(startdate) year_start
from term_dates WHERE to_char(sysdate,'YYYY') = to_char(startdate,'YYYY');
-- this query works by selecting all records which belong to the year of the given date and then picks up the minimum of the startdate which is the start of the year. In this case, i have used the given/parameter date as sysdate but we can give any date we need.
Not sure why you want to setup a view and complicate it unless i did not understand your requirement clearly.
Thanks
ASKER
@nav_kum_v
SQL> select SYSDATE,min(startdate) year_start
2 from term_dates WHERE to_char(sysdate,'YYYY') = to_char(startdate,'YYYY')
3 /
SYSDATE YEAR_STAR
--------- ---------
25-DEC-10 28-MAR-10
THAT'S FINE BUT
1 select SYSDATE,min(startdate) year_start
2* from term_dates WHERE to_char(sysdate,'YYYY') = to_char(startdate,'YYYY')
SQL> /
SYSDATE YEAR_STAR
--------- ---------
25-JAN-11 03-APR-11
SO THAT'S WRONG AS IT SHOULD RETURN AGAIN '28-MAR-10'
i WILL remain open this question for quite some time (as i said earlier, i have got solution myself) to get some opinion on performance issues using my second technique.
SQL> select SYSDATE,min(startdate) year_start
2 from term_dates WHERE to_char(sysdate,'YYYY') = to_char(startdate,'YYYY')
3 /
SYSDATE YEAR_STAR
--------- ---------
25-DEC-10 28-MAR-10
THAT'S FINE BUT
1 select SYSDATE,min(startdate) year_start
2* from term_dates WHERE to_char(sysdate,'YYYY') = to_char(startdate,'YYYY')
SQL> /
SYSDATE YEAR_STAR
--------- ---------
25-JAN-11 03-APR-11
SO THAT'S WRONG AS IT SHOULD RETURN AGAIN '28-MAR-10'
i WILL remain open this question for quite some time (as i said earlier, i have got solution myself) to get some opinion on performance issues using my second technique.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
thanks