i have a table view of table taking scenario of a hotel that works 24/7 non stop, i want to calculate total number of hours in a month when no one is present .

in image you can see we have shift hours in 24hh format, shifts are 8 hours long and different employees may have different week offs , we have date range for which this shift would be valid , i.e. a typical month and we have planned leaves when employee is completely off.

can you suggest oracle SQL to find total number of hours when no employee is working in a month.

CodePudding user response：

You can generate the shifts and then find the shifts where no-one worked:

WITH shifts (shift_start) AS (
  SELECT DATE '2022-07-01'   INTERVAL '6' HOUR   INTERVAL '8' HOUR * (LEVEL - 1)
  FROM   DUAL
  CONNECT BY
         DATE '2022-07-01'   INTERVAL '6' HOUR   INTERVAL '8' HOUR * (LEVEL - 1)
           < DATE '2022-08-01'
)
SELECT s.shift_start
FROM   shifts s
WHERE  NOT EXISTS(
         SELECT 1
         FROM   work_master w
         WHERE  w.shift_date_frm <= s.shift_start
         AND    s.shift_start < w.shift_date_to   INTERVAL '1' DAY
         AND    ':' || w.week_off_day || ':' NOT LIKE '%:' || TO_CHAR(s.shift_start, 'Dy') || ':%'
         AND    EXTRACT(HOUR FROM CAST(s.shift_start AS TIMESTAMP)) = w.shift_start
         AND    ( 
                  (
                        w.vac_date_frm IS NULL
                    AND w.vac_date_to IS NULL
                  )
                OR NOT (
                        w.vac_date_frm <= s.shift_start
                    AND s.shift_start < w.vac_date_to   INTERVAL '1' DAY
                  )
                )
       )

Which, for the sample data:

CREATE TABLE work_master (
  employee_name,
  shift_start,
  shift_end,
  shift_date_frm,
  shift_date_to,
  vac_date_frm,
  vac_date_to,
  week_off_day
) AS
SELECT 'emp1', 22,  6, DATE '2022-07-01', DATE '2022-07-31', NULL, NULL, 'Sat:Sun' FROM DUAL UNION ALL
SELECT 'emp2', 14, 22, DATE '2022-07-01', DATE '2022-07-31', NULL, NULL, 'Sat:Sun' FROM DUAL UNION ALL
SELECT 'emp3',  6, 14, DATE '2022-07-01', DATE '2022-07-31', DATE '2022-07-27', DATE '2022-07-27', 'Sat:Sun' FROM DUAL UNION ALL
SELECT 'emp4', 14, 22, DATE '2022-07-01', DATE '2022-07-31', NULL, NULL, 'Fri:Sat' FROM DUAL UNION ALL
SELECT 'emp5', 22,  6, DATE '2022-07-01', DATE '2022-07-31', NULL, NULL, 'Wed:Thu' FROM DUAL;

Outputs:

SHIFT_START
2022-07-02 06:00:00 (Sat)
2022-07-02 14:00:00 (Sat)
2022-07-03 06:00:00 (Sun)
2022-07-09 06:00:00 (Sat)
2022-07-09 14:00:00 (Sat)
2022-07-10 06:00:00 (Sun)
2022-07-16 06:00:00 (Sat)
2022-07-16 14:00:00 (Sat)
2022-07-17 06:00:00 (Sun)
2022-07-23 06:00:00 (Sat)
2022-07-23 14:00:00 (Sat)
2022-07-24 06:00:00 (Sun)
2022-07-27 06:00:00 (Wed)
2022-07-30 06:00:00 (Sat)
2022-07-30 14:00:00 (Sat)
2022-07-31 06:00:00 (Sun)

If you just want the total hours then:

WITH shifts (shift_start) AS (
  SELECT DATE '2022-07-01'   INTERVAL '6' HOUR   INTERVAL '8' HOUR * (LEVEL - 1)
  FROM   DUAL
  CONNECT BY
         DATE '2022-07-01'   INTERVAL '6' HOUR   INTERVAL '8' HOUR * (LEVEL - 1)
           < DATE '2022-08-01'
)
SELECT COUNT(*) * 8 AS hours_not_worked
FROM   shifts s
WHERE  NOT EXISTS(
         SELECT 1
         FROM   work_master w
         WHERE  w.shift_date_frm <= s.shift_start
         AND    s.shift_start < w.shift_date_to   INTERVAL '1' DAY
         AND    ':' || w.week_off_day || ':' NOT LIKE '%:' || TO_CHAR(s.shift_start, 'Dy') || ':%'
         AND    EXTRACT(HOUR FROM CAST(s.shift_start AS TIMESTAMP)) = w.shift_start
         AND    ( 
                  (
                        w.vac_date_frm IS NULL
                    AND w.vac_date_to IS NULL
                  )
                OR NOT (
                        w.vac_date_frm <= s.shift_start
                    AND s.shift_start < w.vac_date_to   INTERVAL '1' DAY
                  )
                )
       )

Which outputs:

HOURS_NOT_WORKED
128

db<>fiddle here