Query to find the records between the start date and end date

Question

I need to fetch the records that are falling between the start date and end date in oracle SQL. I 'll be having varStartDate and varEndDate. I want to fetch all records that are falling between the varStartDate and varEndDate. I am trying below query where first I am fetching recent start date i.e. in the past and end date i.e. in future. Secondly once I have the start date and end date I am to fetch all the records where startdate >= and enddate <=. Start Date and End Date are DATE datatype.

Leave Type	Start Date	End Date	LeaveNumber
Annual Leave	08-OCT-21	08-OCT-21	24042
Annual Leave	29-NOV-21	29-NOV-21	24043
Annual Leave	23-DEC-21	23-DEC-21	30069
Annual Leave	29-DEC-21	31-DEC-21	30112
Annual Leave	24-JAN-22	24-JAN-22	30189

Query:

with RosterStartDate as (
  select max(start_date) as START_DATE
  from Payrollrecords
  where START_DATE < TO_DATE('2021-11-20','YYYY-MM-DD')
),
RosterEndDate as (
  select min(end_date) as END_DATE
  from Payrollrecords where END_DATE >= TO_DATE('2022-11-30','YYYY-MM-DD')
)
                
select *
from Payrollrecords
where START_DATE = (select START_DATE from RosterStartDate)
  and END_DATE = (select END_DATE from RosterEndDate)

The query is returning no records if RosterEndDate.END_DATE is null ( it is null if no end date is found beyond the varEndDate). It would be great if you can help me the with the optimized query.

Does this answer your question? [Determine Whether Two Date Ranges Overlap](https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) or [What is a simple and efficient way to find rows with time-interval overlaps in SQL?](https://stackoverflow.com/questions/117962/what-is-a-simple-and-efficient-way-to-find-rows-with-time-interval-overlaps-in-s) — astentx, Mar 01 '23 at 07:03
On a side note: You don't have to convert the string literal `'2021-11-20'` to a date by applying `TO_DATE`. You can use a date literal right away: `DATE '2021-11-20'`. — Thorsten Kettner, Mar 01 '23 at 07:09

Thorsten Kettner · Answer 1 · 2023-03-01T07:30:29.407

RosterStartDate is 08-OCT-21, RosterEndDate is NULL. Even if RosterEndDate were not null, but say 23-DEC-21, you wouldn't find a row matching both dates with

where START_DATE = (select START_DATE from RosterStartDate)
  and END_DATE = (select END_DATE from RosterEndDate)

You want this instead:

where START_DATE >= (select START_DATE from RosterStartDate)
  and END_DATE <= (select END_DATE from RosterEndDate)

But as END_DATE can be null, you finally want:

where START_DATE >= (select START_DATE from RosterStartDate)
  and
  (
    END_DATE <= (select END_DATE from RosterEndDate)
    or
    (select END_DATE from RosterEndDate) is null
  )

which is easier to write as

select *
from Payrollrecords
where START_DATE >= (select START_DATE from RosterStartDate)
and END_DATE <= (select nvl(END_DATE, Payrollrecords.END_DATE) from RosterEndDate)

or with an unreachable date literal which may be faster and even clearer:

select *
from Payrollrecords
where START_DATE >= (select START_DATE from RosterStartDate)
and END_DATE <= (select nvl(END_DATE, date '9999-12-31') from RosterEndDate)

Demo: https://dbfiddle.uk/taPsiGJw

score 0 · Answer 2 · answered Mar 01 '23 at 07:05

0

You can just do this:

SELECT *
FROM Payrollrecords 
WHERE start_date > TO_DATE('2021-11-20','YYYY-MM-DD')
AND end_date <= TO_DATE('2022-11-30','YYYY-MM-DD');

answered Mar 01 '23 at 07:05

gotqn

42,737
46
157
243

score 0 · Answer 3 · answered Mar 01 '23 at 07:46

Not sure to understand properly the whole context of your question.

Payroll records represent date intervals. You want to find all payroll records that intersect with the interval (RosterStartDate - RosterEndDate), where RosterEndDate might be NULL. If it is NULL, we can replace it with today's value, using the NVL() function. So something like the following should do the job

SELECT * FROM Payrollrecords
WHERE START_DATE <= NVL((select END_DATE from RosterEndDate), CURRENT_DATE)
  AND END_DATE >= (select START_DATE from RosterStartDate)

MT0 · Answer 4 · 2023-03-03T08:50:30.513

Your question is very confusingly written; however, from your comment you appear to simply want:

SELECT *
FROM   Payrollrecords
WHERE  start_date <= DATE '2022-11-30'
AND    end_date   >= DATE '2021-11-20'

If your dates have a time component and you want the full day for 2022-11-30 then use:

SELECT *
FROM   Payrollrecords
WHERE  start_date <  DATE '2022-11-31'
AND    end_date   >= DATE '2021-11-20'

Original Answer

You can eliminate all the sub-queries if you use analytic functions.

If you want all the ranges that entirely are contained within the range then:

SELECT *
FROM   (
  SELECT p.*,
         MAX(CASE WHEN start_date <  DATE '2021-11-20' THEN start_date END) OVER ()
           AS max_start_date,
         COALESCE(
           MIN(CASE WHEN end_date   >= DATE '2022-11-30' THEN end_date   END) OVER (),
           DATE '9999-12-31' -- Or SYSDATE
         ) AS min_end_date
  FROM   Payrollrecords p
)
WHERE  start_date >= max_start_date
AND    end_date   <= min_end_date;

If you want ranges that overlap the range then:

SELECT *
FROM   (
  SELECT p.*,
         MAX(CASE WHEN start_date <  DATE '2021-11-20' THEN start_date END) OVER ()
           AS max_start_date,
         COALESCE(
           MIN(CASE WHEN end_date   >= DATE '2022-11-30' THEN end_date   END) OVER (),
           DATE '9999-12-31' -- Or SYSDATE
         ) AS min_end_date
  FROM   Payrollrecords p
)
WHERE  start_date <= min_end_date
AND    end_date   >= max_start_date;

fiddle

Both queries are not returning expected output. as the MAX_START_DATE always be having the date due to that it will be considering one extra records. same goes with MIN_END_DATE. The extra record will be out of the beyond the range that is expected in output. For example if start_date is 2021-12-30 and end date is 2022-01-25 then i need the below two records since 12-30-2021 falls under the start_date and end_date range from the table value. Annual Leave 29-DEC-21 31-DEC-21 30112 Annual Leave 24-JAN-22 24-JAN-22 30189 @astentx — user15057578, Mar 03 '23 at 08:26

score 0 · Answer 5 · answered Mar 03 '23 at 21:23

I suggest you include a row in the data that has a NULL end_date, such as below:

CREATE TABLE Payrollrecords  (
  leave_type VARCHAR2(50),
  start_date DATE,
  end_date DATE,
  leave_number NUMBER
);

BEGIN
  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-10-08', DATE '2021-10-08', 24042);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-11-21', DATE '2021-11-29', 24043);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-12-23', DATE '2021-12-23', 30069);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-12-23', NULL, 99999);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-12-29', DATE '2021-12-31', 30112);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2022-01-24', DATE '2022-01-24', 30189);
