[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
7
Medium Priority
?
927 Views
Last Modified: 2012-08-14
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
Comment
Question by:dwhite365
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
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

by:Zberteoc
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
      @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
 
LVL 27

Accepted Solution

by:
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
      @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
 
LVL 70

Assisted Solution

by:Scott Pletcher
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

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question