Skip to content
Advertisement

T-SQL Calculating Time Quarters

Person      Beginning            Ending               Length
1           1/1/2022 12:25:00    1/1/2022 13:05:00    2400

I would like to parse this record across quarter buckets and the length spent in each quarter bucket.

Person      Beginning            Ending               Length      Bucket 
1           1/1/2022 12:25:00    1/1/2022 13:05:00    300         12:15     
1           1/1/2022 12:25:00    1/1/2022 13:05:00    900         12:30 
1           1/1/2022 12:25:00    1/1/2022 13:05:00    900         12:45 
1           1/1/2022 12:25:00    1/1/2022 13:05:00    300         13:00 

Advertisement

Answer

Just another option … BRUTE FORCE

Example or dbFiddle

Select A.Person
      ,A.[Begining]
      ,A.[Ending]
      ,B.[Length]
      ,Bucket = left(convert(varchar(10),Bucket,8),5)
 From  YourTable A
 Cross Apply ( Select [Length] = sum(1)
                      ,Bucket  
                  From (Select Top (datediff(SECOND,[Begining],[Ending])) D=dateadd(SECOND,-1+Row_Number() Over (Order By (Select NULL)),[Begining]) From master..spt_values n1, master..spt_values n2 ) B1
                  Cross Apply ( values (dateadd(minute,(datediff(minute,0,D) / 15 ) * 15,0)  ) ) C(Bucket)
                  Group By Bucket
             ) B

Results

enter image description here

10 People found this is helpful
Advertisement