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
LVL 14
Muhammad Ahmad ImranDatabase DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Can you explain this a little more?  I'm not seeing how you get mar 28 2010 from today's date.
Muhammad Ahmad ImranDatabase DeveloperAuthor Commented:
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>
Muhammad Ahmad ImranDatabase DeveloperAuthor Commented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Naveen KumarProduction Manager / Application Support ManagerCommented:
<<<<<<<<<<<<<<<<<"--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
Muhammad Ahmad ImranDatabase DeveloperAuthor Commented:
@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.
Naveen KumarProduction Manager / Application Support ManagerCommented:
ok. try the below but i think still this is a simple requirement which can be done through a direct sql query. Anyway, the choice is yours to select/prefer the answer/method which suits you.

 select min(startdate) from term_dates
 where to_char(startdate,'YYYY') = (
 select to_char(startdate,'YYYY')  from term_dates
 where to_date('25-dec-2010','dd-mon-yyyy') between startdate and enddate );  this gets me 28-mar-2010

 select min(startdate) from term_dates
 where to_char(startdate,'YYYY') = (
 select to_char(startdate,'YYYY')  from term_dates
 where to_date('25-jan-2011','dd-mon-yyyy') between startdate and enddate );  this gets me 28-mar-2010

you can just give any input date instead of 25-dec-2010 or 25-jan-2011 and test it out. All we use is a simple subquery to get the result we wanted.

Thanks

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Muhammad Ahmad ImranDatabase DeveloperAuthor Commented:
thanks
Naveen KumarProduction Manager / Application Support ManagerCommented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.