CODEWITHSUNDEEP
sqlsql-serverdatedatetime
Axarydax
Axarydax

Reputation: 16603

Calculate time duration with daily limits in SQL

I have a table that stores some events in the database, which log operational time of machines and I'd like to calculate a total running time within a specific date for a specific shift (or all shifts).

CREATE TABLE events (
    ID int, 
    StartTime datetime,
    EndTime datetime,
    DurationSeconds bigint,
    ...
)

I'd like to select total duration of events in the specified date range @dateStart datetime, @dateEnd datetime while considering daily shifts (@shiftStart time, @shiftEnd time). The events will overlap the shifts and the date ranges.

For example, if I have shift starting at 6:00 and ending at 12:00, and the event lasted for whole 2 days (2014/01/01 00:00 - 2014/01/03 00:00), the total time for this row is (48 hours - 2*6 hours = 36 hours).

If an event starts in the middle of the shift, then only the 'in-shift' portion should be considered.

So far I had an implementation without considering the shifts like:

select sum(
        --duration minus start overlap and end overlap
        duration - dbo.udf_max(datediff(s,@pTo,endtime),0) - dbo.udf_max(datediff(s,starttime,@pFrom),0)
        ) 
    from events
    where starttime < @pTo and endtime > @pFrom

I'd really like to have a set-based solution as the data sets are rather large and consider a looping cursor-based solution as the last resort.

Upvotes: 0

Views: 470

Answers (1)

Kevin Cook
Kevin Cook

Reputation: 1932

Ok lets make some test data (I changed the times a little to show variance)

DECLARE @Events TABLE
(
    ID int IDENTITY (1, 1), 
    StartTime datetime,
    EndTime datetime,
    DurationSeconds bigint
)

INSERT INTO @Events
( StartTime, EndTime )
VALUES
( '2014/01/01 01:00', '2014/01/02 22:00');

DECLARE @Shift TABLE
(
    ShiftName VARCHAR(20),
    StartTime DATETIME,
    EndTime DATETIME
)

INSERT INTO @Shift
( ShiftName, StartTime, EndTime )
VALUES
( 'Night', '2014/01/01 00:00', '2014/01/01 06:00' ),
( 'Morning', '2014/01/01 06:00', '2014/01/01 12:00' ),
( 'Afternoon', '2014/01/01 12:00', '2014/01/01 18:00' ),
( 'Evening', '2014/01/01 18:00', '2014/01/02 00:00' ),
( 'Night', '2014/01/02 00:00', '2014/01/02 06:00' ),
( 'Morning', '2014/01/02 06:00', '2014/01/02 12:00' ),
( 'Afternoon', '2014/01/02 12:00', '2014/01/02 18:00' ),
( 'Evening', '2014/01/02 18:00', '2014/01/03 00:00' );

Here I make a numbers table to find all the minutes for the events duration

DECLARE @StartDate DATETIME = '1/1/2014';
DECLARE @number_of_numbers INT = 100000;

;WITH
a AS (SELECT 1 AS i UNION ALL SELECT 1),
b AS (SELECT 1 AS i FROM a AS x, a AS y),
c AS (SELECT 1 AS i FROM b AS x, b AS y),
d AS (SELECT 1 AS i FROM c AS x, c AS y),
e AS (SELECT 1 AS i FROM d AS x, d AS y),
f AS (SELECT 1 AS i FROM e AS x, e AS y),
numbers AS 
(
    SELECT TOP(@number_of_numbers)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
    FROM f
), mins_in_day AS

Now I find all the minutes worked for each shift and total them

(
    SELECT DATEADD(MINUTE, n.number, @StartDate) AS DayMinute
    FROM numbers n
), output AS
    (
    SELECT s.ShiftName, CONVERT(DATE, s.StartTime) ShiftDay, COUNT(1) AS TotalMinutes FROM @Shift s
    INNER JOIN mins_in_day sc
        ON sc.DayMinute >= s.StartTime AND sc.DayMinute < s.EndTime
    INNER JOIN @Events e
        ON sc.DayMinute >= e.StartTime AND sc.DayMinute <e.EndTime
    GROUP BY s.ShiftName, s.StartTime
)

SELECT * FROM output

Here is the output:

ShiftName  ShiftDay TotalMinutes
Night      2014-01-01   300
Morning    2014-01-01   360
Afternoon  2014-01-01   360
Evening    2014-01-01   360
Night      2014-01-02   360
Morning    2014-01-02   360
Afternoon  2014-01-02   360
Evening    2014-01-02   240

Upvotes: 1

Related Questions