December 10, 2008 at 4:52 am
Hi Guys,
I have a reservation system with a table for the units to rent (#PropDet) and a table for the reservations (#Availability).
I want to be able to run a query based on a field in the #PropDet table - "Resort", also using a Start Date and End Date which will query the #Availability table to see which units do not have a "reservation"
I'm struggling to get started with this but have found this tutorial which replicates what I want to achieve. The tutorial however is for access (I'm using mssql) and makes use of Views, which I am unfamiliar with.
http://support.microsoft.com/kb/245074
I'm hoping for a bit of guidance with this, even just a nudge in the right direction would be appreciated.
Here's some sample data:
CREATE TABLE #PropDet
(
PropId INT IDENTITY(1, 1) PRIMARY KEY,
UserId UNIQUEIDENTIFIER,
PropName NVARCHAR(50),
Resort NVARCHAR(50),
Col5 NVARCHAR(50),
Col6 NVARCHAR(50),
Col7 NVARCHAR(50),
Col8 NVARCHAR(50),
Col9 NVARCHAR(50),
Col10 NVARCHAR(50),
Col11 NVARCHAR(50),
Col12 NVARCHAR(50)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #PropDet ON
INSERT INTO #PropDet
(PropId, UserId, PropName, Resort)
SELECT '6','6EC16A5D-1257-49FA-AA1B-8AC745AF41E5','Villa Marky','Courchevel 1850', UNION ALL
SELECT '9','E3EEAE25-622E-4C6B-B782-FDA576C9B10B','Villa Mummypenny Elly ','Meribel', UNION ALL
SELECT '17','6EC16A5D-1257-49FA-AA1B-8AC745AF41E5','Chateaux La La','Meribel'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #PropDet ON
CREATE TABLE #Availability
(
ReservationId INT IDENTITY(1, 1) PRIMARY KEY,
PropId INT,
StartDate DATETIME,
EndDate DATETIME
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Availability ON
INSERT INTO #Availability
(ReservationId, PropId, StartDate, EndDate)
SELECT '4','6','04/01/09','11/01/09', UNION ALL
SELECT '5','6','18/01/09','25/01/09', UNION ALL
SELECT '6','6','04/02/09','11/02/09', UNION ALL
SELECT '9','9','11/01/09','18/01/09', UNION ALL
SELECT '10','9','04/02/09','11/02/09', UNION ALL
SELECT '11','9','18/02/09','25/02/09', UNION ALL
SELECT '12','9','01/03/09','08/03/09', UNION ALL
SELECT '18','17','04/01/09','11/01/09', UNION ALL
SELECT '19','17','11/01/09','18/01/09', UNION ALL
SELECT '20','17','11/02/09','18/02/09', UNION ALL
SELECT '21','17','01/03/09','08/03/09'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Availability ON
Thanks in advance to anybody who can help
Mark
December 10, 2008 at 6:40 am
Hi,
Ive used the tutorial i found (see above link) to produce a query that will give me the Id of any unit the DOES have a reservation between 2 supplied dates:
@StartDate datetime,
@EndDate datetime
AS
SELECT
Availibility.PropId
FROM Availibility
WHERE (((Availibility.StartDate) Between @StartDate And @EndDate - 1))
Or (((Availibility.EndDate - 1) Between @StartDate And @EndDate))
Or (((Availibility.StartDate) @EndDate - 1))
so far so good. It seems to give me a list of units that aren't available.
How would I use this value to give me a list of units that are available???
Thanks in advance.
December 10, 2008 at 6:59 am
Hi Mark
What you'd normally do now is use the output from this bit which you've figured out - which is "bookings" - and use it as a filter in a SELECT from #PropDet. There are a few ways of doing this, here's the simplest to understand:
SELECT *
FROM PropDet p
WHERE NOT EXISTS (SELECT 1 FROM Availibility a
WHERE a.PropId = p.PropId
AND ((a.StartDate) Between @StartDate And @EndDate - 1)
Or (a.EndDate - 1 Between @StartDate And @EndDate)
Or (a.StartDate) < @EndDate - 1)) -- <-- operator was missing)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 10, 2008 at 7:41 am
Hi Chris,
Thanks for your interest . Very bizarrely, where you have put operator missing some of the query has disappeared. I've tried to edit the post and repost the whole thing again but it just won't show, even if I don't used /code/.
I can't even post only that single line of code so I'll try and type it another way:
or (a.startdate "is less than" @startdate and enddate - 1 "is greater than" @enddate - 1)
I've run the code you supplied with my alteration for the missing code (as above) and unfortunately no rows are returned by the query so I'm stuck!
Any other thoughts?
December 10, 2008 at 7:45 am
What values did you assign to startdate and enddate?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 10, 2008 at 9:22 am
Hi,
I assigned a date format dd/mm/yy to @StartDate and @EndDate.
I have different data in my table than the data supplied above but the first query works fine with these values and returns the results expected, i.e. the PropId that isn't available during the dates supplied.
Does this help?
December 10, 2008 at 9:54 am
also just discovered that if I change from WHERE NOT EXISTS to WHERE EXISTS then all rows are returned. This suggests the subquery is not working correctly no?
Regards
December 10, 2008 at 10:09 am
Hi Mark
This requirement isn't unusual, and won't take long to fix. We do need a level playing field though, which you, and any contributors to this thread, can work to - and that's the sample data and the values assigned to @StartDate and @EndDate. If you're not happy with the sample data, would you mind rejigging it until you are? It will make things much easier for everybody.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 10, 2008 at 10:33 am
Ok Here goes.
I'm just working with test data too so I've only created a very small set
SELECT '1','6','Jan 1 2009 12:00AM','Jan 8 2009 12:00AM', UNION ALL
SELECT '2','6','Jan 15 2009 12:00AM','Jan 22 2009 12:00AM', UNION ALL
SELECT '3','17','Jan 1 2009 12:00AM','Jan 8 2009 12:00AM'
the value I've been using for @StartDate is 02/01/09
the value I've been using for @EndDate is 05/01/09
These values work fine and produce the correct results (6 and 17) whe I execute the query at the top of the page (although bear in mind the query hasn't displayed properly in the post).
I know this is a very small data set so If you want me to add some more to my table let me know but this is what i've been working with
Thanks again
December 10, 2008 at 11:43 am
First, I have cleaned up the initial code listing. The commas at the end of the SELECT statements should not be there. For those of us in the U.S., I have added the SET DATEFORMAT command to the start and end of the inserts. By default, U.S. English installations are mdy, not dmy, as is used in the samples.
The code:
CREATE TABLE #PropDet
(
PropId INT IDENTITY(1, 1) PRIMARY KEY,
UserId UNIQUEIDENTIFIER,
PropName NVARCHAR(50),
Resort NVARCHAR(50),
Col5 NVARCHAR(50),
Col6 NVARCHAR(50),
Col7 NVARCHAR(50),
Col8 NVARCHAR(50),
Col9 NVARCHAR(50),
Col10 NVARCHAR(50),
Col11 NVARCHAR(50),
Col12 NVARCHAR(50)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #PropDet ON
INSERT INTO #PropDet
(PropId, UserId, PropName, Resort)
SELECT '6','6EC16A5D-1257-49FA-AA1B-8AC745AF41E5','Villa Marky','Courchevel 1850' UNION ALL
SELECT '9','E3EEAE25-622E-4C6B-B782-FDA576C9B10B','Villa Mummypenny Elly ','Meribel' UNION ALL
SELECT '17','6EC16A5D-1257-49FA-AA1B-8AC745AF41E5','Chateaux La La','Meribel'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #PropDet OFF
CREATE TABLE #Availability
(
ReservationId INT IDENTITY(1, 1) PRIMARY KEY,
PropId INT,
StartDate DATETIME,
EndDate DATETIME
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Availability ON
SET DATEFORMAT dmy
INSERT INTO #Availability
(ReservationId, PropId, StartDate, EndDate)
SELECT '4','6','04/01/09','11/01/09' UNION ALL
SELECT '5','6','18/01/09','25/01/09' UNION ALL
SELECT '6','6','04/02/09','11/02/09' UNION ALL
SELECT '9','9','011/01/09','18/01/09' UNION ALL
SELECT '10','9','04/02/09','11/02/09' UNION ALL
SELECT '11','9','18/02/09','25/02/09' UNION ALL
SELECT '12','9','01/03/09','08/03/09' UNION ALL
SELECT '18','17','04/01/09','11/01/09' UNION ALL
SELECT '19','17','11/01/09','18/01/09' UNION ALL
SELECT '20','17','11/02/09','18/02/09' UNION ALL
SELECT '21','17','01/03/09','08/03/09'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Availability OFF
SET DATEFORMATmdy
Something for consideration: You have the second table named "Availability". Certainly this is up to designer preferrence, but generally, tables are named based upon what a single row defines. In this case, neither a row or a table defines "Availability", rather the opposite. Hence why in the Access example that you linked, this table is named "Booking". This makes no difference in solving the problem, but it is a good thing for you to consider as a part of your overall design.
Back to the code, the original query that has been posted (with some revisions) for finding properties that were NOT available is wrong. The search arguments that are there are grouped incorrectly and a very significant argument is missing (not just the ">").
Using the examples provided, this query returns the properties in use during the time specified:
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '01/03/09'
SET @EndDate = '01/13/09'
SELECT PropId, StartDate, EndDate
FROM #Availability
WHERE (StartDate BETWEEN @StartDate AND @EndDate - 1 OR EndDate BETWEEN @StartDate AND @EndDate)
OR (StartDate < @StartDate
AND EndDate > @EndDate - 1)
The first grouping in the search arguments identified the bookings that start or end during your time range. The second identifies the ones that are bigger than your time range. (note: After pasting this code in here, I find that if the less than symbol and the greater than symbol were on the same line, it was being processed differently by the forum. Pretty sure this is what you were referring to in your earlier problems with posting.)
Now that we know what the reservations are, we can find which rooms are available:
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '02/23/09'
SET @EndDate = '02/28/09'
SELECT *
FROM #PropDet PD
LEFT OUTER JOIN (SELECT PropId, StartDate, EndDate
FROM #Availability
WHERE (StartDate BETWEEN @StartDate AND @EndDate - 1
OR EndDate BETWEEN @StartDate AND @EndDate)
OR (StartDate < @StartDate
AND EndDate > @EndDate - 1)) as Booked
ON PD.PropId = Booked.PropId
WHERE Booked.PropId IS NULL
Using the dates in the sample above, one room (PropId 9) is booked and 2 (6 and 17) are available.
Test it out, I think you will get what you need from this.
GL!
John
December 11, 2008 at 2:56 am
Hi John,
That works a treat. Thanks for your help. It's much appreciated. Thanks also for the info in formatting code listings. I've never really looked into doing things this way. I'll pay more attention to it now.
Thanks also for the spelling lesson! I'm gonna have to go through my project and change all those I's to A's!
Once again thank you
Mark
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply