Consecutive rows help - 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)

Consecutive rows help

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-30 : 13:06:28

I need some help with consecutive rows and a specific flag.

In my data, a row is consecutive when EventTime + Duration = EventTime of next row for a particular Customer. Duration is in minutes. For example, the first two rows are consecutive since it is for Customer 123456 and '2007-07-27 10:48' + 2 = '2007-07-27 10:50'. When we are looking backwards (explained later), we use subtraction instead: '2007-07-27 10:50' - 2 = '2007-07-27 10:48'.

I need the query to return the earliest EntryId for a particular group of consecutive rows. The group must have this condition: Flag & 0x4 <> 0.

Another condition is that we have to look backwards from a particular EventTime. For example, if we want to look backwards from '07-27-2007 12:12' for Customer 123456, our first row is '07-27-2007 11:42'. We do not even look at the 12:12 record per the business rule, so our first comparison is between '07-27-2007 11:42' and '07-27-2007 11:04'. None of the other rows in between those two times for 123456 have Flag & 0x4 <> 0.

One thing to note is that I can not just load all of the rows for that Customer and
where Flag & 0x4 <> 0 into a temp table/table variable or even a derived table as there could be thousands and possibly millions of rows to process. So we have to start with the row just before the EventTime provided and work backwards from there.

I can easily do this with a WHILE loop, but I am hoping there is something more efficient, possibly something using a 2005 technique.

I hope my explanation is clear. If it is not, then the sample data and various select statements provided below should illustrate my problem.

The select statements below are just showing what would be passed in. I would need the value specified in the comment above the query.


DECLARE @Event table 
(EntryId int PRIMARY KEY CLUSTERED, Customer int, Flag int, EventTime datetime, Duration smallint)

INSERT INTO @Event
SELECT 1001, 123456, 0, '07-27-2007 10:48', 2 UNION ALL
SELECT 1002, 123456, 8, '07-27-2007 10:50', 14 UNION ALL
SELECT 1006, 123456, 4, '07-27-2007 11:04', 6 UNION ALL
SELECT 1010, 123456, 0, '07-27-2007 11:10', 22 UNION ALL
SELECT 1021, 333333, 4, '07-27-2007 11:15', 31 UNION ALL
SELECT 1032, 123456, 2, '07-27-2007 11:32', 10 UNION ALL
SELECT 1109, 123456, 4, '07-27-2007 11:42', 30 UNION ALL
SELECT 1111, 333333, 20, '07-27-2007 11:46', 180 UNION ALL
SELECT 1121, 123456, 20, '07-27-2007 12:12', 15 UNION ALL
SELECT 1133, 123456, 4, '07-27-2007 12:27', 65 UNION ALL
SELECT 1156, 123456, 0, '07-27-2007 13:32', 45 UNION ALL
SELECT 1193, 123456, 20, '07-27-2007 14:17', 40 UNION ALL
SELECT 1200, 123456, 20, '07-27-2007 14:20', 3 UNION ALL
SELECT 1237, 333333, 2, '07-27-2007 14:46', 9 UNION ALL
SELECT 1239, 333333, 20, '07-27-2007 14:54', 6 UNION ALL
SELECT 1243, 333333, 2, '07-27-2007 15:00', 9 UNION ALL
SELECT 1248, 333333, 4, '07-27-2007 15:09', 42

-- return 1021, rows are consecutive
SELECT * FROM @Event
WHERE Flag & 0x4 <> 0 AND Customer = 333333 AND EventTime < '07-27-2007 14:46'
ORDER BY Customer, EventTime DESC

-- return 1006 since only 1 row returned
SELECT * FROM @Event
WHERE Flag & 0x4 <> 0 AND Customer = 123456 AND EventTime < '07-27-2007 11:10'
ORDER BY Customer, EventTime DESC

-- empty result set
SELECT * FROM @Event
WHERE Flag & 0x4 <> 0 AND Customer = 123456 AND EventTime < '07-27-2007 11:04'
ORDER BY Customer, EventTime DESC

-- return 1109 since 1109, 1121, 1133 are consecutive 
SELECT * FROM @Event
WHERE Flag & 0x4 <> 0 AND Customer = 123456 AND EventTime < '07-27-2007 14:17'
ORDER BY Customer, EventTime DESC

-- return 1248 since 1239 is not consecutive with 1248
SELECT * FROM @Event
WHERE Flag & 0x4 <> 0 AND Customer = 333333 AND EventTime < '07-27-2007 15:51'
ORDER BY Customer, EventTime DESC

Tara Kizer
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 14:34:47

ok i went over it 3 times and i don't get your example selects.
could you maybe post say an input variable that we can work with and an expected result for that variable?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-30 : 14:48:58

The SELECT statements are just showing sample inputs and do not reflect what should be returned. In the comment directly above it says what needs to be returned, I only need the EntryId returned.

These are all EntryIds:
For the first (Flag & 0x4 <> 0 AND Customer = 333333 AND EventTime < '07-27-2007 14:46'), it should return 1021 since all rows are consecutive.
For the second (Flag & 0x4 <> 0 AND Customer = 123456 AND EventTime < '07-27-2007 11:10'), it should return 1006 returned since only 1 row matches the inputs.
For the third (Flag & 0x4 <> 0 AND Customer = 123456 AND EventTime < '07-27-2007 11:04'), it should return 0 rows since no rows match the inputs.
For the fourth (Flag & 0x4 <> 0 AND Customer = 123456 AND EventTime < '07-27-2007 14:17'), it should return 1109 since 1109, 1121, and 1133 are consecutive and match the inputs.
For the fifth (Flag & 0x4 <> 0 AND Customer = 333333 AND EventTime < '07-27-2007 15:51'), it should return 1248 since 1239 is not consecutive with 1248.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 17:55:52

I hope you will enjoy this!

-- Prepare sample data
DECLARE	@Event TABLE
	(
		EntryId INT PRIMARY KEY CLUSTERED,
		Customer INT,
		Flag INT,
		EventTime DATETIME,
		Duration SMALLINT
	)

INSERT	@Event
SELECT	1001, 123456, 0, '07-27-2007 10:48', 2 UNION ALL
SELECT	1002, 123456, 8, '07-27-2007 10:50', 14 UNION ALL
SELECT	1006, 123456, 4, '07-27-2007 11:04', 6 UNION ALL
SELECT	1010, 123456, 0, '07-27-2007 11:10', 22 UNION ALL
SELECT	1021, 333333, 4, '07-27-2007 11:15', 31 UNION ALL
SELECT	1032, 123456, 2, '07-27-2007 11:32', 10 UNION ALL
SELECT	1109, 123456, 4, '07-27-2007 11:42', 30 UNION ALL
SELECT	1111, 333333, 20, '07-27-2007 11:46', 180 UNION ALL
SELECT	1121, 123456, 20, '07-27-2007 12:12', 15 UNION ALL
SELECT	1133, 123456, 4, '07-27-2007 12:27', 65 UNION ALL
SELECT	1156, 123456, 0, '07-27-2007 13:32', 45 UNION ALL
SELECT	1193, 123456, 20, '07-27-2007 14:17', 40 UNION ALL
SELECT	1200, 123456, 20, '07-27-2007 14:20', 3 UNION ALL
SELECT	1237, 333333, 2, '07-27-2007 14:46', 9 UNION ALL
SELECT	1239, 333333, 20, '07-27-2007 14:54', 6 UNION ALL
SELECT	1243, 333333, 2, '07-27-2007 15:00', 9 UNION ALL
SELECT	1248, 333333, 4, '07-27-2007 15:09', 42

-- Initialize parameters. For example, these parameters can be for a SP or a even an UDF!
DECLARE	@Customer INT,
	@EventTime DATETIME,
	@Flag BINARY(1)

SELECT	@Customer = 333333,
	@EventTime = '07-27-2007 15:51',
	@Flag = 0x4

-- Setup CTE
;WITH Tara (EntryID, Customer, EventTime, Duration)
AS (
	SELECT TOP 1	EntryID,
			Customer,
			EventTime,
			Duration
	FROM		@Event
	WHERE		Flag & @Flag <> 0
			AND Customer = @Customer
			AND EventTime < @EventTime
	ORDER BY	EventTime DESC

	UNION ALL

	SELECT		e.EntryID,
			e.Customer,
			e.EventTime,
			e.Duration
	FROM		@Event AS e
	INNER JOIN	Tara AS t ON t.EventTime = DATEADD(minute, e.Duration, e.EventTime)
				AND t.Customer = e.Customer
	WHERE		e.Flag & @Flag <> 0
			AND e.EventTime < @EventTime
)

-- Show the expected result
SELECT TOP 1	EntryID
FROM		Tara
ORDER BY	EventTime

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 18:03:01

Or this SELECT, if there are more than 100 consecutive records

-- Show the expected result
SELECT TOP 1	EntryID,
		Customer,
		EventTime
		Duration
FROM		Tara
ORDER BY	EventTime
OPTION		(MAXRECURSION 0)

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-30 : 18:20:08

Wow! Now I've got to figure out just exactly how it works before I pass this along to the developer. I'll give full credit to you.

Thank you so much, Peter!

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 18:31:08

