CODEWITHSUNDEEP
sqloracle11g
DataPsycho
DataPsycho

Reputation: 988

how to calculate business Day in oracle excluding any specific day of the week?

In my data I have a start day and end day. I am trying to calculate the difference between the two days. But I have to exclude the Wednesday and Friday always. I am still trying to find the solution by myself. If someone know how to do please help. Here is some pre-build code can be used to experement:

SELECT
START_DATE
,END_DATE
,TRUNC(START_DATE, 'D') AS TRUNC_START
,TRUNC(END_DATE, 'D') AS TRUNC_END
,to_char(START_DATE, 'DY') AS START_DAY_NAME
,to_char(END_DATE, 'DY') AS END_DATE_NAME
,TRUNC(END_DATE) - TRUNC(START_DATE) + 1 AS TOTAL_RESOLVE_TIME
,TRUNC((TRUNC(END_DATE) - TRUNC(START_DATE) + 1)/7*2) AS NO_OF_WEEK
FROM
(
 SELECT 
 TO_DATE('2018-03-08', 'YYYY-MM-DD') AS START_DATE 
 ,TO_DATE('2018-03-14', 'YYYY-MM-DD') AS END_DATE 
 FROM dual
 UNION ALL
 SELECT 
 TO_DATE('2018-03-09', 'YYYY-MM-DD') AS START_DATE 
 ,TO_DATE('2018-03-15', 'YYYY-MM-DD') AS END_DATE 
 FROM dual
 UNION ALL
 SELECT 
 TO_DATE('2018-03-02', 'YYYY-MM-DD') AS START_DATE 
 ,TO_DATE('2018-03-11', 'YYYY-MM-DD') AS END_DATE 
 FROM dual
 UNION ALL
SELECT 
 TO_DATE('2018-03-01', 'YYYY-MM-DD') AS START_DATE 
 ,TO_DATE('2018-03-17', 'YYYY-MM-DD') AS END_DATE 
 FROM dual) a;

Upvotes: 0

Views: 55

Answers (1)

DataPsycho
DataPsycho

Reputation: 988

It works now after reading the above link mentioned by @wolfrevokcats I figure it out.

SELECT
START_DATE
,END_DATE
,TRUNC(START_DATE, 'D') AS TRUNC_START
,TRUNC(END_DATE, 'D') AS TRUNC_END
,to_char(START_DATE, 'DY') AS START_DAY_NAME
,to_char(END_DATE, 'DY') AS END_DATE_NAME
-- Find the difference between Resolve and Create Date
,TRUNC(END_DATE) - TRUNC(START_DATE) + 1 AS TOTAL_RESOLVE_TIME 
-- Find the number of Fridays in the whole range
,((next_day(END_date-7,'FRI')-next_day(START_DATE-1,'FRI'))/7)+1 AS NO_OF_FRI
-- Find the number of Wednesday in the whole range
,((next_day(END_date-7,'WED')-next_day(START_DATE-1,'WED'))/7)+1 AS NO_OF_WED
-- Applying the above finding the total business hours
,(TRUNC(END_DATE) - TRUNC(START_DATE) + 1)
 - (((next_day(END_date-7,'FRI')-next_day(START_DATE-1,'FRI'))/7)+1)
 - (((next_day(END_date-7,'WED')-next_day(START_DATE-1,'WED'))/7)+1) AS TOTAL_BUSINESS_HOUR
FROM
(
 SELECT 
 TO_DATE('2018-03-08', 'YYYY-MM-DD') AS START_DATE 
 ,TO_DATE('2018-03-14', 'YYYY-MM-DD') AS END_DATE 
 FROM dual
 UNION ALL
 SELECT 
 TO_DATE('2018-03-09', 'YYYY-MM-DD') AS START_DATE 
,TO_DATE('2018-03-15', 'YYYY-MM-DD') AS END_DATE 
 FROM dual
 UNION ALL
 SELECT 
 TO_DATE('2018-03-02', 'YYYY-MM-DD') AS START_DATE 
 ,TO_DATE('2018-03-11', 'YYYY-MM-DD') AS END_DATE 
 FROM dual
 UNION ALL
  SELECT 
 TO_DATE('2018-03-01', 'YYYY-MM-DD') AS START_DATE 
 ,TO_DATE('2018-03-17', 'YYYY-MM-DD') AS END_DATE 
 FROM dual) a;

Upvotes: 1

Related Questions