Solved

SQL:   Data Sets - Reserved vs Available Dates

Posted on 2007-11-29
4
184 Views
Last Modified: 2010-04-21
I have a table with reservation dates.  I would like to create a query that returns the dates not in the reservation table...for a given date range.  Is there a simple way to do this without.

I basicly need to return a set of 'interesting' dates....and the interest is availability.
0
Comment
Question by:Paulconsulting
4 Comments
 
LVL 22

Expert Comment

by:dportas
ID: 20376880
SELECT dt
FROM Calendar
WHERE dt >= '20070101'
 AND dt < '20080101'
EXCEPT
SELECT dt
FROM Reservations;
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 20377268
SELECT c.date
FROM calendar c
LEFT OUTER JOIN reservations r ON c.date = r.date
WHERE r.date IS NULL
AND c.date BETWEEN 'startDate' AND 'endDate'
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 125 total points
ID: 20377363
The simplest way is to use a calendar table or, more versatilely, a numbers table with DATEDIFF.  This example uses a numbers table with numbers from 0 to 8192 (which should be a long enough range).

CREATE TABLE Numbers (value int primary key)

INSERT INTO Numbers Values (0)
Insert Into Numbers Values (1)

INSERT INTO Numbers SELECT Value + 2 FROM Numbers
INSERT INTO Numbers SELECT Value + 4 FROM Numbers
INSERT INTO Numbers SELECT Value + 8 FROM Numbers
INSERT INTO Numbers SELECT Value + 16 FROM Numbers
INSERT INTO Numbers SELECT Value + 32 FROM Numbers
INSERT INTO Numbers SELECT Value + 64 FROM Numbers
INSERT INTO Numbers SELECT Value + 128 FROM Numbers
INSERT INTO Numbers SELECT Value + 256 FROM Numbers
INSERT INTO Numbers SELECT Value + 512 FROM Numbers
INSERT INTO Numbers SELECT Value + 1024 FROM Numbers
INSERT INTO Numbers SELECT Value + 2048 FROM Numbers
INSERT INTO Numbers SELECT Value + 4096 FROM Numbers


Now that we have our table, let's see what we can do with it:

Assume that our interested date range is from Jan 15, 2007 to June 15, 2007

SELECT Dateadd(Day, N.Value, '20070115')
FROM Numbers N
LEFT JOIN ReservationDates rd
    ON Dateadd(Day, N.Value, '20070115') = rd.ReservedDate
WHERE N <= Datediff(Day, '20070115', '20070615')
    AND rd.ReservedDate is null

Clearly, a table with all dates would also work.  But the numbers table allows for more versatility with a smaller table (need to query a range back in 1980?  No need to have all of the dates in the table).
0
 

Author Closing Comment

by:Paulconsulting
ID: 31411776
Works great!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now