MS SQL Server
  1. MS SQL Server
  3. Sélection de plages de time qui se chevauchent
Intereting Posts
Problèmes de compatibilité descendante avec les anciens files de package DTS Microsoft SQL Server 2000 Comment SQL Server gère-t-il les instructions SELECT dans un INSERT transactionnel? Comment insert la date de plusieurs sélections à une autre table? SQL: Ordre par colonne, puis par sous-string mix asc et desc Comment utiliser NOT dans une requête SQL relationnelle Vue SQL qui renvoie la valeur ou la valeur null si elle n'est pas trouvée UNION versus SELECT DISTINCT et UNION ALL Performance Tri T-SQL basé sur des colonnes alternées Déterminer ROW qui a provoqué une erreur "fin de file inattendue" dans BULK INSERT? SQL – Il y a déjà un object nommé 'variableName' dans la database SQL Server – Déclaration de cas pour un champ basé sur le contenu d'autres champs Détection de la taille du file avant sp_send_dbmail Problèmes de conversion avec VARCHAR à DATETIME Comment insert des valeurs dans deux tables avec une relation de key étrangère? Convertir le type de données bit en VarChar dans SQL Server

Sélection de plages de time qui se chevauchent

T-SQL DateTime Question.

J'ai un set de plages de time. Pendant ces périodes, il pourrait y avoir un set de plages de time qui se chevauchent que j'appelle le time de «blocage». Le time bloqué ne dépasserait pas un jour. Ce que je veux faire est de split le time pour exclure le time bloqué, en me donnant fondamentalement les intervalles de time qui ne sont pas bloqués. Il est prudent de supposer que les time bloqués ne peuvent pas tomber en dehors des intervalles de time.

Exemple: Je travaille de 9h à 17h avec une pause de 30 minutes à 13h. Je veux le résultat de 2 lignes: de 9h à 13h et de 13h30 à 17h.

Comme je l'ai mentionné, j'ai un set de plages de time, donc dans l'exemple ci-dessus les heures de travail peuvent différer quotidiennement et le nombre de pauses ainsi que leur durée peuvent différer.

Je suppose qu'en termes de SQL les parameters d'input ressemblerait à ceci: 

declare @timeranges table ( StartDateTime datetime, EndDateTime datetime ) declare @blockedtimes table ( StartDateTime datetime, EndDateTime datetime ) insert into @timeranges select '01 Jan 2009 09:00:00', '01 Jan 2009 17:00:00' union select '02 Feb 2009 10:00:00', '02 Feb 2009 13:00:00' insert into @blockedtimes select '01 Jan 2009 13:00:00', '01 Jan 2009 13:30:00' union select '02 Feb 2009 10:30:00', '02 Feb 2009 11:00:00' union select '02 Feb 2009 12:00:00', '02 Feb 2009 12:30:00'

L'set de résultats ressemblerait à ceci. 

 Start End --------------------- --------------------- '01 Jan 2009 09:00:00' '01 Jan 2009 13:00:00' '01 Jan 2009 13:30:00' '01 Jan 2009 17:00:00' '02 Feb 2009 10:00:00' '02 Feb 2009 10:30:00' '02 Feb 2009 11:00:00' '02 Feb 2009 12:00:00' '02 Feb 2009 12:30:00' '02 Feb 2009 13:00:00'

Je pourrais le faire avec un slider ou en boucle mais si quelqu'un pouvait suggérer comment le faire sans itération, ce serait génial – merci.

