Link to home
Start Free TrialLog in
Avatar of dwhite365
dwhite365

asked on

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.

Avatar of UnifiedIS
UnifiedIS

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?
Avatar of Zberteoc
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
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It would be more efficient with EXISTS: if you want, I'll work on that when I get time.