T-SQL Calculating Time Quarters-CodePudding

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

CodePudding user response：

One method is to use some "magic" dates and a Tally. This isn't particularly pretty, but looks like this:

SELECT V.Person,
       V.[Start],
       V.[End],
       V.Length,
       DATEDIFF(SECOND,CASE WHEN V.[Start] > R.BucketStart THEN V.[Start] ELSE R.BucketStart END, CASE WHEN V.[End] < R.BucketEnd THEN V.[End] ELSE R.BucketEnd END) AS Length
FROM (VALUES(1,CONVERT(datetime2(0),'1/1/2022 12:25:00',103),CONVERT(datetime2(0),'1/1/2022 13:05:00',103),2400))V(Person, [Start], [End], Length)
     CROSS APPLY (VALUES(DATEADD(MINUTE, (DATEDIFF(MINUTE,0,[Start])/15)*15,0),DATEADD(MINUTE, (DATEDIFF(MINUTE,0,[End])/15)*15,0)))B(StartBucket,EndBucket)
     CROSS APPLY fn.Tally(DATEDIFF(MINUTE,B.StartBucket,B.EndBucket) / 15,0) T
     CROSS APPLY (VALUES(DATEADD(MINUTE,T.I*15,B.StartBucket),DATEADD(MINUTE,(T.I 1)*15,B.StartBucket)))R(BucketStart,BucketEnd);

Note the use of the function Tally, which has the following definition:

CREATE   FUNCTION [fn].[Tally] (@End bigint, @StartAtOne bit) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT 0 AS I
        WHERE @StartAtOne = 0
        UNION ALL
        SELECT TOP (@End)
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7, N N8)
    SELECT I
    FROM Tally;
GO

On the next version of SQL Server (2022) then it's actually easier, as you have access to DATE_BUCKET, and also GENERATE_SERIES (though I don't use GENERATE_SERIES here for intentionally undisclosed reasons).

SELECT V.Person,
       V.[Start],
       V.[End],
       V.Length,
       DATEDIFF(SECOND,CASE WHEN V.[Start] > R.BucketStart THEN V.[Start] ELSE R.BucketStart END, CASE WHEN V.[End] < R.BucketEnd THEN V.[End] ELSE R.BucketEnd END) AS Length,
       CONVERT(time(0),R.BucketStart) AS Bucket
FROM (VALUES(1,CONVERT(datetime2(0),'1/1/2022 12:25:00',103),CONVERT(datetime2(0),'1/1/2022 13:05:00',103),2400))V(Person, [Start], [End], Length)
     CROSS APPLY (VALUES(DATE_BUCKET(MINUTE, 15, V.[Start]),DATE_BUCKET(MINUTE, 15, V.[End])))B(StartBucket,EndBucket)
     CROSS APPLY fn.Tally(DATEDIFF(MINUTE,B.StartBucket,B.EndBucket)/15,0) T
     CROSS APPLY (VALUES(DATEADD(MINUTE,T.I*15,B.StartBucket),DATEADD(MINUTE,(T.I 1)*15,B.StartBucket)))R(BucketStart,BucketEnd);

CodePudding user response：

Just another option ... BRUTE FORCE

Example or