Two Prior Rows and Two After Rows

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)

Two Prior Rows and Two After Rows

Author

Topic

spohnsoftware
Starting Member

3 Posts

Posted - 2009-05-18 : 10:11:40

Basic Table Layout:
Column 1: ID (Identity(1,1))
Column 2: Name (Char(30))
Column 3: Date (Date)

Example Rows:
1 A 5/1/2009
2 B 5/1/2009
3 C 5/2/2009
4 D 5/2/2009
5 E 5/2/2009
6 C 5/3/2009
7 B 5/3/2009
8 D 5/3/2009
9 E 5/4/2009
10 A 5/5/2009

So, here is what I need. I want to, based on Column 2, select the two rows prior and the two rows after, when the date is greater than 4/30/2009. Example: I want to use E as my starting point, so I want two queries:
1.) One query returns prior 2 rows, i.e. Rows 3,4 and 7,8
2.) One query returns succeeding 2 rows, i.e. 6,7 and 10,11

I will also want to adjust the "2 rows" to someday may be "5 rows". I can surely write a program to do it, but I think the overhead would be tremendous. Can this be done in two stored procedures?

Thx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-18 : 10:15:30

quote:
1.) One query returns prior 2 rows, i.e. Rows 3,4 and 7,8

you says one query return 2 rows but you examples says Row 3,4 and 7,8 ? that's 4 rows

quote:
I will also want to adjust the "2 rows" to someday may be "5 rows"

don't quite understand this. Example ? How will the end result looks like ?

KH
[spoiler]Time is always against us[/spoiler]

spohnsoftware
Starting Member

3 Posts

Posted - 2009-05-18 : 11:38:50

The query will return more than two rows, but they will be in couples. Obvisouly, many more than 2 can be returned, but it would be even numbers. When I say two rows, I mean the prior two rows for each instance that meets the where clause.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-18 : 12:50:11

I'm not entirely sure, this gets you what you want. But it does return the rows you want and should be asily expanedable to 5:

DECLARE @Table TABLE (ID INT, Name CHAR(30), Date DATETIME)
INSERT @Table
SELECT 1, 'A', '5/1/2009'
UNION ALL SELECT 2, 'B', '5/1/2009'
UNION ALL SELECT 3, 'C', '5/2/2009'
UNION ALL SELECT 4, 'D', '5/2/2009'
UNION ALL SELECT 5, 'E', '5/2/2009'
UNION ALL SELECT 6, 'C', '5/3/2009'
UNION ALL SELECT 7, 'B', '5/3/2009'
UNION ALL SELECT 8, 'D', '5/3/2009'
UNION ALL SELECT 9, 'E', '5/4/2009'
UNION ALL SELECT 10, 'A', '5/5/2009'


SELECT *
FROM
	(
		SELECT
			*,
			ROW_NUMBER() OVER (ORDER BY ID ASC) RowNum
		FROM 
			@Table
		WHERE
			Date > '4/30/2009'
	) AS A
INNER JOIN
	(	
	
		SELECT
			*,
			ROW_NUMBER() OVER (ORDER BY ID ASC) RowNum
		FROM 
			@Table
		WHERE
			Date > '4/30/2009'

	) AS B
	ON B.Name = 'E'
	AND 
	(
		B.RowNum - 2 = A.RowNum
		OR B.RowNum - 1 = A.RowNum
		OR B.RowNum + 1 = A.RowNum
		OR B.RowNum + 2 = A.RowNum
	)

spohnsoftware
Starting Member

3 Posts

Posted - 2009-05-18 : 15:58:54

Holy Crap!!!! Awesome. It's chugging, as me real world example is over 48 million rows of data, but it is working!!! Thx!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources