CODEWITHSUNDEEP
sqlsql-servert-sql
Tatranskymedved
Tatranskymedved

Reputation: 4381

TSQL - Sum on time column with condition

Need to create a query, which will get summary of time during which was bit set ON/OFF.

Example:

╔═══════════════════════════╗
║           TABLE           ║
╠════╦════════╦═════╦═══════╣
║ ID ║  TIME  ║ BIT ║ VALUE ║
╠════╬════════╬═════╬═══════╣
║  1 ║ 13:40  ║  1  ║   5   ║
║  2 ║ 13:45  ║  1  ║   3   ║
║  3 ║ 13:50  ║  1  ║   1   ║
║  4 ║ 13:55  ║  0  ║   2   ║
║  5 ║ 14:00  ║  0  ║   7   ║
║  6 ║ 14:05  ║  1  ║   3   ║
║  7 ║ 14:10  ║  1  ║   4   ║
║  8 ║ 14:15  ║  0  ║   2   ║
║  9 ║ 14:20  ║  1  ║   2   ║
╚════╩════════╩═════╩═══════╝

I would like to have total summary of TIME (and VALUE - simpler one) when the BIT was SET ON:

13:40 - 13:50 = 10 mins
14:05 - 14:10 = 5 mins
14:20         = no end time, 0 mins
-----------------------------------------
                15 mins

Have found:

I thought that this could be done as a recursive function (passing last processed datetime), which will pass the last date which was handled, and sum the datetime since the BIT is ON.

SQL query for summing the VALUE (easy one):

SELECT SUM(Value)
FROM Table
WHERE Bit = 1

How should I get total value of minutes (time), during which was the BIT set ON?

EDIT: Query which can be used for testing:

DECLARE @Table TABLE(
    ID INT Identity(1,1) PRIMARY KEY,
    [TIME] DATETIME NOT NULL,
    [BIT] BIT NOT NULL,
    [VALUE] INT NOT NULL
);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('13:40',1,5);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('13:45',1,3);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('13:50',1,1);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('13:55',0,2);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('14:00',0,7);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('14:05',1,3);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('14:10',1,4);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('14:15',0,2);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('14:20',1,2);
SELECT * FROM @Table;

Upvotes: 2

Views: 558

Answers (3)

usart
usart

Reputation: 56

Use LEAD function to get time in the next row and to calculate time interval. Then just group result by [bit]

WITH t AS(
SELECT
    [time],
    DATEDIFF(minute, [time], LEAD([time], 1, null) OVER (ORDER BY [time])) AS interval,
    [bit],
    [value]
FROM table1)
SELECT [bit], CAST(DATEADD(MINUTE, SUM(interval), '00:00') AS TIME), SUM([value]) FROM t
GROUP BY [bit]

Upvotes: 2

Richard Hansell
Richard Hansell

Reputation: 5403

This is a "gaps and islands" problem, with a pretty standard solution. I came up with this, which is pretty much the same as Gordon's, but has an extra step to calculate the intervals. This is the only reason I am posting what is essentially a duplicate answer, I'm not sure that taking the difference in minutes from zero actually works?

DECLARE @table TABLE (id int, [time] TIME, [bit] BIT, value INT);
INSERT INTO @table SELECT 1, '13:40', 1, 5;
INSERT INTO @table SELECT 2, '13:45', 1, 3;
INSERT INTO @table SELECT 3, '13:50', 1, 1;
INSERT INTO @table SELECT 4, '13:55', 0, 2;
INSERT INTO @table SELECT 5, '14:00', 0, 7;
INSERT INTO @table SELECT 6, '14:05', 1, 3;
INSERT INTO @table SELECT 7, '14:10', 1, 4;
INSERT INTO @table SELECT 8, '14:15', 0, 2;
INSERT INTO @table SELECT 9, '14:20', 1, 2;
WITH x AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY [bit] ORDER BY id) AS a_id, ROW_NUMBER() OVER (ORDER BY id) AS b_id FROM @table),
y AS (
    SELECT [bit], MIN([time]) AS min_time, MAX([time]) AS max_time, SUM(value) AS value FROM x GROUP BY a_id - b_id, [bit])
SELECT [bit], SUM(value) AS total_value, SUM(DATEDIFF(MINUTE, min_time, max_time)) AS total_minutes FROM y GROUP BY [bit];

Results:

bit total_value total_minutes
0   11          5
1   18          15

As a bonus here is a solution that only solves the actual question, i.e. how much elapsed time is there when the BIT is set to 1:

WITH x AS (SELECT id, id - DENSE_RANK() OVER(ORDER BY id) AS grp FROM @table WHERE [bit] = 1), y AS (SELECT MIN(id) AS range_start, MAX(id) AS range_end FROM x GROUP BY grp)
SELECT SUM(DATEDIFF(MINUTE, t1.[time], t2.[time])) AS minutes_elapsed FROM y INNER JOIN @table t1 ON t1.id = y.range_start INNER JOIN @table t2 ON t2.id = y.range_end;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You have two issues: summing up the time and identify the adjacent values. You can handle the second with the difference of row numbers approach. You can handle the former by converting to minutes:

select bit, min(time), max(time),
       sum(datediff(minute, 0, time)) as minutes,
       sum(value)
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by bit order by id) as seqnum_b
      from t
     ) t
group by (seqnum - seqnum_b), bit;

Upvotes: 2

Related Questions