[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

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

Posted on 2007-10-16
Medium Priority
927 Views
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.

0
Question by:dwhite365
• 2
• 2

LVL 18

Expert Comment

ID: 20088771
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

LVL 27

Expert Comment

ID: 20089113
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
@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

LVL 27

Accepted Solution

Zberteoc earned 100 total points
ID: 20089131
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
@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

LVL 70

Assisted Solution

Scott Pletcher earned 100 total points
ID: 20094605
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

LVL 70

Expert Comment

ID: 20094667
It would be more efficient with EXISTS: if you want, I'll work on that when I get time.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…