December 26, 2016 at 11:02 am
Hi All,
I'm pretty new to this and am in need of some help writing a query. I'll try to explain.
I have 2 tables
1. tbl_accom
2. tbl_bookings
The table tbl_accom has 3 columns accomID which is the PK, accomname (accommdation name such as single bedroom and double bedroom) and availno is an integer for the amount of room available.
The table tbl_bookings includes all the guests booking details but the 4 columns I want to concentrate on are book_ID which is the PK, arrivedate, departdate and accomID which is the FK.
What i am trying to achieve is to write a query that pulls out all of the available accommodation that has a availabilty greater than 0 between 2 given dates.
Below is what I have at the moment:
Select accomname, tbl_accom.availno - count(book_id) as availability
From tbl_accom
Left Outer Join tbl_bookings
on tbl_bookings.accomID = tbl_accom.accomID
where tbl_bookings.arrivedate between '2016-09-12' and '2016-10-12' or
tbl_bookings.arrivedate between '2016-09-12' and '2016-10-12' and tbl_bookings.departdate between '2016-01-12' and '2016-10-12' or
tbl_bookings.departdate between '2016-09-12' and '2016-10-12'
Group By tbl_accom.accomname, tbl_accom.availno
The dates I have used just as an example is 2016-09-12 to 2016-10-12 but all the query is pulling out is the 1 record and from what I have read, this is to do with the Where clause. However I don't know how to change this.
Any help at all would be greatly appreciated.
Thanks,
December 26, 2016 at 11:52 am
Firstly a brief explanation of how AND and OR statements work. I noticed that you have a mixture of AND and OR without any brackets, which is likely causing some logic concerns.
Your code shows it has following logic:
--I've tabbed below intentionally for any that find it "offensive to mine eyes" ;)
tbl_bookings.arrivedate between '2016-09-12' and '2016-10-12'
OR
tbl_bookings.arrivedate between '2016-09-12' and '2016-10-12'
AND
tbl_bookings.departdate between '2016-01-12' and '2016-10-12'
OR
tbl_bookings.departdate between '2016-09-12' and '2016-10-12'
You should really be encapsulating your OR statements. It can mean if someone moves something around it doesn't after your query, as you can tell where items are linked.
I'm kind of guessing at an end result here, however, does this fulfil your needs?
SELECT accomname, --Where is this from?? Always use aliasing
a.availno - count(b.book_id /*I guessed this is from booking*/) as [Availability] --Try to avoid key words for Column names too
FROM tbl_accom a --prefixes like this are generally frowned upon as well, due to possible system database conflicts
LEFT OUTER JOIN tbl_bookings b ON a.accomID = b.accomID
WHERE (b.arrivedate BETWEEN '2016-09-12' AND '2016-10-12'
OR b.arrivedate BETWEEN '2016-09-12' AND '2016-10-12')
AND (b.departdate BETWEEN '2016-01-12' AND '2016-10-12'
OR b.departdate BETWEEN '2016-09-12' AND '2016-10-12')
GROUP BY a.accomname,
a.availno
HAVING a.availno - count(b.book_id) > 0;
EDIT: I really need to fix my spacebar.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 27, 2016 at 8:30 am
Thom A (12/26/2016)
Firstly a brief explanation of how AND and OR statements work. I noticed that you have a mixture of AND and OR without any brackets, which is likely causing some logic concerns.Your code shows it has following logic:
--I've tabbed below intentionally for any that find it "offensive to mine eyes" ;)tbl_bookings.arrivedate between '2016-09-12' and '2016-10-12'
OR
tbl_bookings.arrivedate between '2016-09-12' and '2016-10-12'
AND
tbl_bookings.departdate between '2016-01-12' and '2016-10-12'
OR
tbl_bookings.departdate between '2016-09-12' and '2016-10-12'
You should really be encapsulating your OR statements. It can mean if someone moves something around it doesn't after your query, as you can tell where items are linked.
I'm kind of guessing at an end result here, however, does this fulfil your needs?
SELECT accomname, --Where is this from?? Always use aliasinga.availno - count(b.book_id /*I guessed this is from booking*/) as [Availability] --Try to avoid key words for Column names too
FROM tbl_accom a --prefixes like this are generally frowned upon as well, due to possible system database conflicts
LEFT OUTER JOIN tbl_bookings b ON a.accomID = b.accomID
WHERE (b.arrivedate BETWEEN '2016-09-12' AND '2016-10-12'
OR b.arrivedate BETWEEN '2016-09-12' AND '2016-10-12')
AND (b.departdate BETWEEN '2016-01-12' AND '2016-10-12'
OR b.departdate BETWEEN '2016-09-12' AND '2016-10-12')
GROUP BY a.accomname,
a.availno
HAVING a.availno - count(b.book_id) > 0;
EDIT: I really need to fix my spacebar.
I really don't think that your rewrite is what the OP is looking for, because in both subclauses you're ORing two identical predicates. I think the OP meant to use the logical precedence that ANDs are evaluated before ORs.
For the OP, this sounds like a gaps and islands problem, but the problem is that you're looking at the gaps (booked) rather than the islands (availability). I assume that you want to be able to find contiguous availability rather than just total availability, and your setup is not conducive to that approach.
Also, you're running into the typical problem with identifying overlapping intervals: you're trying to compare starts with starts and ends with ends. The formula is actually quite simple: each interval starts before the other ends. In code that's
b.arrivedate <= '2016-10-12' AND
b.departdate > '2016-09-12'
The approach I would take is use a calendar table to create all possible dates for each accommodation between the start and end dates, then join on your bookings table, and, finally, find which dates didn't have any bookings.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 27, 2016 at 10:11 am
The table tbl_bookings is outer joined. Thus, every reference to columns from that table must allow for the column to be NULL. The WHERE clause references to tbl_bookings must allow for NULLs or be included in the ON clause. In the query below, I have added them to the ON clause and removed the WHERE clause.
SELECT A.accomname, A.availno - count(B.book_id) AS [availability]
FROM tbl_accom A
LEFT OUTER JOIN tbl_bookings B
ON B.accomID = A.accomID
AND B.arrivedate BETWEEN '2016-09-12' and '2016-10-12' OR
B.arrivedate BETWEEN '2016-09-12' and '2016-10-12' AND B.departdate BETWEEN '2016-01-12' AND '2016-10-12' OR
B.departdate BETWEEN '2016-09-12' and '2016-10-12'
GROUP BY A.accomname, A.availno
This fix is in addition to the other issues pointed out by the other responses (using table aliases, grouping the OR/AND clauses, using correct criteria for date ranges).
December 27, 2016 at 12:26 pm
Hi Guys,
Thanks for the replies, after a bit of playing about I finally got it to work as I needed. I used the below code.
<code>
SELECT a.accomname, a.availno - count(book_id) as availability
FROM tbl_accom AS a
LEFT OUTER JOIN tbl_bookings AS b
on b.accomID = a.accomID
and (b.arrivedate between '2016-01-11' and '2016-09-12' or b.departdate between '2016-01-11' and '2016-09-12')
GROUP BY a.accomname, a.availno
</code>
Just a quick question, the dates in the code will be populated from calendars on my website, how can I replace the dates with variables? Then all I have to do is put the value into the variable in vb.net.
Cheers,
December 27, 2016 at 3:27 pm
Simon Hammill (12/27/2016)
Hi Guys,Thanks for the replies, after a bit of playing about I finally got it to work as I needed. I used the below code.
SELECT a.accomname, a.availno - count(book_id) as availability
FROM tbl_accom AS a
LEFT OUTER JOIN tbl_bookings AS b
on b.accomID = a.accomID
and (b.arrivedate between '2016-01-11' and '2016-09-12' or b.departdate between '2016-01-11' and '2016-09-12')
GROUP BY a.accomname, a.availno
Just a quick question, the dates in the code will be populated from calendars on my website, how can I replace the dates with variables? Then all I have to do is put the value into the variable in vb.net.
Cheers,
Again, this can be simplified.
SELECT a.accomname, a.availno - count(book_id) as availability
FROM tbl_accom AS a
LEFT OUTER JOIN tbl_bookings AS b
on b.accomID = a.accomID
and b.arrivedate <= '2016-09-12' -- or @end_range
and b.departdate >= '2016-01-11' -- or @start_range
GROUP BY a.accomname, a.availno
Drew
PS: I replaced the incorrect code tags with the correct ones. See my signature for more details.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 28, 2016 at 11:43 am
>> I'm pretty new to this and am in need of some help writing a query. I'll try to explain. <<
Do not try to explain things by telling us a story. If you had read the forum rules, you would post DDL. That way we do not have to try and guess at everything, do transcriptions for you, etc.
Another classic mistake is tibbling. This term was invented by Phil Factor; t refers to using metadata affixes in schema element names (like “tbl_”). According to ISO 11179 standards and basic data modeling. You should name a table for what it is by its very nature, not physical storage, or language constructs.
CREATE TABLE Accommodations
(accommodation_id CHAR(5) NOT NULL PRIMARY KEY,
accommodation_type CHAR(5) NOT NULL
CHECK (accommodation_type IN (‘1BR’, ‘2BR’, ..)),
occupied_room_count INTEGER DEFAULT 0 NOT NULL
CHECK(occupied_room_count >= 0));
What you were calling a name is actually a type. Why do you think that “single bedroom” is a name? Is there only one of them in all the world?
CREATE TABLE Bookings
(booking_nbr CHAR(16) NOT NULL PRIMARY KEY,
arrival_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
departure_date DATE,
CHECK (arrival_date <= departure_date)
accommodation_id CHAR(5) NOT NULL
REFERENCES Accommodations (accommodation_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
See how check constraints and references do so much of your programming declaratively. A NULL departure date indicates that the accommodation is still booked. Why not make this a procedure and use parameters instead of constant dates?
>> a query that pulls out all of the available accommodation that have availability greater than 0 BETWEEN two given dates. Below is what I have at the moment:
You do not seem to understand how Boolean algebra works; the AND is stronger than the OR. However, the [NOT] BETWEEN..AND 3-ary predicate is stronger than both of them.
Let us try a little different approach. We are looking for a row in the bookings that has the (@start_date,@end_date) interval inside it.
SELECT B.accommodation_id
FROM B.Bookings AS B
WHERE B.arrival_date <= @start_date
AND B.departure_date >= @end_date
GROUP BY B.accommodation_id
HAVING SUM(B.occupied_room_count) = 0);
This interval should have no occupied rooms. This gives you the accommodation ID and then you can join it back the accommodations table, if you need more details.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply