Calculate date range in select

Question

I'm designing a website to accept ads for my website. My website will get the list of valid ads (ads that verified and they have credit left) and will show them. My ads table has these field that validates an ad:

• TotalDaysToShow int 'total number of days that ad will be shown
• StartDate datetime
• verified bit
• LastShowed datetime

I want to get for example 4 ads to show - using a query similar to this:

SELECT TOP 4 ImageId,
             Url
FROM   ads
WHERE  verified = 1 AND .......... 
ORDER  BY LastShowed ASC

The problem here is that if the TotalDaysToShow was the same for all ads I could simply use:

declare @d= dateadd(day, @TotalDaysToShow, starttime) and then use it like:

SELECT TOP 4 ImageId,
             Url
FROM   ads
WHERE  verified = 1
       AND @d > Getdate()
ORDER  BY LastShowed ASC

Is it possible to calculate it for each of them?

SELECT TOP 4 ImageId,
             Url
FROM   ads
WHERE  verified = 1
       AND Dateadd(day, TotalDaysToShow, startdate) > Getdate()
ORDER  BY LastShowed ASC

Thanks in advance

Answer 1

Yes, but I believe you will find this SQL clause more efficient

AND startdate BETWEEN Dateadd(day, -TotalDaysToShow, getdate()) AND getdate() 

because it compares the table field to two constants.