CODEWITHSUNDEEP
sqloracle-databasedate
user2469253
user2469253

Reputation: 89

Oracle SQL Query to fetch non-working hours in a given month

Need your help to frame a Oracle SQL query to capture non-working hours in a given month for the list of trips.

Values passed will be start date and end date

E.g.: 

Feb has totally 696 hrs
     Start Date        End Date 
S2 - 02-Feb-16 14:00 - 06-Feb-16 20:00 - 102hrs
S1 - 01-Feb-16 04:00 - 02-Feb-16 10:00 - 30hrs

                    Total Worked hrs   - 132 hrs

The query result expected is 564 hrs

Attempted Query:

SELECT (a)
FROM   (
  SELECT COUNT( (SELECT DISTINCT 'O'
                 FROM   TRIP s,
                        TRUCK p
                 WHERE  s.TRUCKID = p.TRUCKID
                 AND    p.NOTES2='ABC'
                 AND    p.TRUCKID='553'
                 AND    ( to_date(d,'DD-Mon-YYYY HH24')
                          NOT BETWEEN to_date(s.STIME,'DD-Mon-YYYY HH24')
                          AND to_date(s.ETIME,'DD-Mon-YYYY HH24')
                        )
               ) ) a
  FROM  (
    SELECT (TRUNC(to_date(sysdate,'DD-MON-YYYY'),'MM') + level - 1) d
    FROM   dual
    CONNECT BY level <= TO_CHAR(LAST_DAY(to_date(sysdate,'DD-MON-YYYY')),'DD')
  )
)

Upvotes: 1

Views: 94

Answers (1)

MT0
MT0

Reputation: 168232

WITH Trip_Dates ( Start_Date, End_Date ) AS (
  SELECT TO_DATE( '2016-02-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS' ), TO_DATE( '2016-02-06 20:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
  SELECT TO_DATE( '2016-02-01 04:00:00', 'YYYY-MM-DD HH24:MI:SS' ), TO_DATE( '2016-02-02 10:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL
)
SELECT ( ADD_MONTHS( TRUNC( SYSDATE, 'MM' ), 1 ) - TRUNC( SYSDATE, 'MM' )
         - SUM( End_Date - Start_Date ) ) * 24 AS Unworked_Hours
FROM   Trip_Dates;

Outputs:

UNWORKED_HOURS
--------------
           564

Upvotes: 1

Related Questions