CODEWITHSUNDEEP
sqlsql-server
Bric
Bric

Reputation: 5

Find Historical Status in list of Start Dated only records

I have an SQL table which lists status changes for an item

StatusHistory

RecordID  |  StartDate   |  Status
-----------------------------------
1         |  1983-01-01  |  A
2         |  2008-03-18  |  B
3         |  2009-11-10  |  C
4         |  2015-01-12  |  D

As you can see the records are only start dated and have no effective end date.

If given an input date, using SQL how can I return the status that was current at that particular date ?

Eg.

for 2008-03-17 the status would be A or for 2012-10-20 the status would be C

I'm sure this is a simple script, but would appreciate any help!

cheers

Upvotes: 0

Views: 32

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use TOP:

SELECT TOP 1 Status
FROM records
WHERE
    StartDate <= @inputDate
ORDER BY StartDate DESC

Upvotes: 2

Related Questions