The CTE is recursive, that is the anchor part is the latest record that fulfills the parameters.
Then the recursive part is just a JOIN on the EntryTime + Duration that equals the anchor part and the parameters.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 18:37:38

This CTE is a little more slimlined.
Please let me know of performance.

-- Prepare sample data
DECLARE	@Event TABLE
	(
		EntryId INT PRIMARY KEY CLUSTERED,
		Customer INT,
		Flag INT,
		EventTime DATETIME,
		Duration SMALLINT
	)

INSERT	@Event
SELECT	1001, 123456, 0, '07-27-2007 10:48', 2 UNION ALL
SELECT	1002, 123456, 8, '07-27-2007 10:50', 14 UNION ALL
SELECT	1006, 123456, 4, '07-27-2007 11:04', 6 UNION ALL
SELECT	1010, 123456, 0, '07-27-2007 11:10', 22 UNION ALL
SELECT	1021, 333333, 4, '07-27-2007 11:15', 31 UNION ALL
SELECT	1032, 123456, 2, '07-27-2007 11:32', 10 UNION ALL
SELECT	1109, 123456, 4, '07-27-2007 11:42', 30 UNION ALL
SELECT	1111, 333333, 20, '07-27-2007 11:46', 180 UNION ALL
SELECT	1121, 123456, 20, '07-27-2007 12:12', 15 UNION ALL
SELECT	1133, 123456, 4, '07-27-2007 12:27', 65 UNION ALL
SELECT	1156, 123456, 0, '07-27-2007 13:32', 45 UNION ALL
SELECT	1193, 123456, 20, '07-27-2007 14:17', 40 UNION ALL
SELECT	1200, 123456, 20, '07-27-2007 14:20', 3 UNION ALL
SELECT	1237, 333333, 2, '07-27-2007 14:46', 9 UNION ALL
SELECT	1239, 333333, 20, '07-27-2007 14:54', 6 UNION ALL
SELECT	1243, 333333, 2, '07-27-2007 15:00', 9 UNION ALL
SELECT	1248, 333333, 4, '07-27-2007 15:09', 42

-- Initialize parameters. For example, these parameters can be for a SP or a even an UDF!
DECLARE	@Customer INT,
	@Flag BINARY(1),
	@EventTime DATETIME

SELECT	@Customer = 123456,
	@Flag = 0x4,
	@EventTime = '07-27-2007 14:17'

-- Setup CTE
;WITH Tara (EntryID, EventTime)
AS (
	SELECT TOP 1	EntryID,
			EventTime
	FROM		@Event
	WHERE		Customer = @Customer
			AND Flag & @Flag <> 0
			AND EventTime < @EventTime
	ORDER BY	EventTime DESC

	UNION ALL

	SELECT		e.EntryID,
			e.EventTime
	FROM		@Event AS e
	INNER JOIN	Tara AS t ON t.EventTime = DATEADD(minute, e.Duration, e.EventTime)
	WHERE		e.Customer = @Customer
			AND e.Flag & @Flag <> 0
			AND e.EventTime < @EventTime
)

-- Show the expected result
SELECT TOP 1	EntryID
FROM		Tara
ORDER BY	EventTime
OPTION		(MAXRECURSION 0)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 18:55:27

I have rewritten the code as an UDF for you

CREATE FUNCTION dbo.fnGetEntryID
(
	@Customer INT,
	@EventTime DATETIME,
	@Flag BINARY(1)
)
RETURNS INT
AS
BEGIN
	DECLARE	@EntryID INT

	;WITH Tara (EntryID, EventTime)
	AS (
		SELECT TOP 1	EntryID,
				EventTime
		FROM		[Event]
		WHERE		Customer = @Customer
				AND EventTime < @EventTime
				AND Flag & @Flag <> 0
		ORDER BY	EventTime DESC

		UNION ALL

		SELECT		e.EntryID,
				e.EventTime
		FROM		[Event] AS e
		INNER JOIN	Tara AS t ON t.EventTime = DATEADD(minute, e.Duration, e.EventTime)
		WHERE		e.Customer = @Customer
				AND e.EventTime < @EventTime
				AND e.Flag & @Flag <> 0
	)

	SELECT TOP 1	@EntryID = EntryID
	FROM		Tara
	ORDER BY	EventTime
	OPTION		(MAXRECURSION 0)
	
	RETURN	@EntryID
END

And here is how you can test the UDF

-- Prepare sample data
DECLARE	@Params TABLE (Customer INT, EventTime DATETIME, Flag BINARY(1), ExpectedEntryID INT)

INSERT	@Params
SELECT	333333, '07-27-2007 14:46', 0x4, 1021 UNION ALL
SELECT	123456, '07-27-2007 11:10', 0x4, 1006 UNION ALL
SELECT	123456, '07-27-2007 11:04', 0x4, NULL UNION ALL
SELECT	123456, '07-27-2007 14:17', 0x4, 1109 UNION ALL
SELECT	333333, '07-27-2007 15:51', 0x4, 1248

