CODEWITHSUNDEEP
sqldatetimesql-server-2012
Andy
Andy

Reputation: 383

Need SQL Server SELECT To Return Previous Record Based On A DateTime

I need to create a SQL Server 2012 SELECT statement that will give me the previous row in a table. I need to specify the current datetime and have the query return the closest previous datetime stored in the [TargetDate] column.

So in the table I may have...

[TargetUnit]    [TargetDate]
-----------------------------------
4               2015-12-09 10:15:00 
5               2015-12-09 10:30:00
8               2015-12-09 10:45:00
15              2015-12-09 11:00:00
22              2015-12-09 11:15:00

and I use the GETDATE() to query the [TargetDate] field. Say the current GETDATE() is 2015-12-09 10:37:00 - then I need the query to return the row with the [TargetUnit] of 5 and the [TargetDate] of 2015-12-09 10:30:00.

Upvotes: 1

Views: 3426

Answers (2)

WBratz
WBratz

Reputation: 146

Can do it using datediff and a while loop, I should say this is the best option I could think of if you're dealing with a table that could have FUTURE datetimes in it. This till find the closest value, future or past, to the specified date.

DECLARE @targetdate datetime
DECLARE @getdate datetime = GETDATE()
DECLARE @targetunit int

CREATE TABLE #diffs (targetunit int, targetdate datetime, diffday, diffsec int)

SELECT * INTO #table FROM YOUR_TABLE
WHILE (SELECT COUNT(*) FROM #TABLE) > 0
BEGIN
SELECT TOP 1 @targetunit = targetunit, @targetdate = targetdate FROM #table
DELETE FROM #table WHERE targetunit = @targetunit and targetdate = @targetdate

INSERT INTO #diff
SELECT @targetunit, @targetdate, datediff(day, @targetdate, @getdate),         Datediff(second, @targetdate, @getdate)
END

SELECT targetunit, targetdate, MIN(diffday) diffday, MIN(diffsec) diffsec
INTO #mins
FROM #diff 

SELECT *
FROM #diff a
JOIN #mins b on a.targetunit = b.targetunit
WHERE a.diffday = b.diffday and a.diffsec = b.diffsec

Upvotes: 0

sstan
sstan

Reputation: 36483

You can use top and order by to get the desired record.

select top 1 [TargetUnit], [TargetDate]
  from tbl
 where [TargetDate] < GETDATE()
 order by [TargetDate] desc

Upvotes: 3

Related Questions