SQL Query to check ALL days exist between a date range

Question

Hi, I have a table of Prices with a start date, end date and price. I want a search to pass in a date range and return whether a price exists for all days in that range. The date range can span multiple prices, just not have any gaps in between.

Is this possible?

Prices
startDate datetime
endDate datetime
price

DECLARE @startDate datetime = '2010-04-01',
        @endDate datetime = '2010-04-30'

SELECT * FROM Prices WHERE @startDate BETWEEN startDate AND endDate...

Answer 1

Add a grouping to your query with a having statement :

HAVING COUNT(*) = DATEDIFF(DAY,@StartDate,@EndDae)+1

Answer 2

If you have an Auxilliary dates table you can use any of these Relational Division techniques http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

Answer 3

I used this in the end, works perfectly:

http://stackoverflow.com/questions/271595/getting-dates-between-a-range-of-dates