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.
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.
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?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It would be more efficient with EXISTS: if you want, I'll work on that when I get time.