Counting sequential duplicates (T-SQL)

[JustAnAverageGuy]

I have a table which lists the minutes for a day and the action being taken at that time.
For reasons I'm not going to get into, it is in the general format

Time	Action
----	------
00:00	Action a
00:01	Action b
00:02	Action c
...
23:57	Action x
23:58	Action y
23:59	Action z

What I would like to do is count the number of times each Action occurs in sequential order to find how long it took to do.
Each action can occur more than once, so it's not a simple matter of maxtime - mintime.

e.g.

Time	Action	 Minutes
----	------	 --------
00:00	Action A	4
00:01	Action A	3
00:02	Action A	2
00:03	Action A	1
00:04	Action B	4
00:05	Action B	3
00:06	Action B	2
00:07	Action B	1
00:08	Action A	4
00:09	Action A	3
00:10	Action A	2
00:11	Action A	1

The closest I've managed to get so far isn't pretty. It uses common table expressions and a quirky update...

Stripped to the basics code:

DECLARE @NumDupedA int = 0

;WITH cte as(
SELECT TOP 1500 ta.ActionA, 
		ta2.ActionA as ActionA2, 
		ta.MinutesA
FROM #TimeAction ta
LEFT JOIN #TimeAction ta2 on ta2.Time = DATEADD(minute, 1, ta.Time)
ORDER BY ta.Time DESC
)
UPDATE cte
SET @NumDupedA = MinutesA = (CASE WHEN ActionA = ActionA2 THEN @NumDupedA + 1 ELSE 1 END)

it's fast, but it's behaving... quirkily.

Time	Action	Minutes
0:00	A	26
0:01	A	25
…		
0:24	A	2
0:25	A	1
0:26	Z	6
0:27	Z	5
...
0:30	Z	2
0:31	Z	1
0:32	B	36
0:33	B	35
…		
1:06	B	2
1:07	B	1
1:08	C	36
1:09	C	35
…		
1:32	C	12
1:33	C	11
1:34	C	10
1:35	C	9	<-- does great up to here
1:36	C	1      <
1:37	D	16
1:38	D	14	<-- ?
1:39	D	12	|
1:40	D	10	|
1:41	D	8	v
1:42	D	6
1:43	D	4
1:44	D	2
1:45	D	9	<-- ?
...

I do realize it's working from the bottom up, but it's curiously inconsistent. It'll be correct for large stretches of time and then go crazy for one or two actions then be correct again. The table itself is populated correctly and doesn't contain any nulls. There are no duplicate times and each time has only one action associated with it.

It's easily solvable with a loop, but execution time is unacceptable even with small datasets.

Is there a good way to go about doing this?

SQL Server 2008

 

[KLin]

I don't see how you're differentiating between the different instances of say like the Action A's. Especially if your data table only has action name and time.

 

[JustAnAverageGuy]

A group would be any set of rows not interrupted by any other action.
e.g.

Time	Action	  Minutes	 Group
0:00	Action A	2	1
0:01	Action A	1	1
0:02	Action B	2	2
0:03	Action B	1	2
0:04	Action C	2	3
0:05	Action C	1	3
0:06	Action B	2	4
0:07	Action B	1	4
0:08	Action A	4	5
0:09	Action A	3	5
0:10	Action A	2	5
0:11	Action Q	1	6
0:12	Action A	1	7

 

[KLin]

lol wow. I don't see how you're going to accomplish what you want to do without iterating through the recordset without some code that loops.

 

[Mark R]

This works for me:

SELECT sub.Action, Min(sub2.Time)-sub.Time as duration FROM
(SELECT        Actions.Time, Actions.Action
FROM            Actions INNER JOIN
                         Actions AS Actions_1 ON Actions.Time = DATEADD(mi, 1, Actions_1.Time) AND Actions.Action <> Actions_1.Action) sub,
(SELECT        Actions.Time, Actions.Action
FROM            Actions INNER JOIN
                         Actions AS Actions_1 ON Actions.Time = DATEADD(mi, 1, Actions_1.Time) AND Actions.Action <> Actions_1.Action) sub2
WHERE sub2.Time > sub.Time
GROUP BY sub.action, sub.time
order by sub.time

