SQL calcule le nombre total de minutes d'arrêt

Ce que vous devez faire est de générer une séquence d'entiers et de l'utiliser pour générer les heures de la journée. Rejoignez cette séquence d'heures entre vos dates d'ouverture et de fermeture, puis comptez le nombre de fois distinctes.

Voici un exemple qui fonctionnera avec MySQL:

 SET @row_num = 0;

 SELECT COUNT(DISTINCT time_stamp)
         -- this simulates your dateopen and dateclosed table
   FROM (SELECT '2019-04-01 08:00:00' open_time, '2019-04-01 08:45:00' close_time
         UNION SELECT '2019-04-01 08:10:00', '2019-04-01 08:20:00'
         UNION SELECT '2019-04-01 09:06:00', '2019-04-01 09:07:00'
         UNION SELECT '2019-04-01 09:06:00', '2019-04-01 09:41:00') times_used
   JOIN (
         -- generate sequence of minutes in day
         SELECT TIME(sequence*100) time_stamp
           FROM (
             -- create sequence 1 - 10000
                 SELECT (@row_num:=@row_num + 1) AS sequence
                   FROM {table_with_10k+_records}
                  LIMIT 10000
                ) minutes
         HAVING time_stamp IS NOT NULL
          LIMIT 1440
       ) times ON (time_stamp >= TIME(open_time) AND time_stamp < TIME(close_time));         

Puisque vous ne sélectionnez que des heures distinctes trouvées dans le résultat, les minutes qui se chevauchent ne seront pas comptées.

REMARQUE: En fonction de votre base de données, il peut y avoir une meilleure façon de générer une séquence. MySQL n'a pas de fonction de génération de séquence. Je l'ai fait de cette façon pour montrer l'idée de base qui peut facilement être convertie pour fonctionner avec n'importe quelle base de données que vous utilisez.

réponse de @ drakin8564 adaptée pour SQL Server que je crois que vous utilisez:

;WITH Gen AS
(
    SELECT TOP 1440 
           CONVERT(TIME, DATEADD(minute, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '00:00:00')) AS t
      FROM sys.all_objects a1
     CROSS
      JOIN sys.all_objects a2
)
SELECT COUNT(DISTINCT t)
  FROM incidents inci
  JOIN Gen 
    ON Gen.t >= CONVERT(TIME, inci.DateOpen) 
   AND Gen.t < CONVERT(TIME, inci.DateClosed)

Votre total pour le dernier enregistrement est faux, dit 33 alors qu'il est 35, donc la requête donne 80, pas 78.

Au fait, comme MarcinJ vous l'a dit, 41 - 6 est 35, pas 33. Donc la réponse est 80, pas 78.

La solution suivante fonctionnerait même si le paramètre de date n'est pas un jour seulement ( 1440 minutes). Dites si le paramètre de date est un mois, voire une année, cette solution fonctionnerait toujours.

Démo en direct: http://sqlfiddle.com/#!18/462ac/5

INSERT INTO dt
    ([ID], [DateOpen], [DateClosed], [Total])
VALUES
    (1, '2019-04-01 07:00:00', '2019-04-01 07:50:00', 50),
    (2, '2019-04-01 07:45:00', '2019-04-01 08:00:00', 15),   
    (3, '2019-04-01 08:00:00', '2019-04-01 08:45:00', 45);
;

Output:

-- arranged the opening and closing downtime
with a as 
(
    select 
        DateOpen d, 1 status
    from dt
    union all
    select
        DateClosed, 2
    from dt
    -- order by d. postgres can do this?
)
-- don't compute the downtime from previous date
-- if the current date's status is opened
-- yet the previous status is closed
, downtime_minutes AS
(
    select 
        *, 
        lag(status) over(order by d, status desc) as prev_status,
        case when not ( status = 1 and lag(status) over(order by d, status desc) = 2 ) then
            datediff(minute, lag(d) over(order by d, status desc), d)
        end as downtime
    from a
)
select sum(downtime) from downtime_minutes;

Découvrez comment cela fonctionne:

Cela fonctionne en calculant le temps d'arrêt des temps d'arrêt précédents. Ne calculez pas le temps d'arrêt si l'état de la date actuelle est ouvert et l'état de la date précédente est fermé, ce qui signifie que le temps d'arrêt actuel ne se chevauche pas. Les temps d'arrêt sans chevauchement sont indiqués par null.

