Select 2 Closest Dates in a group

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 2008 Forums

Transact-SQL (2008)

Select 2 Closest Dates in a group

Author

Topic

tomrippity
Starting Member

37 Posts

Posted - 2014-03-19 : 13:43:18

I have a set of data that draws comparisons between test scores of 2 different standardized tests.

1 test can be taken as often as once per month, and the other is taken every year.

What I need to know is how to select the test scores for each test with the two closest test dates in a given year.

Sample Data:

TEST_1 TEST_2 GROUP
7/1/2011 12/1/2011 2011
9/1/2011 12/1/2011 2011
10/1/2011 12/1/2011 2011
5/1/2012 12/1/2012 2012
9/1/2012 12/1/2012 2012
11/1/2012 12/1/2012 2012

Expected Return:

TEST_1 TEST_2 GROUP
10/1/2011 12/1/2011 2011
11/1/2012 12/1/2012 2012

There are other fields in the table as well that have no common values, such as the test scores for test_1 and test_2.

Thanks in advance!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-19 : 16:30:46

[code]DECLARE @Foo TABLE (TEST_1 DATE, TEST_2 DATE, [GROUP] INT)

INSERT @Foo VALUES
('7/1/2011', '12/1/2011', 2011),
('9/1/2011', '12/1/2011', 2011),
('10/1/2011', '12/1/2011', 2011),
('5/1/2012', '12/1/2012', 2012),
('9/1/2012', '12/1/2012', 2012),
('11/1/2012', '12/1/2012', 2012)

SELECT
*
FROM
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY [Group] ORDER BY DATEDIFF(DAY, TEST_1, TEST_2) ASC) AS RowNum
FROM
@Foo
) AS T
WHERE
RowNum = 1[/code]

tomrippity
Starting Member

37 Posts

Posted - 2014-03-20 : 10:10:53

Perfecto! Thank you very much!

quote:

Originally posted by Lamprey

DECLARE @Foo TABLE (TEST_1 DATE, TEST_2 DATE, [GROUP] INT)

INSERT @Foo VALUES
('7/1/2011', '12/1/2011', 2011),
('9/1/2011', '12/1/2011', 2011),
('10/1/2011', '12/1/2011', 2011),
('5/1/2012', '12/1/2012', 2012),
('9/1/2012', '12/1/2012', 2012),
('11/1/2012', '12/1/2012', 2012)

SELECT
	*
FROM
	(
		SELECT
			*
			,ROW_NUMBER() OVER (PARTITION BY [Group] ORDER BY DATEDIFF(DAY, TEST_1, TEST_2) ASC) AS RowNum
		FROM
			@Foo
	) AS T
WHERE
	RowNum = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources