Hello All,
We have target table with Date_1 and Date_2 and Calendar table with time details along with Weekend_flg and Holiday_flg.
We are trying to achieve Working Hrs and Mins without weekends and public holidays by making use of both tables business Hrs we have to consider 8 AM to 5 PM. If any work done before or after these Working Hrs we should not consider those Hrs Mins.
We used below Query to find the working Hrs, issue we are facing is whenever the Total_diff is 0 or 1 day it’s not working properly if Total_diff is more than 1 days its working correctly below is the query we used.
Can someone provide the inputs for these is much appreciated.
WITH DAYS AS (select distinct id,Date_1,Date_2,Total_Diff,Weekend_total,Holiday_total,
(Total_Diff - (WEEKEND_total+HOLIDAY_total)) AS work_DAYS
from
(select distinct id,Date_1,Date_2,Total_Diff,Weekend_total,sum(holiday_flag) over (partition BY ID) AS Holiday_total
from
(SELECT distinct A.task_pyid,A.Date_1,a.Date_2,
(round(to_date(Date_2, 'YYYY-MM-DD hh24:mi:ss')-TO_DATE(Date_1,'YYYY-MM-DD hh24:mi:ss'))
as Total_DIFF,
least(7-to_char(Date_1,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')+1,2)
+greatest(to_char(Date_2,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')-5,0)
+((trunc(Date_2,'IW')-trunc(Date_1,'IW'))/7-1)*2 as Weekend_total,
case when b.holiday_flg='Y' then 1
when b.holiday_flg='N' then 0
else 0 end as holiday_flg
from target A , Calendar B
where B.time_key between A.Date_1 and A.Date_2
SELECT distinct A.id,A.Date_1,a.Date_2,Total_DIFF,Weekend_total,Holiday_total,work_DAYS,
Working_DAYS*9+( CASE WHEN (TO_NUMBER(TO_CHAR(A.Date_2,'HH24'))-8) >=9 then 9
ELSE (TO_NUMBER(TO_CHAR(A.Date_2,'HH24'))-8) END) as Work_HRS,
case when (TO_NUMBER(TO_CHAR(A.Date_2,'HH24'))) >=17 then 0
else (TO_NUMBER(TO_CHAR(A.Date_2,'mi'))) end as work_MIN
from target A ,calender B,DAYS D
where b.time_key between A.Date_1 and a.Date_2
#########