SQL vérifie si le temps se situe entre certains créneaux horaires

Question

J'ai un tableau de rendez-vous (voir ci-dessous)

ID | Start Time | End Time |
1  | 09:00      | 09:45    |
2  | 10:15      | 10:30    |

Ce que je veux faire, c'est sortir un tableau qui montre si chaque enregistrement occupe un créneau horaire de 15 minutes. Le tableau ci-dessus serait donc affiché comme suit :

09:00 - 09:15 | 09:16 - 09:30 | 09:31 - 09:45 | 09:46 - 10:00 | 10:01 - 10:15 | 10:16 - 10:30
ID 1          | ID 1          | ID 1          |               | ID 2          | ID 2

Vous avez une idée de par où commencer ?!

EDITAR:

C'est là où je veux en venir, mais je n'ai pas essayé d'inclure les heures de fin ou comment un intervalle de temps qui est couvert par l'heure de début et de fin comme ID 1 dans l'exemple à 09:16 - 09:30 :

SELECT 
'09:00 - 09:15' = case when 
cast(a_start as time) >= '09:00:00' and cast(a_start as time) < '09:16:00'
then a_id else '' END,

'09:16 - 09:30' = case when 
cast(a_start as time) >= '09:16:00' and cast(a_start as time) < '09:30:00'
then a_id else '' END,

'09:31 - 09:45' = case when 
cast(a_start as time) >= '09:31:00' and cast(a_start as time) < '09:45:00'
then a_id else '' END,     

'09:46 - 10:00' = case when 
cast(a_start as time) >= '09:46:00' and cast(a_start as time) < '10:00:00'
then a_idelse '' END 

  FROM appointments

EDIT 2 :

Maintenant, ça marche un peu :

SELECT 

    '08:00 - 08:59' = case when 
    (cast(a_start as time) <= '08:00:00') 
    AND 
    (cast(a_end as time) >= '08:59:00')
    then a_id else '' END,

    '09:00 - 09:15' = case when 
    (cast(a_start as time) <= '09:00:00') 
    AND 
    (cast(a_end as time) >= '09:15:00')
    then a_id else '' END,

    '09:16 - 09:30' = case when 
    (cast(a_start as time) <= '09:16:00') 
    AND 
    (cast(a_end as time) >= '09:30:00')
    then a_id else '' END,

    '09:31 - 09:45' = case when 
    (cast(a_start as time) <= '09:31:00') 
    AND 
    (cast(a_end as time) >= '09:45:00')
    then a_id else '' END,     

    '09:46 - 10:00' = case when 
    (cast(a_start as time) <= '09:46:00') 
    AND 
    (cast(a_end as time) >= '10:00:00')
    then a_id else '' END,

    '10:01 - 10:15' = case when 
    (cast(a_start as time) <= '10:01:00') 
    AND 
    (cast(a_end as time) >= '10:15:00')
    then a_id else '' END 

      FROM appointments

Answer 1

Vous pouvez le faire avec quelques calculs de date et une tabulation croisée (c'est-à-dire une agrégation conditionnelle). J'ai également utilisé un tally pour générer les créneaux horaires sur la base des données contenues dans votre tableau. Vous pouvez ajuster cela selon vos besoins. Personnellement, je conserve une table de pointage comme vue dans mon système. C'est super rapide et sans aucune lecture.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

Maintenant, nous avons besoin de ddl réels pour travailler avec.

if OBJECT_ID('tempdb..#Appt') is not null
    drop table #Appt

create table #Appt
(
    ID int
    , StartTime time
    , EndTime time
)
insert #Appt select 1, '09:00', '09:45';
insert #Appt select 2, '10:15', '10:30';

Pour résoudre le problème, je génère d'abord les créneaux horaires, puis je joins vos données de rendez-vous aux créneaux horaires pour déterminer quel rendez-vous (le cas échéant) serait affecté à ce créneau horaire.

Il faudra sans doute modifier un peu ce système pour qu'il réponde à vos besoins exacts, mais il devrait être assez proche.

with TimeSlots as
(
    select TimeSlot = dateadd(minute, (t.N) * 15, '09:00') --should make the start time dynamic
    from cteTally t
    where t.N <=
    (
        select datediff(minute, MIN(StartTime), MAX(EndTime)) / 15
        from #Appt
    )
)
, Appointments as
(
    select *
        , ROW_NUMBER() over(order by ts.TimeSlot) as RowNum
    from TimeSlots ts
    left join #Appt a on convert(datetime, ts.TimeSlot) >= convert(datetime, a.StartTime) 
        AND convert(datetime, ts.TimeSlot) <= convert(datetime, a.EndTime)
)

select MAX(case when RowNum = 1 then ID end) as '09:00 - 09:15'
    , MAX(case when RowNum = 2 then ID end) as '09:16 - 09:30'
    , MAX(case when RowNum = 3 then ID end) as '09:31 - 09:45'
    , MAX(case when RowNum = 4 then ID end) as '09:46 - 10:00'
    , MAX(case when RowNum = 5 then ID end) as '10:01 - 10:15'
    , MAX(case when RowNum = 6 then ID end) as '10:16 - 10:30'
from Appointments

Answer 2

Voici un pivot dynamique.

J'ai un peu triché en plaçant les résultats temporaires dans un #temp, mais cela peut être modifié si nécessaire. J'ai aussi ajouté une DATE comme première colonne (facile à enlever).

--Drop Table #Temp

Declare @TimeR1 time = '09:00'
Declare @TimeR2 time = '17:00'

Select Date = cast([Start Time] as date)
      ,Val  = concat('ID ',B.ID)
      ,Col  = Format(cast(A.TR1 as datetime),'HH:mm') +' - ' + Format(cast(A.TR2 as datetime),'HH:mm')
Into  #Temp
From (Select Top 96 
             TR1=cast(DateAdd(MINUTE,(15*(Row_Number() Over (Order By (Select null))-1))+1,'1900-01-01') as time)
            ,TR2=cast(DateAdd(MINUTE,(15*(Row_Number() Over (Order By (Select null))+0))+0,'1900-01-01') as time)
      From master..spt_values 
     ) A
Left Join YourTable B 
  on TR1 between cast([Start Time] as time)  and cast([End Time] as time) or TR2 between cast([Start Time ] as time) and cast([End Time] as time)
Where TR1 >=@TimeR1 and TR1<@TimeR2

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(Col) From #Temp  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select [Date],' + @SQL + '
From (Select * From #Temp Where Date is not null
      Union All
      Select Date,Val,Col
        From (Select Distinct Date,Val='''' from #Temp Where Date is not Null) A
        Cross Join (Select Distinct Col From #Temp) B
) A
Pivot (max(Val) For [Col] in (' + @SQL + ') ) p'
Exec(@SQL);

Renvoie à

** L'image est truquée... elle sort en fait à 17h00.