Première coupe, peut avoir quelques problèmes, mais je vais continuer à travailler dessus.
Fonctionne pour datatables données, il suffit d'essayer des scénarios supplémentaires 

 declare @timeranges table ( StartDateTime datetime, EndDateTime datetime ) declare @blockedtimes table ( StartDateTime datetime, EndDateTime datetime ) insert into @timeranges select '01 Jan 2009 09:00:00', '01 Jan 2009 17:00:00' union select '02 Feb 2009 10:00:00', '02 Feb 2009 13:00:00' --union select '03 Feb 2009 10:00:00', '03 Feb 2009 15:00:00' insert into @blockedtimes select '01 Jan 2009 13:00:00', '01 Jan 2009 13:30:00' union select '02 Feb 2009 10:30:00', '02 Feb 2009 11:00:00' union select '02 Feb 2009 12:00:00', '02 Feb 2009 12:30:00' --build an ordered, time range table with an indicator --to determine which ranges are timeranges 'tr' --and which are blockedtimes 'bt' -- declare @alltimes table (row int, rangetype varchar(10), StartDateTime datetime, EndDateTime datetime ) insert into @alltimes select row_number() over (order by a.startdatetime), * from ( select 'tr' as rangetype ,startdatetime, enddatetime from @timeranges union select 'bt' as rangetype ,startdatetime, enddatetime from @blockedtimes )a --what does the data look like -- select * from @alltimes -- -- build up the results select --start time is either the start time of a timerange, or the end of a blockedtime case when at1.rangetype = 'tr' then at1.startdatetime when at1.rangetype = 'bt' then at1.enddatetime end as [Start], case --a time range followed by another time range : end time from the current time range when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'tr' then at1.enddatetime --a time range followed by nothing (last record) : end time from the currenttime range when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) is null then at1.enddatetime --a time range followed by a blockedtime : end time is start time of blocked time when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'bt' then (select top 1 at2.startdatetime from @alltimes at2 where at2.row > at1.row and at2.rangetype = 'bt' order by row) --a blocked time followed by a blockedtime : end time is start time of next blocked time when at1.rangetype = 'bt' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'bt' then (select top 1 at2.startdatetime from @alltimes at2 where at2.row > at1.row and at2.rangetype = 'bt' order by row) --a blocked time followed by a time range : end time is end time of previous time range when at1.rangetype = 'bt' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'tr' then (select top 1 at2.enddatetime from @alltimes at2 where at2.row < at1.row and at2.rangetype = 'tr' order by row desc) --a blocked time followed by nothing (last record) : end time is end time of previous time range when at1.rangetype = 'bt' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) is null then (select top 1 at2.enddatetime from @alltimes at2 where at2.row < at1.row and at2.rangetype = 'tr' order by row desc) end as [End] from @alltimes at1

Je pensais partager la solution sur laquelle je me suis finalement arrêté:

Léger ajustement à la table temporaire en ce que j'ai ajouté un champ StartDate à @timeranges et @blockedtimes 

 declare @timeranges table ( StartDate datetime, StartDateTime datetime, EndDateTime datetime ) declare @blockedtimes table ( StartDate datetime, StartDateTime datetime, EndDateTime datetime )

Quoi qu'il en soit, semble plus simple que certains de l'autre réponse posté – applaudissements pour tout le monde aide 🙂 

 select * from ( -- first SELECT get start boundry select t.StartDateTime s, b.StartDateTime e from @timeranges t, @blockedtimes b where -- same day and blocks overlaps timerange t.StartDate = b.StartDate and (t.StartDateTime <= b.EndDateTime and b.StartDateTime <= t.EndDateTime) and -- the following is the important bit for this SELECT not exists (select 1 from @blockedtimes b2 where b2.StartDate = b.StartDate and b2.StartDateTime < b.StartDateTime) union -- second SELECT get spikes ie middle select b1.EndDateTime s, b2.StartDateTime e from @timeranges t, @blockedtimes b1, @blockedtimes b2 where -- same day and blocks overlaps timerange t.StartDate = b1.StartDate and (t.StartDateTime <= b1.EndDateTime and b1.StartDateTime <= t.EndDateTime) and -- same day and blocks overlaps timerange t.StartDate = b2.StartDate and (t.StartDateTime <= b2.EndDateTime and b2.StartDateTime <= t.EndDateTime) and -- the following is the important bit for this SELECT b1.EndDateTime < b2.StartDateTime union -- third SELECT get end boundry select b.EndDateTime s, t.EndDateTime e from @timeranges t, @blockedtimes b where -- same day and blocks overlaps timerange t.StartDate = b.StartDate and (t.StartDateTime <= b.EndDateTime and b.StartDateTime <= t.EndDateTime) and -- the following is the important bit for this SELECT not exists (select 1 from @blockedtimes b2 where b2.StartDate = b.StartDate and b2.StartDateTime > b.StartDateTime) ) t1

