Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table of 2 columns (Date, Text). I am getting data from the Text column according to the Date using a query like this:

SELECT Text
FROM MyTable
WHERE Date>= @userDate;

This works, except when @userDate is bigger than the most recent Date in MyTable. In this case it returns nothing but I need it to return the Text for the most recent Date.

I have to change my query to use the smaller one of @userDate and MAX(Date). Any ideas how to do it?

share|improve this question
    
What database engine are you using? –  mrdenny Oct 26 '12 at 18:06
    
sorry I forgot to write it. It's sql server 2008 R2 –  david Oct 26 '12 at 18:11
add comment

2 Answers 2

up vote 0 down vote accepted
DECLARE @tablename varchar(100)='site'
DECLARE @table1 table(date1 date)
INSERT INTO @table1
EXEC ('SELECT MAX(insdt) AS date  FROM '+@tablename+'')
DECLARE @yourvariable date
SELECT @yourvariable=date1 FROM @table1

DECLARE @userDate date

IF @userDate>@yourvariable
BEGIN
   SELECT  top 1 text FROM MyTable ORDER BY [date] DESC
END
ELSE 
BEGIN
   SELECT Text FROM MyTable WHERE Date>= @userDate
END
share|improve this answer
    
And is it possible to use this syntax even if the SELECT MAX(Date) FROM MyTable is in variable, because this query is built according to some other parameters –  david Oct 26 '12 at 18:20
    
ya it can like IF @userDate>your variable –  kumar_2002 Oct 26 '12 at 18:21
    
Sorry I have asked bad question I mean what to do if I have somthing like this set @q ='SELECT MAX(Date) FROM MyTable' because I have to build the query as a string because the name of MyTable changes according to some paramters. So how could I get the Date when I have the string of query? –  david Oct 26 '12 at 18:25
    
Hi edited according to your request, if you want to change table name you have to use dynamic T-sql . –  kumar_2002 Oct 26 '12 at 18:49
add comment
   SELECT CASE WHEN T.Date IS NULL THEN M.TEXT ELSE T.TEXT END TEXT
     FROM (SELECT TOP(1) *
              FROM MyTable
          ORDER BY Date DESC) M
LEFT JOIN MyTable T ON T.Date>= @userDate11

This is one of several ways to solve the problem. The TOP(1) and ORDER BY Date DESC gives you the fallback record. The original table aliased T is consigned as the optional partner in the LEFT JOIN. This allows it to return no records, while still retaining the singular record from the derived table aliased M. The CASE statement then tests to see if we actually managed to get anything from T using WHEN T.Date IS NULL - if T is not empty, all the TEXT values will come from T, otherwise we will have just the single TEXT value from M, being the one for the MAX(Date).

share|improve this answer
    
it is usually helpful to add a description about your answer. –  Max Vernon Oct 26 '12 at 23:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.