snippetsqlMinor
How to find duration in a dataset
Viewed 0 times
datasetfinddurationhow
Problem
I have a dataset of the following structure:
I would like to build a resultset that groups the null values, showing the start, end time and duration. (Null means the target was unavialble).
The StartDate would be the first NULL Value, while the EndDate would be the next NON-NULL Value, and my duration calcualtion would be based on those two values.
Any clues to build this query would be great.
As I am thinking of joining the table to itself, but I am not sure where to start.
Target, PollTime, Value
------------------------
1. A, 2013-12-13 13:31:44.000, 15.00
2. A, 2013-12-13 13:32:44.000, 16.00
3. A, 2013-12-13 13:33:44.000, 16.00
4. A, 2013-12-13 13:34:44.000, NULL
5. A, 2013-12-13 13:35:44.000, NULL
6. A, 2013-12-13 13:36:44.000, 15.00
7. B, 2013-12-21 05:29:34.000, 3.00
8. B, 2013-12-21 05:30:34.000, NULL
9. B, 2013-12-21 05:31:34.000, NULL
10. B, 2013-12-21 05:32:34.000, NULL
11. B, 2013-12-21 05:33:34.000, 4.00
12. B, 2013-12-21 05:34:34.000, NULL
13. B, 2013-12-21 05:35:34.000, NULL
14. B, 2013-12-21 05:36:34.000, 5.00I would like to build a resultset that groups the null values, showing the start, end time and duration. (Null means the target was unavialble).
Target, StartDate, EndDate, Duration(min)
---------------------------------------------
1. A, 2013-12-13 13:34:44.000,2013-12-13 13:36:44.000, 2
2. B, 2013-12-21 05:30:34.000,2013-12-21 05:33:34.000 3
3. B, 2013-12-21 05:34:34.000,2013-12-21 05:36:34.000, 2The StartDate would be the first NULL Value, while the EndDate would be the next NON-NULL Value, and my duration calcualtion would be based on those two values.
Any clues to build this query would be great.
As I am thinking of joining the table to itself, but I am not sure where to start.
Solution
As pointed out, this is better solved by Gaps and Islands.
Original source for code: Getting each status change in a table
WITH T
AS (SELECT *,
DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) -
DENSE_RANK() OVER (PARTITION BY Target, Value ORDER BY PollTime) AS Grp
FROM [schema].[Table]
)
SELECT Target,
MIN(PollTime) AS Start,
MAX(PollTime) AS Finish,
DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration
FROM T
GROUP BY Target,
Grp
HAVING MAX(Value) is null
ORDER BY StartOriginal source for code: Getting each status change in a table
Code Snippets
WITH T
AS (SELECT *,
DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) -
DENSE_RANK() OVER (PARTITION BY Target, Value ORDER BY PollTime) AS Grp
FROM [schema].[Table]
)
SELECT Target,
MIN(PollTime) AS Start,
MAX(PollTime) AS Finish,
DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration
FROM T
GROUP BY Target,
Grp
HAVING MAX(Value) is null
ORDER BY StartContext
StackExchange Database Administrators Q#65142, answer score: 4
Revisions (0)
No revisions yet.