Voici une solution qui devrait fonctionner si deux conditions sont remplies pour datatables d'input: A) Chaque intervalle de time bloqué tombe dans un intervalle de time unique. (Vous avez dit que cela pourrait être supposé.), Et B) Les intervalles de time bloqués ne se chevauchent pas – c'est-à-dire, aucun time n'est "doublement bloqué" en tombant dans plus d'un intervalle bloqué. 

  with TB(src,S,E) as ( select 'T', StartDateTime, EndDateTime from @timeranges as T union all select 'B', StartDateTime, EndDateTime from @blockedtimes as B ), TBP(evt,switch,DT,rk) AS ( select src+DT, CHARINDEX(src+DT,'TEBSTSBE')/5 AS OffOn, EventDT, row_number() over ( order by EventDT, CHARINDEX(src+DT,'TEBSTSBE')/5 desc ) as rk from TB UNPIVOT ( EventDT FOR DT in ([S],[E]) ) as U ) select min(DT) as StartDateTime, max(DT) as EndDateTime from TBP group by (rk-1)/2 having min(DT) < max(DT) order by (rk-1)/2;

Comment cela marche-t-il?

Il balise d'abord toutes les valeurs datetime avec 0 ou 1 pour indiquer si la disponibilité se termine (0 pour les valeurs EndDateTime dans @timeranges et pour StartDateTime dans @blockedtimes) ou commence (1, pour les deux autres possibilités) au moment donné. Ensuite, les time et les balises sont mis en ordre par heure, tag et numérotés avec une colonne rk qui utilise la fonction row_number. Le marquage pourrait être rendu plus lisible avec une expression CASE, mais CHARINDEX était less à taper …

En raison des suppositions, la séquence d'label va alterner entre 0 et 1: 0,1,0,1,0,1 …, avec chaque paire (0,1) consécutive indiquant le début et la fin d'un intervalle de disponibilité. Ces intervalles peuvent être numérotés avec (rk-1) / 2.

Les lignes sont regroupées sur chaque intervalle de disponibilité. La date-heure minimale dans le groupe est l'heure de début, et la valeur maximale correspond à l'heure de fin, et si elles sont différentes, le groupe représente un intervalle non vide qui appartient à l'set de résultats. Notez que pour vos données, il n'y a pas d'intervalles vides, mais il y aurait si deux time bloqués étaient en butée ou si un time bloqué se terminait simultanément avec une plage de time.

Enfin, les résultats sont pivotés pour le format d'affichage souhaité.

Ce n'est pas le code le plus simple à lire, mais cela pourrait être utile. Des solutions comme celle-ci qui utilisent row_number et grouping sont parfois utiles pour résoudre des questions difficiles. 

 SELECT COALESCE(bt.StartDateTime, tr.StartDateTime), bt.EndDateTime FROM @timeranges tr CROSS APPLY ( SELECT bp.StartDateTime, bt.StartDateTime AS EndDateTime FROM ( SELECT StartDateTime FROM @blockedtimes bt WHERE bt.EndDateTime >= tr.StartDateTime AND bt.StartDateTime <= tr.EndDateTime UNION ALL SELECT tr.EndDateTime ) bt OUTER APPLY ( SELECT TOP 1 EndDateTime AS StartDateTime FROM @blockedtimes bti WHERE bti.EndDateTime >= tr.StartDateTime AND bti.StartDateTime <= tr.EndDateTime AND bti.StartDateTime < bt.StartDateTime ORDER BY bti.StartDateTime DESC ) AS bp ) bt

Cette solution repose sur les hypothèses suivantes:

  • Les minéralisations ne chevauchent jamais d'autres temporisateurs
  • Les heures bloquées ne chevauchent jamais les autres heures bloquées