It could be more cleanly done with a temporary table or table variable, as the same subquery is used twice.

It doesn't report the first task or the last task - so it needs a "dummy" adding before and after.

 

[KLin]

Okay I was bored.

Select Action, MIN(Time) As StartTime, MAX(Time) As EndTime, MAX(Time)-MIN(Time) As TimeDiff FROM
(
SELECT [Time], Action, ROW_NUMBER() OVER (Partition by Action ORDER BY RecordId)- Row_Number() Over (Order By [Time]) As SetId from actions
) sub
Group by Action, SetId
Order by MIN(Time)

1899-12-30 00:01:00.000	A
1899-12-30 00:02:00.000	A
1899-12-30 00:03:00.000	A
1899-12-30 00:04:00.000	A
1899-12-30 00:05:00.000	A
1899-12-30 00:06:00.000	B
1899-12-30 00:07:00.000	B
1899-12-30 00:08:00.000	B
1899-12-30 00:09:00.000	B
1899-12-30 00:10:00.000	B
1899-12-30 00:11:00.000	B
1899-12-30 00:12:00.000	C
1899-12-30 00:13:00.000	C
1899-12-30 00:14:00.000	C
1899-12-30 00:15:00.000	C
1899-12-30 00:16:00.000	C
1899-12-30 00:17:00.000	C
1899-12-30 00:18:00.000	C
1899-12-30 00:19:00.000	D
1899-12-30 00:20:00.000	D
1899-12-30 00:21:00.000	E
1899-12-30 00:22:00.000	E
1899-12-30 00:23:00.000	E
1899-12-30 00:24:00.000	E
1899-12-30 00:25:00.000	E
1899-12-30 00:26:00.000	A
1899-12-30 00:27:00.000	A
1899-12-30 00:28:00.000	A
1899-12-30 00:29:00.000	A
1899-12-30 00:30:00.000	Z
1899-12-30 00:31:00.000	Z
1899-12-30 00:32:00.000	Z
1899-12-30 00:33:00.000	Z
1899-12-30 00:34:00.000	Z
1899-12-30 00:35:00.000	F
1899-12-30 00:36:00.000	F
1899-12-30 00:37:00.000	F
1899-12-30 00:38:00.000	F
1899-12-30 00:39:00.000	H
1899-12-30 00:40:00.000	H

Action	Start	                        End	                      Diff
A	1899-12-30 00:01:00.000	1899-12-30 00:05:00.000	04:00.0
B	1899-12-30 00:06:00.000	1899-12-30 00:11:00.000	05:00.0
C	1899-12-30 00:12:00.000	1899-12-30 00:18:00.000	06:00.0
D	1899-12-30 00:19:00.000	1899-12-30 00:20:00.000	01:00.0
E	1899-12-30 00:21:00.000	1899-12-30 00:25:00.000	04:00.0
A	1899-12-30 00:26:00.000	1899-12-30 00:29:00.000	03:00.0
Z	1899-12-30 00:30:00.000	1899-12-30 00:34:00.000	04:00.0
F	1899-12-30 00:35:00.000	1899-12-30 00:38:00.000	03:00.0
H	1899-12-30 00:39:00.000	1899-12-30 00:40:00.000	01:00.0

I Included the sample set I used and the result set I got back.

 

[KLin]

http://smehrozalam.wordpress.com/20...est-chain-of-consecutive-alikeduplicate-rows/

Gotta give credit to that article for showing me how to group like records together and assign an id to that particular set of records.

 

[JustAnAverageGuy]

Okay I was bored.

Select Action, MIN(Time) As StartTime, MAX(Time) As EndTime, MAX(Time)-MIN(Time) As TimeDiff FROM
(
SELECT [Time], Action, ROW_NUMBER() OVER (Partition by Action ORDER BY RecordId)- Row_Number() Over (Order By [Time]) As SetId from actions
) sub
Group by Action, SetId
Order by MIN(Time)

Nice! Works great! Had been throwing my head against the desk all day yesterday on this one.

 

[Markbnj]

Nice! Works great! Had been throwing my head against the desk all day yesterday on this one.

The row_number() function comes in handy for so many things: paging, calculating medians, and the problem described in this post probably represent the top three things I use it for.