1

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...
Mark Clancy
  • 7,831
  • 8
  • 43
  • 49
  • 1
    This is an exact duplicate of a question asked here many times. See here: http://stackoverflow.com/questions/271595/getting-dates-between-a-range-of-dates – David Mar 10 '10 at 21:57
  • Thanks, I obviously wasn't searching for the right thing. – Mark Clancy Mar 10 '10 at 22:00
  • I actually think it is different. You don't need to find out all the dates to answer this query. – D'Arcy Rittich Mar 10 '10 at 22:02
  • I think you're right. it's not a duplicate. Why can't I reverse my vote to close? – David Mar 10 '10 at 22:04
  • Ah yes I thought that answer would help but it might not be exactly what I'm looking for. I'm using SQL 2008. I just need to check if there's a gap between the given date range in the Prices table. – Mark Clancy Mar 10 '10 at 22:07
  • Then this could be answered by this other stackoverflow question: http://stackoverflow.com/questions/1025688/calculate-missing-date-ranges-and-overlapping-date-ranges-between-two-dates – Jose Chama Mar 10 '10 at 22:52

1 Answers1

2

Add a grouping to your query with a having statement :

HAVING COUNT(*) = DATEDIFF(DAY,@StartDate,@EndDae)+1
Gary W
  • 1,874
  • 1
  • 14
  • 18