SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Consecutive rows help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 07/30/2007 :  13:06:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 07/30/2007 13:06:53

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 07/30/2007 :  14:34:47  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 07/30/2007 :  14:48:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 07/30/2007 14:57:57
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/30/2007 :  17:55:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 07/30/2007 18:18:00
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/30/2007 :  18:03:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 07/30/2007 :  18:20:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/30/2007 :  18:31:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/30/2007 :  18:37:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 07/30/2007 18:41:38
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/30/2007 :  18:55:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 07/30/2007 18:59:39
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/30/2007 :  19:02:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/30/2007 :  19:15:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 07/30/2007 19:25:41
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/30/2007 :  20:12:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 07/30/2007 :  20:56:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/31/2007 :  02:06:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000