How should I write my WHERE clause in SQL Server for the following case?

Question

I have a SQL table with two columns Date and User and contains the following rows:

    **Date        User**
    2009-02-20  Danny
    2009-02-20  Matthew
    2009-02-15  William
    2009-02-15  Abner
    2009-12-14  Joseph
    1900-01-01  Adam
    1900-01-01  Eve

Given a date, how should I write my WHERE clause to return a list of users for that date, or if no users users were found for that date, return a list of users for the next earlier date. For example, if the given date is '2009-02-19', the users list returned should be William and Abner.

Answer 1

SELECT * 
FROM [MyTable] t
INNER JOIN
   ( 
      SELECT TOP 1 [Date]
      FROM [MyTable]
      WHERE [Date] >= @TargetDate
      ORDER BY [Date]
   ) d ON t.[Date] = d.[Date]

Answer 2

SELECT User
FROM MyTable
WHERE MyDate = (SELECT MAX(MyDate) FROM MyTable WHERE MyDate <= '2009-02-19')

Answer 3

SELECT [User] FROM myTable WHERE Date =
  (SELECT Max(Date) FROM myTable WHERE Date <= '2009-02-19')

Answer 4

Something like this:

select Name
from UnknownTable
where [Date] = 
    (select Max([Date]) 
     from UnknownTable
     where [Date] <= '2009-02-19')

This assumes there are no time components to your date values.

Answer 5

You could do something like this

SELECT TOP 2 [Date] ,[User] FROM [LearningDatabase].[dbo].[Users] WHERE [Date] = '07/15/2009' OR [Date] < '07/15/2009'