END;
/

1 rows affected

select * 
from Payrollrecords

LEAVE_TYPE	START_DATE	END_DATE	LEAVE_NUMBER
Annual Leave	08-OCT-21	08-OCT-21	24042
Annual Leave	21-NOV-21	29-NOV-21	24043
Annual Leave	23-DEC-21	23-DEC-21	30069
Annual Leave	23-DEC-21	null	99999
Annual Leave	29-DEC-21	31-DEC-21	30112
Annual Leave	24-JAN-22	24-JAN-22	30189

select * 
from Payrollrecords
where start_date <= TO_DATE('2022-11-30','YYYY-MM-DD') --range_end
and (end_date >= TO_DATE('2021-11-20','YYYY-MM-DD') --range_start
    or end_date IS NULL
    )

LEAVE_TYPE	START_DATE	END_DATE	LEAVE_NUMBER
Annual Leave	21-NOV-21	29-NOV-21	24043
Annual Leave	23-DEC-21	23-DEC-21	30069
Annual Leave	23-DEC-21	null	99999
Annual Leave	29-DEC-21	31-DEC-21	30112
Annual Leave	24-JAN-22	24-JAN-22	30189

see this fiddle

Notes:

comparing start_date to the range_end, and end_date to range_start may seem counter-intuitive but this method works and simplifies the predicates needed
as you include date literals in the CTEs, just use those same literals in the where clause and avoid the CTEs - bind variables will work here as well
to account for NULLs add "or end_date IS NULL", with appropriate parentheses, don't use NVL or COALESCE in the predicate as these functions can be detrimental to query performance.

Hi Paul, above is failing a scenario where :startdate and :enddate are within the range of the record 23-DEV-21 and null. If you run the fiddle with below query, I am expecting only that record. can you please have a look. much appreciated! select * from Payrollrecords where start_date <= TO_DATE('2022-12-24','YYYY-MM-DD') --range_end and (end_date >= TO_DATE('2021-12-24','YYYY-MM-DD') --range_start or end_date IS NULL ) — user15057578, Aug 08 '23 at 12:06
https://dbfiddle.uk/2vZrtyv1 the query results in 3 rows. If you look at the query and examine the data, all 3 rows comply to that query's filtering conditions. — Paul Maxwell, Aug 09 '23 at 04:23
try this version: https://dbfiddle.uk/aI7kYpML the query works as expected — Paul Maxwell, Aug 09 '23 at 05:21

Query to find the records between the start date and end date

5 Answers5

Original Answer

Linked