SQL Server Consecutive Days query w/ only Start and End dates

Our hotel booking table uses only StartDate and EndDate fields. Days between the two dates are booked.

My challenge is to find gaps of X consecutive days in a two-month period. In that timespan, there may be as few as 0 records, and as many as 10.  

I do not have to identify all gaps X days long. One or none is sufficient, so long as one or none is true.

I've not been able to come up with a solution that executes in less than three minutes. Execute time has to be below 2 seconds.

We're using MS SQL Server 2000.

LVL 1
dwhite365Asked:
Who is Participating?
 
ZberteocCommented:
Sorry, use this:

CREATE TABLE #period
(
      id int identity,
      room int,
      startdate datetime,
      enddate datetime
)

DECLARE
      @startperiod AS datetime,
      @endperiod datetime,
      @countgaps int,
      @id int,
      @rows int

SELECT
      @startperiod =dateadd(m,-2,getdate()),
      @endperiod =getdate(),
      @countgaps=0,
      @id=2


INSERT INTO #period
(
      room,
      startdate,
      enddate
)
SELECT
      room,
      startdate,
      enddate
FROM
      RoomsOcupancy
WHERE
      startdate >= @startperiod
      AND enddate<=@endperiod
ORDER BY
      room,
      startdate
SELECT
      @rows=@@rowcount

WHILE @id<=@rows
      SELECT
            @countgaps=@countgaps+
            datediff(day,
                         ( SELECT enddate FROM #period WHERE id=@id-1),  
                         ref.startdate),
            @id=@id+1
      FROM
            #period ref
      WHERE
            ref.id=@id

SELECT
      @countgaps AS gaps

DROP TABLE #period
0
 
UnifiedISCommented:
DATEDIFF(d, StartDate, EndDate) - Distinct count of booked days would tell you if there was a gap within the two dates.  Is that what you need?
0
 
ZberteocCommented:
Assuming that you have a table like:

RoomsOcupancy
      room int,
      startdate datetime,
      enddate datetime

then use this:


CREATE TABLE #period
(
      id int identity,
      room int,
      startdate datetime,
      enddate datetime
)

DECLARE
      @startperiod AS datetime,
      @endperiod datetime,
      @countgaps int,
      @id int,
      @rows int

SELECT
      @startperiod =dateadd(m,-2,getdate()),
      @endperiod =getdate(),
      @countgaps=0,
      @id=2


INSERT INTO #period
(
      room,
      startdate,
      enddate
)
SELECT
      room,
      startdate,
      enddate
FROM
      RoomsOcupancy
WHERE
      startdate >= @startperiod
      AND enddate<=@endperiod
ORDER BY
      start_date
SELECT
      @rows=@@rowcount

WHILE @id<=@rows
      SELECT
            @countgaps=@countgaps+
            datediff(day,
                         ( SELECT enddate FROM #period WHERE id=@id-1),  
                         ref.startdate),
            @id=@id+1
      FROM
            #period ref
      WHERE
            ref.id=@id

SELECT
      @countgaps AS gaps

DROP TABLE #period
0
 
Scott PletcherSenior DBACommented:
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @numberOfDaysNeeded INT

SET @StartDate = '20071001'  --<<-- adjust date as required
SET @EndDate = '20071031'  --<<-- adjust date as req'd
SET @numberOfDaysNeeded = 5  --<<-- adjust # days as req'd

if object_id('tempdb.dbo.#hotelBooking') is not null
    drop table #hotelBooking
create table #hotelBooking (
    StartDate DATETIME,
    EndDate DATETIME
    )
set nocount on
insert into #hotelBooking values('20071001', '20071004')
insert into #hotelBooking values('20071009', '20071022')
insert into #hotelBooking values('20071023', '20071026')  
insert into #hotelBooking values('20071201', '20071204')
insert into #hotelBooking values('20071209', '20071222')
insert into #hotelBooking values('20071223', '20071227')  

set nocount off

-- should return 'Yes' because 5 days are avail. (Oct 27-31)
SELECT CASE WHEN @numberOfDaysNeeded > ALL
(SELECT ISNULL(DATEDIFF(DAY, EndDate, ISNULL((
    SELECT TOP 1 StartDate
    FROM #hotelBooking hb2
    WHERE hb2.StartDate > hb1.EndDate
    ORDER BY StartDate
    ), DATEADD(DAY, 1, @EndDate)) - 1), 0)
FROM #hotelBooking hb1
WHERE StartDate >= @StartDate AND EndDate <= @EndDate)
THEN 'No' ELSE 'Yes' END


SET @StartDate = '20071201'  --<<-- adjust date as required
SET @EndDate = '20071231'  --<<-- adjust date as req'd
SET @numberOfDaysNeeded = 5  --<<-- adjust # days as req'd

-- should return 'No' because 5 days are not avail. (Dec 27 is in use)
SELECT CASE WHEN @numberOfDaysNeeded > ALL
(SELECT ISNULL(DATEDIFF(DAY, EndDate, ISNULL((
    SELECT TOP 1 StartDate
    FROM #hotelBooking hb2
    WHERE hb2.StartDate > hb1.EndDate
    ORDER BY StartDate
    ), DATEADD(DAY, 1, @EndDate)) - 1), 0)
FROM #hotelBooking hb1
WHERE StartDate >= @StartDate AND EndDate <= @EndDate)
THEN 'No' ELSE 'Yes' END
0
 
Scott PletcherSenior DBACommented:
It would be more efficient with EXISTS: if you want, I'll work on that when I get time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.