Select first record with date closest

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)

Select first record with date closest

Author

Topic

phillikc
Starting Member

6 Posts

Posted - 2011-08-14 : 11:51:39

Given a set of records like below, I'm looking for a query that would return the first ActionDate nearest, and just previous to, any given date. For example, I enter 8/2/2009 and it returns the first record it finds just previous to that date (7/20/2009). I'm kind of a beginner, and I have had trouble implementing other solutions I've found.

RecID UserID UserStatus ActionDate
712 rinaudofr EN 7/1/2009 10:30:35 PM
713 rinaudofr EN 7/2/2009 10:30:42 PM
714 rinaudofr EN 7/3/2009 10:30:51 PM
715 rinaudofr EN 7/4/2009 10:30:17 PM
716 rinaudofr EN 7/5/2009 10:31:16 PM
717 rinaudofr EN 7/6/2009 10:31:08 PM
718 rinaudofr EN 7/7/2009 10:30:41 PM
719 rinaudofr EN 7/8/2009 10:30:53 PM
720 rinaudofr EN 7/9/2009 10:31:06 PM
721 rinaudofr EN 7/10/2009 10:31:38 PM
722 rinaudofr EN 7/11/2009 10:30:20 PM
723 rinaudofr EN 7/12/2009 10:36:14 PM
724 rinaudofr EN 7/13/2009 10:30:22 PM
725 rinaudofr EN 7/14/2009 10:30:54 PM
726 rinaudofr EN 7/15/2009 10:30:47 PM
727 rinaudofr EN 7/16/2009 10:31:16 PM
728 rinaudofr EN 7/17/2009 10:30:31 PM
729 rinaudofr EN 7/18/2009 10:30:25 PM
730 rinaudofr EN 7/19/2009 10:30:20 PM
731 rinaudofr EN 7/20/2009 10:30:41 PM
732 rinaudofr EN 9/23/2009 2:04:12 PM
733 rinaudofr EN 9/23/2009 4:28:10 PM
734 rinaudofr EN 9/25/2009 4:37:46 PM
735 rinaudofr EN 9/25/2009 4:38:28 PM

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-14 : 14:24:56

This will get you the date, however, if you need the record correlated to the date, there are a few more steps that you'd have to go. Here's a start:

declare @inputdate date
set @inputdate='2009-08-02'
declare @test table (recid int, userid varchar(30), userstatus char(2), actiondate datetime)
insert into @test (recid, userid, userstatus, actiondate)
values
(712, 'rinaudofr', 'EN', '7/1/2009 10:30:35 PM'),
(713, 'rinaudofr', 'EN', '7/2/2009 10:30:42 PM'),
(714, 'rinaudofr', 'EN', '7/3/2009 10:30:51 PM'),
(715, 'rinaudofr', 'EN', '7/4/2009 10:30:17 PM'),
(716, 'rinaudofr', 'EN', '7/5/2009 10:31:16 PM'),
(717, 'rinaudofr', 'EN', '7/6/2009 10:31:08 PM'),
(718, 'rinaudofr', 'EN', '7/7/2009 10:30:41 PM'),
(719, 'rinaudofr', 'EN', '7/8/2009 10:30:53 PM'),
(720, 'rinaudofr', 'EN', '7/9/2009 10:31:06 PM'),
(721, 'rinaudofr', 'EN', '7/10/2009 10:31:38 PM'),
(722, 'rinaudofr', 'EN', '7/11/2009 10:30:20 PM'),
(723, 'rinaudofr', 'EN', '7/12/2009 10:36:14 PM'),
(724, 'rinaudofr', 'EN', '7/13/2009 10:30:22 PM'),
(725, 'rinaudofr', 'EN', '7/14/2009 10:30:54 PM'),
(726, 'rinaudofr', 'EN', '7/15/2009 10:30:47 PM'),
(727, 'rinaudofr', 'EN', '7/16/2009 10:31:16 PM'),
(728, 'rinaudofr', 'EN', '7/17/2009 10:30:31 PM'),
(729, 'rinaudofr', 'EN', '7/18/2009 10:30:25 PM'),
(730, 'rinaudofr', 'EN', '7/19/2009 10:30:20 PM'),
(731, 'rinaudofr', 'EN', '7/20/2009 10:30:41 PM'),
(732, 'rinaudofr', 'EN', '9/23/2009 2:04:12 PM'),
(733, 'rinaudofr', 'EN', '9/23/2009 4:28:10 PM'),
(734, 'rinaudofr', 'EN', '9/25/2009 4:37:46 PM'),
(735, 'rinaudofr', 'EN', '9/25/2009 4:38:28 PM')

select MAX(actiondate)
from (
select actiondate
from @test
where @inputdate>actiondate) a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources