How many consequtives? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How many consequtives?

Author

Topic

eladlavi
Starting Member

2 Posts

Posted - 2011-01-08 : 04:18:54

Hello all!

Here's my challenge:

I have a table with columns:
UserID, LocationID, IsOK(bit), CheckTime(datetime)

there's no index on this table, no unique, combination of UserID and LocationID may appear many times.

For each combination of UserID and LocationID, I'd like to know how many consecutive "OK"(IsOK=1) records are there, chronologically(CheckTime).

I'm not sure if my explanation is clear, but users pass through check in different locations, and I need to know for each combination of location and user, how many times the user passed check OK consecutively. Every time there's a check that is False, the count restarts.

I don't want to hold a table that I increment a counter for each True check and zero it each False check. Instead, the information must be deduced from the above table. Bottom line, I need to know those that passed 3 times check OK in a location, so the user doesn't have to go through this location again.

Thanks so much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-08 : 05:18:15

You can use ROW_NUMBER for this problem, to find the islands of IsOK = 1.
Then use COUNT to get the largest island.

N 56°04'39.26"
E 12°55'05.63"

eladlavi
Starting Member

2 Posts

Posted - 2011-01-08 : 05:50:52

quote:
Originally posted by Peso

You can use ROW_NUMBER for this problem, to find the islands of IsOK = 1.
Then use COUNT to get the largest island.

N 56°04'39.26"
E 12°55'05.63"

Would you please provide further explanation, maybe give an example of a query? I'm not familiar with ROW_NUMBER? what shall be the query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-08 : 06:52:37

Here is a stub for you

DECLARE	@Sample TABLE
	(
		UserID INT,
		LocationID INT,
		IsOK BIT,
		CheckTime DATETIME
	)

INSERT	@Sample
SELECT	1, 1, 1, '20100101' UNION ALL
SELECT	1, 1, 1, '20100103' UNION ALL
SELECT	1, 2, 0, '20100104' UNION ALL
SELECT	1, 2, 1, '20100105' UNION ALL
SELECT	1, 2, 1, '20100106' UNION ALL
SELECT	2, 1, 0, '20100102' UNION ALL
SELECT	2, 2, 1, '20100107' UNION ALL
SELECT	1, 1, 0, '20100102' UNION ALL
SELECT	1, 1, 1, '20100106' UNION ALL
SELECT	1, 1, 1, '20100104' UNION ALL
SELECT	1, 1, 1, '20100105'

;WITH cte
AS (
	SELECT	UserID,
		LocationID,
		IsOK,
		CheckTime,
		ROW_NUMBER() OVER (PARTITION BY UserID, LocationID ORDER BY CheckTime) -
		ROW_NUMBER() OVER (PARTITION BY UserID, LocationID, IsOK ORDER BY CheckTime) AS Yak
	FROM	@Sample
)
SELECT		UserID,
		LocationID,
		IsOK,
		CheckTime,
		COUNT(*) OVER (PARTITION BY UserID, LocationID, IsOK, Yak) AS Entries
FROM		cte
ORDER BY	UserID,
		LocationID,
		CheckTime

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources