Home » SQL & PL/SQL » SQL & PL/SQL » 12.1.0.1 (Filter dates between a list of start and end dates )
12.1.0.1 [message #684025] Thu, 25 March 2021 06:06 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi All,

I have two tables, one contains a list of start and end dates (tst_date_list) and the second contains a list of dates (tst_dates).
I need to show dates in the second table (tst_dates) that fall outside the start and end dates in the first table (tst_date_list).

create table tst_date_list
  (
    id number(1) primary key,
    sdate date,
    edate date
  );

create table tst_dates
  (
    ID number(2) primary key,
    A_date date
  );

insert all 
  into TST_DATE_LIST(id, sdate, edate) values(1, to_date('01-01-2000','dd-mm-yyyy'), to_date('01-03-2000','dd-mm-yyyy'))
  into TST_DATE_LIST(id, sdate, edate) values(2, to_date('01-07-2000','dd-mm-yyyy'), to_date('01-12-2000','dd-mm-yyyy'))
  into TST_DATE_LIST(id, sdate, edate) values(3, to_date('01-07-2001','dd-mm-yyyy'), to_date('01-01-2002','dd-mm-yyyy'))
  into tst_dates(id, a_date) values (1, to_date('31-12-1999','dd-mm-yyyy'))
  into tst_dates(id, a_date) values (2, to_date('01-01-2000','dd-mm-yyyy'))
  into tst_dates(id, a_date) values (3, to_date('20-02-2000','dd-mm-yyyy'))
  into tst_dates(id, a_date) values (4, to_date('06-06-2000','dd-mm-yyyy'))
  into tst_dates(id, a_date) values (5, to_date('01-12-2000','dd-mm-yyyy'))
  into tst_dates(id, a_date) values (6, to_date('01-05-2001','dd-mm-yyyy'))
  into tst_dates(id, a_date) values (7, to_date('01-12-2001','dd-mm-yyyy'))
  into tst_dates(id, a_date) values (8, to_date('02-01-2002','dd-mm-yyyy'))
select * from dual;

So the result I am looking for is:
ID      A_DATE
1       31-Dec-99 12:00:00 AM
3       20-Feb-00 12:00:00 AM
4       06-Jun-00 12:00:00 AM
6       01-May-01 12:00:00 AM
8       02-Jan-02 12:00:00 AM
where the dates in the results are all the dates that do not fall between any of the start/end date periods in the dates list table.

Thanks,
Ferro
Re: 12.1.0.1 [message #684026 is a reply to message #684025] Thu, 25 March 2021 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from tst_dates d
  2  where not exists (select null from TST_DATE_LIST l where d.a_date between l.sdate and l.edate)
  3  order by id
  4  /
        ID A_DATE
---------- -------------------
         1 31/12/1999 00:00:00
         4 06/06/2000 00:00:00
         6 01/05/2001 00:00:00
         8 02/01/2002 00:00:00

4 rows selected.
Your result is not correct:
3 (20-Feb-00 12:00:00 AM) is between "to_date('01-01-2000','dd-mm-yyyy'), to_date('01-03-2000','dd-mm-yyyy')"

Re: 12.1.0.1 [message #684039 is a reply to message #684026] Sat, 27 March 2021 23:25 Go to previous message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear Michel,

Thanks a lot for your reply.

You're right, my result was not correct, the row you pointed should not appear.

Thanks again,
Ferro
Previous Topic: how to use select field value in where clause (merged)
Next Topic: sort with duplicate records
Goto Forum:
  


Current Time: Tue Apr 16 02:02:43 CDT 2024