The Public holiday days saved in table as the here under format
Holiday name ------- start date ------- end date
NATIONAL_DAY_2022 --- 23/09/2022 ----- 23/09/2022
Eid_al-Fitr_2022 ---- 01/05/2022 ----- 05/05/2022
EID_ADHA_2022 ----- 07/07/2022 ------ 12/07/2022
When Employee submit fo vacation I got a paramter value of vacation start date "P_DATE_FROM " and vacation end date "P_DATE_TO"
So, I used the here under query to determine which public holiday fall in between the employee vacation
select holiday_name ,to_date(start_date,'dd/mm/yyyy') start_date ,to_date(end_date,'dd/mm/yyyy') end_date
into V_holiday_name,V_H_SD,V_H_ED
from XX_MOHR.XX_PUBLIC_HOLIDAYS@ECOM_XXMOHR
where
(
P_DATE_FROM between to_Date(start_date,'dd/mm/yyyy') and to_Date(end_date,'dd/mm/yyyy')
or
P_DATE_TO between to_Date(start_date,'dd/mm/yyyy') and to_Date(end_date,'dd/mm/yyyy')
or
P_DATE_FROM <= to_Date(start_date,'dd/mm/yyyy') and P_DATE_TO >= to_Date(end_date,'dd/mm/yyyy')
)
Now I need to get the count of public holiday days located in between the employee vacation start date and end date
Thank you for your support
Thanks