Link to home
Start Free TrialLog in
Avatar of Muhammad Ahmad Imran
Muhammad Ahmad ImranFlag for United Kingdom of Great Britain and Northern Ireland

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Can you explain this a little more?  I'm not seeing how you get mar 28 2010 from today's date.
Avatar of Muhammad Ahmad Imran

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,termend 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>
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
<<<<<<<<<<<<<<<<<"--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
@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.
ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks