select not available hours

Question

i have the falowing table machines_hist

Contract      start_time                Fin_time
C1       2016-01-01 05:10:10     2016-01-01 15:10:10
C1       2016-01-02 10:16:20     2016-01-03 12:14:10
C1       2016-01-05 10:16:20     2016-01-10 17:11:10
C1       2016-02-05 02:16:20     2016-01-06 19:18:10

On one contract there are several machines. Each machine has different start time and finish time. The intervals may overlap. I have to do a report which will show how many hours in contract per month machines were not working...

Is it possible? Any sugestion...

Additional information: The table shows downtime for the machines. In a larger table, there may be a column showing which specific machine is down, but that is irrelevant for this problem; we need calculations per contract, not per machine. If there are overlaps, the "common" downtime between two or more machines should not be double-counted; two machines down simultaneously counts the same as one machine being down.

Answer 1

Setup (test data):

SQL> select * from machines_hist;

CONTRACT   START_TIME          FIN_TIME
---------- ------------------- -------------------
C1         2015-12-30 05:10:10 2016-01-01 15:10:10
C1         2016-01-02 10:16:20 2016-01-03 12:14:10
C1         2016-01-25 10:16:20 2016-02-10 17:11:10
C1         2016-01-05 02:16:20 2016-01-06 19:18:10
C2         2016-01-15 12:20:22 2016-01-17 13:40:10
C2         2016-02-23 04:13:50 2016-02-24 02:20:44
C3         2016-02-20 10:13:20 2016-02-20 11:16:40
C4         2015-12-23 20:00:00 2015-12-24 12:23:00
C5         2015-12-31 22:34:00 2016-02-23 00:00:00
9 rows selected.
Elapsed: 00:00:00.33

Query: (notice the bind variables - normally provided by application):

with a as (select to_date(:mon || '-' || :yr, 'MON-yyyy') as month_start from dual),
     b as (select add_months(month_start, 1) as month_end from a),
     c as (select contract, greatest(month_start, start_time) as st, 
                            least(month_end, fin_time) as fin
           from machines_hist join a on fin_time >= month_start 
                              join b on start_time <= month_end),
     m as (select contract, st,
                   max(fin) over (partition by contract order by st
                            rows between unbounded preceding and 1 preceding) as m_time
           from c
           union all
           select contract, NULL, max(fin) from c group by contract),
     n as (select contract, st, m_time
           from m
           where st > m_time or st is null or m_time is null),
     f as (select contract, st as st_downtime,
                  lead(m_time) over (partition by contract order by st) as fin_downtime
           from n)
select contract, max(:mon || '-' || :yr) as mth, 
       round(100 * sum(fin_downtime - st_downtime)/
          ((select month_end from b) - (select month_start from a)), 2) as downtime_pct
from f
where st_downtime is not null
group by contract
order by contract
/

Bind variables (illustrating SQL*Plus interface - each program has its own mechanism):

SQL> variable yr number
SQL> variable mon varchar2(3)
SQL> begin :mon := 'JAN'; :yr := 2016; end;
  2  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03

Output (notes: script saved as "downtime.sql", called through SQL*Plus; percentage of downtime in the month expressed as 22.3 representing 22.3% etc.; if a contract had NO downtime, it is not included in the output)

SQL> start downtime

CONTRACT   MTH          DOWNTIME_PCT
---------- ------------ ------------
C1         JAN-2016            32.24
C2         JAN-2016             6.63
C5         JAN-2016              100
3 rows selected.
Elapsed: 00:00:00.19

Answer 2

Oracle Setup:

CREATE TABLE machines_hist ( Contract, start_time, Fin_time ) AS
SELECT 'C1', CAST( TIMESTAMP '2016-01-01 05:10:10' AS DATE ), CAST( TIMESTAMP '2016-01-01 15:10:10' AS DATE ) FROM DUAL UNION ALL
SELECT 'C1', CAST( TIMESTAMP '2016-01-02 10:16:20' AS DATE ), CAST( TIMESTAMP '2016-01-03 12:14:10' AS DATE ) FROM DUAL UNION ALL
SELECT 'C1', CAST( TIMESTAMP '2016-01-05 10:16:20' AS DATE ), CAST( TIMESTAMP '2016-01-10 17:11:10' AS DATE ) FROM DUAL UNION ALL
SELECT 'C1', CAST( TIMESTAMP '2016-01-05 02:16:20' AS DATE ), CAST( TIMESTAMP '2016-01-06 19:18:10' AS DATE ) FROM DUAL UNION ALL
SELECT 'C1', CAST( TIMESTAMP '2016-02-01 00:00:00' AS DATE ), CAST( TIMESTAMP '2016-03-01 00:00:00' AS DATE ) FROM DUAL UNION ALL
SELECT 'C1', CAST( TIMESTAMP '2016-04-01 00:00:00' AS DATE ), CAST( TIMESTAMP '2016-04-07 00:00:00' AS DATE ) FROM DUAL UNION ALL
SELECT 'C1', CAST( TIMESTAMP '2016-04-05 00:00:00' AS DATE ), CAST( TIMESTAMP '2016-04-10 00:00:00' AS DATE ) FROM DUAL;

Query:

WITH times ( contract, boundary_time, type ) AS (
  SELECT Contract,
         Start_time,
         1
  FROM   machines_hist
  UNION ALL
  SELECT Contract,
         fin_time,
         -1
  FROM   machines_hist
  ORDER BY 1, 2
),
bounds ( contract, boundary_time, type, boundary_value ) AS (
  SELECT t.*,
         SUM( type ) OVER ( PARTITION BY Contract
                            ORDER BY boundary_time ASC, type DESC
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
  FROM  times t
),
end_times ( contract, start_time, type, boundary_value, end_time ) AS (
  SELECT b.*,
         LEAD( CASE WHEN type = -1 AND boundary_value = 0 THEN boundary_time END )
           IGNORE NULLS
           OVER ( PARTITION BY Contract ORDER BY boundary_time )
           AS end_time
  FROM   bounds b
),
filtered_end_times ( contract, start_time, end_time ) AS (
  SELECT contract,
         start_time,
         end_time
  FROM   end_times
  WHERE  type = 1
  AND    boundary_value = 1
),
month_boundaries ( start_month, end_month ) AS (
  SELECT TRUNC( MIN( start_time ), 'MM' ),
         CASE WHEN MAX( end_time ) = TRUNC( MAX( end_time ), 'MM' )
              THEN MAX( end_time )
              ELSE TRUNC( MAX( end_time ), 'MM' ) + INTERVAL '1' MONTH
              END
  FROM   filtered_end_times
),
months ( start_month, end_month ) AS (
  SELECT ADD_MONTHS( start_month, LEVEL - 1),
         ADD_MONTHS( start_month, LEVEL)
  FROM   month_boundaries
  CONNECT BY ADD_MONTHS( start_month, LEVEL) <= end_month
)
SELECT contract,
       start_month,
       end_month
         - start_month
         - SUM( LEAST(end_month,end_time)-GREATEST(start_month,start_time) )
           AS days_not_working
FROM   months
       INNER JOIN
       filtered_end_times
       ON ( start_time <= end_month AND end_time >= start_month )
GROUP BY
       contract,
       start_month,
       end_month
ORDER BY
       contract,
       start_month

Output:

CONTRACT START_MONTH         DAYS_NOT_WORKING
-------- ------------------- ----------------
C1       2016-01-01 00:00:00       23.8800926 
C1       2016-02-01 00:00:00                0 
C1       2016-03-01 00:00:00               31 
C1       2016-04-01 00:00:00               21