Need a query to group by consecutive minutes

[tjw2006]

Hi, been really struggling with this one - I've got a few records that are listed in time order and by company ID, I need to group them together by company and consecutive minutes to total up the count of minutes, so that if there's an entry for 3 consecutive minutes, the first record of the three will show 3, the second 2 etc. Here's an example of what I'm looking for

Company ID Time Duration
AAA 13:09 3
AAA 13:10 2
AAA 13:11 1
BBB 15:04 1
BBB 15:32 1
CCC 18:25 2
CCC 18:26 1
DDD 20:05 4
DDD 20:06 3
DDD 20:07 2
DDD 20:08 1
EEE 22:35 1

Hope this is clear enough, any help with be gratefully received.

Thanks

 

[tjw2006]

Anyone?

 

[gmmastros]

If AAA exists again, I assume you want to 'start over'???

[tt]
Company ID Time Duration
AAA 13:09 3
AAA 13:10 2
AAA 13:11 1
BBB 15:04 1
BBB 15:32 1
CCC 18:25 2
CCC 18:26 1
DDD 20:05 4
DDD 20:06 3
DDD 20:07 2
DDD 20:08 1
EEE 22:35 1
[red]AAA 23:10 2
AAA 23:11 1[/red]
[/tt]

If this is possible, it causes things to get a little more difficult (I think).

Also, what version of SQL Server are you using?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tjw2006]

AAA can exist again, but will be unique by time and date in the final version. So, basically in the case of AAA the span of consecutive minutes from 13:09-13:11 is 3 (inclusive) and subsequently the span from 13:10-13:11 is 2 and this is basically what I need to achieve. I'm using SQL 2000 by the way.

Thanks

 

[gmmastros]

You'll be glad to know that I have managed to come up with a solution. Unfortunately, it's really ugly, and difficult to explain. But I'll try.

I approached this problem by first determining the transitions between groups of rows. Also, I needed to add a 'RowId' column. To do this, I create a table variable with an identity column. So the data looks something like this...

[tt][blue]
RowId CompanyId Time
----------- --------- -----
1 AAA 13:09
2 AAA 13:10
3 AAA 13:11
4 BBB 15:04
5 BBB 15:32
6 CCC 18:25
7 CCC 18:26
8 DDD 20:05
9 DDD 20:06
10 DDD 20:07
11 DDD 20:08
12 EEE 22:35
[/blue]

Next, I pick out the transitions where a company starts, end, or the next row is not 1 minute different from previous/next row.

[tt][blue]
RowId CompanyId Time Start Of Company End Of Company Transition
----------- --------- ----- ---------------- --------------
----------
1 AAA 13:09 X
2 AAA 13:10
3 AAA 13:11 X
4 BBB 15:04 X X
5 BBB 15:32 X X
6 CCC 18:25 X
7 CCC 18:26 X
8 DDD 20:05 X
9 DDD 20:06
10 DDD 20:07
11 DDD 20:08 X
12 EEE 22:35 X X
[/blue][/tt]

If you pull out the X's along with the corresponding RowId's, you end up with:

[tt][blue]
RangeId RowId CompanyId
----------- ----------- --------------------
1 1 AAA
2 3 AAA
3 4 BBB
4 4 BBB
5 5 BBB
6 5 BBB
7 6 CCC
8 7 CCC
9 8 DDD
10 11 DDD
11 12 EEE
12 12 EEE
[/blue][/tt]

I added a range id (identity). This allows me to do a self join to get this...

[tt][blue]
MinRow MaxRow
----------- -----------
1 3
4 4
5 5
6 7
8 11
12 12
[/blue][/tt]

Finally, I can join the original table to the above table variable to get the final results.

Of course, there's lots of code here, and some of it may seem strange (depending on your experience and level of ability). If there's anything in here that you don't understand, let me know and I will explain it more.

In the code I show below, I create a table variable named @Data. I then hard code your sample data in to this table so that I could test the code. This means you can copy/paste this code to a query window and run it as is to see how it works. In the final version, you will need to replace @Data with your actual table.

SET NOCOUNT ON

[green]-- Setup test data[/green]
Declare @Data Table(CompanyId VarChar(3), Time DateTime, Duration Int)

Insert Into @Data Values('AAA','13:09',3)
Insert Into @Data Values('AAA','13:10',2)
Insert Into @Data Values('AAA','13:11',1)
Insert Into @Data Values('BBB','15:04',1)
Insert Into @Data Values('BBB','15:32',1)
Insert Into @Data Values('CCC','18:25',2)
Insert Into @Data Values('CCC','18:26',1)
Insert Into @Data Values('DDD','20:05',4)
Insert Into @Data Values('DDD','20:06',3)
Insert Into @Data Values('DDD','20:07',2)
Insert Into @Data Values('DDD','20:08',1)
Insert Into @Data Values('EEE','22:35',1)