-- Show the expected output
SELECT		p.Customer,
		p.EventTime,
		p.Flag,
		p.ExpectedEntryID,
		dbo.fnGetEntryID(p.Customer, p.EventTime, p.Flag) AS EntryID
FROM		@Params AS p
ORDER BY	p.Customer,
		p.EventTime

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 19:02:31

Result from test code above

Customer  EventTime                Flag  ExpectedEntryID  EntryID
--------  -----------------------  ----  ---------------  -------
123456    2007-07-27 11:04:00.000  0x04  NULL             NULL
123456    2007-07-27 11:10:00.000  0x04  1006             1006
123456    2007-07-27 14:17:00.000  0x04  1109             1109
333333    2007-07-27 14:46:00.000  0x04  1021             1021
333333    2007-07-27 15:51:00.000  0x04  1248             1248

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 19:15:15

I have noticed one flaw now. And that is circular reference!
There is a way to make the recursion go berserk, and that is if some duration is negative so that we go forth in time again, or a duration is equal to zero.

This can be solved in two ways

1) Make a constraint on the table Duration > 0
2) Add a WHERE clause to the recursive part in the CTE definition Duration > 0

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 20:12:11

There is more than one way to skin this cat. Here is a way where you avoid circular reference and avoid filling the stack with recursive calls!

CREATE FUNCTION dbo.fnGetEntryID2
(
	@Customer INT,
	@EventTime DATETIME,
	@Flag BINARY(1)
)
RETURNS INT
AS

BEGIN
	DECLARE	@EntryID INT

	;WITH Tara (EntryID, EventTime, Duration, RecID)
	AS (
		SELECT	EntryID,
			EventTime,
			Duration,
			ROW_NUMBER() OVER (ORDER BY EventTime DESC)
		FROM	[Event]
		WHERE	Customer = @Customer
			AND EventTime < @EventTime
			AND Flag & @Flag <> 0
	)
	,Peso (EntryID)
	AS (
		SELECT TOP 1	t1.EntryID
		FROM		Tara AS t1
		LEFT JOIN	Tara AS t2 ON t2.RecID = t1.RecID + 1
					AND DATEADD(minute, t2.Duration, t2.EventTime) = t1.EventTime
		WHERE		t2.EntryID IS NULL
		ORDER BY	t1.RecID
	)

	SELECT	@EntryID = EntryID
	FROM	Peso

	RETURN @EntryID
END

To test the functions

-- Prepare sample data
DECLARE	@Params TABLE (Customer INT, EventTime DATETIME, Flag BINARY(1), ExpectedEntryID INT)

INSERT	@Params
SELECT	333333, '07-27-2007 14:46', 0x4, 1021 UNION ALL
SELECT	123456, '07-27-2007 11:10', 0x4, 1006 UNION ALL
SELECT	123456, '07-27-2007 11:04', 0x4, NULL UNION ALL
SELECT	123456, '07-27-2007 14:17', 0x4, 1109 UNION ALL
SELECT	333333, '07-27-2007 15:51', 0x4, 1248

-- Show the expected output
SELECT		p.Customer,
		p.EventTime,
		p.Flag,
		p.ExpectedEntryID,
		dbo.fnGetEntryID(p.Customer, p.EventTime, p.Flag) AS EntryID,
		dbo.fnGetEntryID2(p.Customer, p.EventTime, p.Flag) AS EntryID2
FROM		@Params AS p
ORDER BY	p.Customer,
		p.EventTime

And the output

Customer  EventTime              Flag  ExpectedEntryID  EntryID  EntryID2
--------  ---------------------  ----  ---------------  -------  --------
123456	2007-07-27 11:04:00.000  0x04  NULL             NULL     NULL
123456	2007-07-27 11:10:00.000  0x04  1006             1006     1006
123456	2007-07-27 14:17:00.000  0x04  1109             1109     1109
333333	2007-07-27 14:46:00.000  0x04  1021             1021     1021
333333	2007-07-27 15:51:00.000  0x04  1248             1248     1248

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-30 : 20:56:38

I'll pass this information along to the developer. I don't understand the business logic enough to know if Duration can ever equal 0. I believe it would be a bug though. Duration must also be non-negative.

Thanks, Peter!

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 02:06:20

Thanks.

You have to do some testing with your data to see which method that is more efficient.
The first one is elegant and works much like EXISTS. It fetches only the record it needs and keeps going until business rules are not met.
The second one is more brutal and fetches all records according to parameters.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources