SQL Query to check if the start date and end date columns are within the parameter date-CodePudding

I have a query how to handle dates within a parameter date RANGE

select absence_name,
abs_start_date,
abs_end_date,
PERSON_ID
from abs_table JOIN PER_ALL_PEOPLE_F PAPF
on (papf.person_id = abs_table.person_id)

Output looks like -

ABSENCE_NAME        abs_start_date      abs_end_date    PERSON_ID
Vacation            01-JAN-2021         03-FEB-2021     10
VACATION            24-NOV-2021         30-NOV-2021     01
Maternity           10-OCT-2021         25-OCT-2022     03
Paid Leave          27-NOV-2021         28-NOV-2021     04
VACATION            01-JUN-2021         19-NOV-2021     05
Maternity           01-NOV-2021         19-NOV-2022     06
Maternity           27-NOV-2021         19-DEC-2021     07

Now there are three conditions I want to tackle in the output wrt. dates. Parameter date is p_From_Date - 10-Nov-2021 p_to_date 28-Nov-2021

person_id #10 row shouldnt come as the start date and end date is not within this range.
person_id #01 row should come in the output. Start date is after the p_from_Date although end date is after the p_tp_Date
person_id #03 row should come. Although start date is before the p_from_date but leaves falls within this date range.
person_id #04 row should come as start date is between the date range.
person_id #05 should also come.

How can i handle in the above query such that if the person start date and end date is within the date range irrespective of it being just the start date or end date the row should be returned.

Also, second part to the same question, If the absence is Maternity, and if the person is on Maternity leave for the entire range of start date and end date then a flag Y should be passed in the output. Else Null

ABSENCE_NAME    abs_start_date      abs_end_date    PERSON_ID     Rule_flag
Vacation        01-JAN-2021         03-FEB-2021     10
VACATION        24-NOV-2021         30-NOV-2021     01
Maternity       10-OCT-2021         25-OCT-2022     03              Y
Paid Leave      27-NOV-2021         28-NOV-2021     04
VACATION        01-JUN-2021         19-NOV-2021     05
Maternity       01-NOV-2021         19-NOV-2022     06              y
Maternity       27-NOV-2021         19-DEC-2021     07

So for example, employee #3,06 was on maternity leave during entire duration of the parameter passed then the flag is Y

Employee #07 were on materniy leave but after a days from the p_from date and hence it will not be Y. How can we add a check a for this in the same above query

CodePudding user response：

A couple of BETWEENs should handle the first part nicely, while a CASE expression can take care of the rule:

WITH rangeData
  AS (SELECT DATE '2021-11-10' AS P_FROM_DATE,
             DATE '2021-11-28' AS P_TO_DATE
        FROM DUAL)
select absence_name,
       abs_start_date,
       abs_end_date,
       PERSON_ID,
       CASE
         WHEN UPPER(ABSENCE_NAME) = 'MATERNITY' AND
              ABS_START_DATE <= P_FROM_DATE AND
              ABS_END_DATE   >= P_FROM_DATE
           THEN 'Y'
           ELSE NULL
       END AS RULE_FLAG
  from abs_table
  INNER JOIN PER_ALL_PEOPLE_F PAPF
    on papf.person_id = abs_table.person_id
  CROSS JOIN rangeData
  WHERE ABS_START_DATE BETWEEN P_FROM_DATE AND P_TO_DATE OR
        ABS_END_DATE   BETWEEN P_FROM_DATE AND P_TO_DATE

Not tested on animals - you'll be first!