I can easily get a random record with this:
SELECT * FROM MyTable ORDER BY NewId()
I can easily get a record with "today's date" with this:
SELECT * FROM MyTable WHERE MyDate = "2010-24-08" -- db doesn't store times
But how would I combind the two?
Get 1 random record... anything with today's date.
If none are found... get 1 random record from yesterday (today-1).
If none are found... get 1 random record from etc, etc, today-2
... until 1 record is found.
Just make the day date the primary order by condition:
select top(1) *
from Table
order by Date desc, newid();
If you store the dates as full day and time, you need to round them out to the day part only: cast (Date as DATE) in SQL 2008 or cast(floor(cast(Date as FLOAT)) as DATETIME) in pre-2008.
Use the TOP operator:
SELECT TOP 1 *
FROM MyTable
WHERE MyDate = "2010-24-08"
ORDER BY NEWID()
...combined with the ORDER BY NEWID(). Without the ORDER BY, you'd get the first inserted row/record of the records returned by the filteration in most cases typically, but the only way to ensure order is with an ORDER BY clause.
SQL Server 2005+ supports brackets on the TOP value, so you can use a variable in the brackets without needing to use dynamic SQL.
Does this give you what you want?
SELECT TOP 1 *
FROM MyTable
ORDER BY MyDate desc, NewId()
This assumes there are no dates later than today.
