CODEWITHSUNDEEP
sqlsql-server
dawntrader
dawntrader

Reputation: 785

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

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.

Upvotes: 3

Views: 3058

Answers (5)

Michael Ciba
Michael Ciba

Reputation: 561

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'

Upvotes: -1

JeremyDWill
JeremyDWill

Reputation: 3132

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.

Upvotes: 0

AnthonyWJones
AnthonyWJones

Reputation: 189525

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

Upvotes: 5

cletus
cletus

Reputation: 625347

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

Upvotes: 6

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

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

Upvotes: 0

Related Questions