[green]-- create a table variable with RowId Column[/green]
Declare @Temp Table(RowId Int Identity(1,1), CompanyId VarChar(3), Time DateTime)
Insert Into @Temp(CompanyId, Time)
Select CompanyId, Time
From   @Data
Order By CompanyId, Time

[green]-- Get the transitions[/green]
Declare @Range Table(RangeId Int Identity(1,1), RowId Int, CompanyId VarChar(20))

Insert Into @Range(RowId, CompanyId)

Select Min(RowId) As RowId, CompanyId 
From   @Temp
Group By CompanyId

Union All

Select Max(RowId), CompanyId
From   @Temp
Group By CompanyId

Union All

Select	A.RowId, A.CompanyId
From	@Temp As A
		Inner Join @Temp As B
			On A.CompanyId = B.CompanyId
			And A.RowId = B.RowId - 1
			And A.Time <> DateAdd(Minute, -1, B.Time)

Union All

Select	B.RowId, B.CompanyId
From	@Temp As A
		Inner Join @Temp As B
			On A.CompanyId = B.CompanyId
			And A.RowId = B.RowId - 1
			And A.Time <> DateAdd(Minute, -1, B.Time)

Order By CompanyId, RowId

[green]-- Final query to merge the transitions
-- back to the original data.[/green]
Select T.CompanyId, T.Time, Ranges.MaxRow - RowId + 1 As Duration
From   @Temp T
       Inner Join (
          Select A.RowId As MinRow, B.RowId As MaxRow
          From   @Range As A 
                 Inner Join @Range As B 
                    On A.RangeId = B.RangeId - 1 
                    And A.RangeId % 2 = 1
          ) As Ranges
          On T.RowId Between Ranges.MinRow And Ranges.MaxRow

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tjw2006]

That looks great, thanks for all your help George, I'll give it a go and let you know how it goes.

Thanks again

 

[tjw2006]

That's incredible George, it works like a charm - you're a genius - I've been working on that for 2 days! Thanks again.

 

[EKOnerhime]

Ah, George beat me to it, I dug out a version without temporary tables.

CREATE TABLE #Duration([DurationId] int identity(1,1), [Company ID] varchar(3), [Time] datetime)

INSERT INTO #Duration([Company ID], [Time]) VALUES('AAA', '13:09')
INSERT INTO #Duration([Company ID], [Time]) VALUES('AAA', '13:10')
INSERT INTO #Duration([Company ID], [Time]) VALUES('AAA', '13:11')
INSERT INTO #Duration([Company ID], [Time]) VALUES('BBB', '15:04')
INSERT INTO #Duration([Company ID], [Time]) VALUES('BBB', '15:32')
INSERT INTO #Duration([Company ID], [Time]) VALUES('CCC', '18:25')
INSERT INTO #Duration([Company ID], [Time]) VALUES('CCC', '18:26')
INSERT INTO #Duration([Company ID], [Time]) VALUES('DDD', '20:05')
INSERT INTO #Duration([Company ID], [Time]) VALUES('DDD', '20:06')
INSERT INTO #Duration([Company ID], [Time]) VALUES('DDD', '20:07')
INSERT INTO #Duration([Company ID], [Time]) VALUES('DDD', '20:08')
INSERT INTO #Duration([Company ID], [Time]) VALUES('EEE', '22:35')

SELECT grp.[Company ID], grp.[Time], COUNT(*)
FROM
(
	SELECT d.[Company ID], d.[Time], MIN(j_d.[Time]) Max_Time
	FROM #Duration d
	LEFT OUTER JOIN 
	(
		SELECT [Company ID], [Time], DurationId
		FROM #Duration d
		WHERE NOT EXISTS 
		(
			SELECT [Company ID], [Time]
			FROM #Duration s_d
			WHERE d.[Company ID] = s_d.[Company ID]	
			AND dateadd(mi, 1,d.[Time]) = s_d.[Time]
		)
	) AS j_d ON d.[Company ID] = j_d.[Company ID] AND  d.[Time] <= j_d.[Time]
	GROUP BY d.[Company ID], d.[Time]
) grp
INNER JOIN #Duration num ON grp.[Company ID] = num.[Company ID] AND num.[Time] BETWEEN grp.[Time] AND grp.Max_Time
GROUP BY grp.[Company ID], grp.[Time]

Drop table #Duration