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  |
 |
OraFerro
Messages: 415 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 #684039 is a reply to message #684026] |
Sat, 27 March 2021 23:25  |
 |
OraFerro
Messages: 415 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
|
|
|
Goto Forum:
Current Time: Sun Mar 26 14:18:14 CDT 2023
|