SQL Checking for NULL and incrementals

Question

I'd like to check if there is anything to return given a number to check against, and if that query returns no entries, increase the number until an entry is reached and display that entry. Currently, the code looks like this :

        SELECT * 
 FROM news
 WHERE DATEDIFF(day, date, getdate() ) <= #url.d#
 ORDER BY date desc

where #url.d# is an integer being passed through (say 31). If that returns no results, I'd like to increase the number stored in #url.d# by 1 until an entry is found.

Answer 1

This kind of incremental querying is just not efficient. You'll get better results by saying - "I'll never need more than 100 results so give me these" :

SELECT top 100 *
FROM news
ORDER BY date desc

Then filtering further on the client side if you want only a particular day's items (such as the items with a common date as the first item in the result).

Or, you could transform your multiple query request into a two query request:

DECLARE
  @theDate datetime,
  @theDate2 datetime

SET @theDate = (SELECT Max(date) FROM news)
  --trim the time off of @theDate
SET @theDate = DateAdd(dd, DateDiff(dd, 0, @theDate), 0)
SET @theDate2 = DateAdd(dd, 1, @theDate)

SELECT *
FROM news
WHERE @theDate <= date AND date < @theDate2
ORDER BY date desc

Answer 2

In MySQL:

SELECT  news.*,
        (
        SELECT  COUNT(*)
        FROM    news
        WHERE   date < DATEADD(day, GETDATE(), -#url.d#)
        )
FROM    news
WHERE   date >= DATEADD(day, GETDATE(), -#url.d#)
ORDER BY
        date DESC
LIMIT 1

In SQL Server:

SELECT  TOP 1
        news.*,
        (
        SELECT  COUNT(*)
        FROM    news
        WHERE   date < DATEADD(day, GETDATE(), -#url.d#)
        )
FROM    news
WHERE   date >= DATEADD(day, GETDATE(), -#url.d#)
ORDER BY
        date DESC

Note that using this syntax makes your query sargable, that is an index can be used to filter on date efficiently.

Answer 3

First, I think you will probably want to avpod using the DateDiff function in your where clause, instead, compute the desired cutoff date and do use any computations on the date column within the where clause, this will be more efficient, so rather than

WHERE DATEDIFF(day, date, getdate() ) <= #url.d#

you would have something like

WHERE date >= @cutoffDate

where @cutoffDate is a computed date based on #url.d#

Now, as for grabbing the correct cutoff date. My assumption is that under normal circumstances, there will be articles returned from the request otherwise you would just grab articles from the most recent date. So, the approach that I would take would be to grab the OLDEST of the computed cutoff date (based on #url.d# and the MOST RECENT article date. Something like

-- @urld == #url.d
-- compute the cutoff date as the OLDEST of the most recent article and
-- the date based on #url.d
declare @cutoff datetime
select @cutoff =  DateAdd(dd,-1*@urld,GetDate())
select @cutoff


select @cutoff = min(cutoffDate)
from 
(SELECT Max(date) as cutoffDate from News
UNION
select @cutoff) Cutoff


-- grab the articles with dates that are more recent than the cutoff date
select *
from News
WHERE date >= @cutoff

I'm also guessing that you would probably want to round to midnight for the dates (which I didn't do here). This is a multi-query approach and should probably be implemented in a single stored procedure ... if this is what you are looking for.

Good luck with the project!

Answer 4

If you wanted the one row:

SELECT t.*
  FROM NEWS t
 WHERE t.id = (SELECT MAX(n.id)
                 FROM NEWS n
                WHERE n.date BETWEEN DATEADD(day, -:url.d, getDate()) AND getDate())

It might not be obvious that the DATEADD is using a negative in order to go back however many number of days desired.

If you wanted all the rows in that date:

SELECT t.*
  FROM NEWS t
 WHERE t.date BETWEEN DATEADD(day, -:url.d, getDate()) AND getDate())