Pour ce nouveau temps d'arrêt ouvert, son temps d'arrêt est initialement nul, le temps d'arrêt sera calculé aux dates suivantes jusqu'à sa fermeture.

Peut raccourcissez le code en inversant la condition:

| all_downtime |
|--------------|
|           80 |

Pas particulièrement fier de ma solution originale: http://sqlfiddle.com/#!18/462ac/1

Quant au status desc code > sur order by d, status desc , si un DateClosed est similaire au DateOpen d'un autre temps d'arrêt, status desc triera la DateClosed en premier.

Pour cela données où 8h00 est présent à la fois sur DateOpened et DateClosed:

-- arranged the opening and closing downtime
with a as 
(
    select 
        DateOpen d, 1 status
    from dt
    union all
    select
        DateClosed, 2
    from dt
)
-- don't compute the downtime from previous date
-- if the current date's status is opened
-- yet the previous status is closed
, downtime_minutes AS
(
    select 
        *, 
        lag(status) over(order by d, status desc) as prev_status,
        case when status = 1 and lag(status) over(order by d, status desc) = 2 then
            null
        else
            datediff(minute, lag(d) over(order by d, status desc), d)
        end as downtime
    from a
)
select sum(downtime) as all_downtime from downtime_minutes;

Pour une heure similaire (par exemple, 8h00), si nous ne trierons pas la fermeture avant l'ouverture , alors 7h00 sera calculé jusqu'à 7h50 uniquement, au lieu de 8h00 maximum, car le temps d'arrêt de 8h00 ouvert est initialement nul. Voici comment les temps d'arrêt ouverts et fermés sont organisés et calculés s'il n'y a pas de status desc pour une date similaire, par exemple 8h00. Le temps d'arrêt total est de 95 minutes seulement, ce qui est faux. Cela devrait prendre 105 minutes.

Voici comment cela sera organisé et calculé si nous trions la date de fermeture avant la date d'ouverture ( en utilisant status desc ) lorsqu'ils ont une date similaire, par exemple 8h00. Le temps d'arrêt total est de 105 minutes, ce qui est correct.

Une autre approche utilise l'approche lacunes et îles . La réponse est basée sur SQL Time Packing of Islands

Test en direct : http://sqlfiddle.com/#!18/462ac/11 p>

iif(lag(DateClosed) over (order by DateOpen) >= DateOpen, 0, 1) as gap

Résultat:

Comment ça marche strong >

Un DateOpen est le début de la série de temps d'arrêt s'il n'a pas de temps d'arrêt précédent (indiqué par null lag (DateClosed) ). Un DateOpen est également un début de la série de temps d'arrêt s'il a un écart par rapport à la DateClosed du temps d'arrêt précédent.

with gap_detector as
(
    select 
        DateOpen, DateClosed,                 
        case when lag(DateClosed) over (order by DateOpen) >= DateOpen 
        then 
            0
        else 
            1 
        end as gap
    from dt              
)
, downtime_grouper as
(
    select 
        DateOpen, DateClosed, 
        sum(gap) over (order by DateOpen) as downtime_group
    from gap_detector
)
 -- group's open and closed detector. then computes the group's downtime
select 
    downtime_group,
    min(DateOpen) as group_date_open, 
    max(DateClosed) as group_date_closed,
    datediff(minute, min(DateOpen), max(DateClosed)) as group_downtime,

    sum(datediff(minute, min(DateOpen), max(DateClosed))) 
        over(order by downtime_group) as downtime_running_total
from downtime_grouper
group by downtime_group

Sortie:

Après avoir détecté les démarreurs d'écart, nous effectuons un total cumulé de l'écart afin de pouvoir regrouper les temps d'arrêt contigus les uns aux autres.

with gap_detector as
(
    select 
        DateOpen, DateClosed,                 
        case when 
            lag(DateClosed) over (order by DateOpen) is null 
            or lag(DateClosed) over (order by DateOpen) < DateOpen 
        then 
            1
        else 
            0 
        end as gap
    from dt              
)
select 
   DateOpen, DateClosed, gap, 
   sum(gap) over (order by DateOpen) as downtime_group
from gap_detector
order by DateOpen;