April 21, 2009 at 9:38 am
Heh, glad we could help. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2009 at 5:39 am
so is there any way to achieve this faster?
consider a table with 10.000 records with BookingStartDate, BookingEndDate, RoomId and Id
A simple test for all Booking Id to tell whether is or isn't overlapped is very costly performance wise.
does it help if i index the BSD && BED ?!
my real example includes more : a booking number, a booking quantity in the booking table and an available number column and a quantity column in another table: Resources/Rooms. For this, i must do the overlap calculus twice: to find the overlapping bookings, and then find each of these booked number (grouped by quantity), after which left join Resources table to compare with Available Number(also for quantity).
and is still more complicated :)... i have to consider time also. consider a booking record that is date ranged:
BSD: '2009-08-10 10:00'
BED: '2009-08-19 16:00'
Room = 1
and another one is
BSD: '2009-08-11 17:00'
BED: '2009-08-11 18:00'
Room = 1
in this case, neither booking should bring an overlap.
The method i use is through a self join:
SELECT *
FROM
tblBookings a INNER JOIN
tblBookings b ON
a.id b.id AND
a.roomid = b.roomid AND
-- here is the date range overlap filter; i'll write the short version
a.BookingStartDateTime < b.BookingEndDateTime and
b.BookingStartDateTime < a.BookingEndDateTime
August 12, 2009 at 9:53 am
jack ha (8/12/2009)
so is there any way to achieve this faster?...
does it help if i index the BSD && BED ?!
Yes, definitely.
my real example includes more : a booking number, a booking quantity in the booking table and an available number column and a quantity column in another table: Resources/Rooms. For this, i must do the overlap calculus twice: to find the overlapping bookings, and then find each of these booked number (grouped by quantity), after which left join Resources table to compare with Available Number(also for quantity).
Sorry, but this whole description makes no sense to me. If you want us to go any further with this we will need the table DDL's, some sample data and example results that you want.
and is still more complicated :)... i have to consider time also. consider a booking record that is date ranged:
BSD: '2009-08-10 10:00'
BED: '2009-08-19 16:00'
Room = 1
and another one is
BSD: '2009-08-11 17:00'
BED: '2009-08-11 18:00'
Room = 1
in this case, neither booking should bring an overlap.
??? Why not? It does appear to be overlapping.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 13, 2009 at 12:37 am
This is the main definitions used in this case scenario, the tables contain more columns, but these are the columns specific to this situation
tblBooking :
id int
resourceId int, -- related to tblResources.Id
BookingStartDate smalldatetime, -- seconds do not matter
BookingEndDate smalldatetime,
ResourceType int, --related to tblResourcesTypes
BookedNo decimal(10,4) -- how many items
tblResources:
id int, --not unique
ResourceName varchar(20),
ResourceType int,
AvailableNo decimal(10,4)
tblResourcesTypes:
id int,
Name varchar
Cases:
resourcesTypes:
id, name
1, 20ProductsVendingMachine
2, 30ProductsVendingMachine
3, actors
4, singers
5, tables
tblResource:
id, ResourceName, ResourceType, AvailableNo
1, Vending Machine, 1(20ProductsVendingMachine), 5(pieces)
1, Vending Machine, 2(30ProductsVendingMachine), 2(pieces)
3, Jim Carrey, 3(Actors), 1 (no doubles allowed 🙂 )
tblBookings:
id, ResourceId, BSD, BED, ResourceType, BookedNo
1, 3(JimCarrey), '2009-08-10 10:00', '2009-08-19 16:00', 3(Actor), 1
2, 3(JimCarrey), '2009-08-11 17:00', '2009-08-11 18:00', 3(Actor), 1
3, 1(VendingMachine), '2009-08-20 09:00', '2009-08-20 22:00', 1(20SlotsVendingMachine), 3
4, 1(VendingMachine), '2009-08-20 10:00', '2009-08-20 11:00', 1(20SlotsVendingMachine), 3
5, 1(VendingMachine), '2009-08-20 10:00', '2009-08-20 11:00',
2(30SlotsVendingMachine), 2
Explanations:
Booking 1 and 2 seem to be overbooking mathematically, but not functionally. Booking 1 must be read as "on each day from 10oct till 19oct an actor is booked for 6 hours: from 10 to 16"
Booking 2 is read "the actor is booked on 11 from 17:00 till 18:00". That actor can be present at both bookings, so functionally speaking, it is not considered an overlap.
Bookings 3,4,5:
All present the same resource(a vending machine). Consider the resource type as a quantity: e.g. litres, kilograms, miles, feet, boxes. Vending machines come in two sizes : 20 slots capacity and 30 slots. The maximum number of vending machines that the hotel has is 7: 5 with 20slots capacity and 2 with 30 slots. Booking Overlap sould be considered for each resource for its type separately, meaning booking 3 and 4 do not affect booking 5, but booking 3 affects booking 4 and vice versa.
Booking 5 does not have another resource VendingMachine of type 30Slots in the table so it does not overlap, and isn't overlapped.
Booking 3 and 4 have the same type so a SUM() must be made because they have overlap ( 10:00-11:00 is inside 09:00-22:00 ), 3pieces for booking 3 + 3pieces for booking 4 equals 6pieces booked which is bigger than what is available for VendingMachine of type 20Slots, that is 5 from tblResources. so for neither these bookings the booking would be possible. (there is a functional flaw here, the booking 3 starts before booking 4 with 3 pieces of Vending machines. booking 4, that starts later may start with the two reaming pieces and an overlap would be present only for this booking. but the big view must be made as if the bookings are long time planned ahead, so both bookings must show overlap: one affects the other and viceversa)
the code that i applied is similar to this:
select x.*, y.totalbookeditems from
(
select a.id, a.resourceid, a.resourcetype, a.bookedno from tblBookings a
inner join tblBookings b
on a.id b.id and
a.resourceid = b.resourceid and
a.resourcetype = b.resourcetype and
datediff(day, 0, a.bookingstartdate) < datediff(day, 0, b.bookingenddate) and
datediff(day, 0, b.bookingstartdate) < datetiff(day, 0, a.bookingenddate) and
dateadd(day, -datediff(day, 0, a.bookingstardate), a.bookingstartdate) < dateadd(day, -datediff(day, 0, b.bookingenddate), b.bookingenddate) and
dateadd(day, -datediff(day, 0, b.bookingstartdate), b.bookingstartdate) < dateadd(day, -datediff(day, 0, a.bookingenddate), a.bookingenddate)
) x left join
(
select a.resourceid, a.resourcetype, sum(a.bookedno) as TotalBookedNo from tblBookings a
inner join tblBookings b
on a.id b.id and
a.resourceid = b.resourceid and
a.resourcetype = b.resourcetype and
datediff(day, 0, a.bookingstartdate) < datediff(day, 0, b.bookingenddate) and
datediff(day, 0, b.bookingstartdate) < datetiff(day, 0, a.bookingenddate) and
dateadd(day, -datediff(day, 0, a.bookingstardate), a.bookingstartdate) < dateadd(day, -datediff(day, 0, b.bookingenddate), b.bookingenddate) and
dateadd(day, -datediff(day, 0, b.bookingstartdate), b.bookingstartdate) < dateadd(day, -datediff(day, 0, a.bookingenddate), a.bookingenddate)
group by resourceid, resourcetype
) y on
x.resource = y.resource and
y.resourcetype = y.resourcetype
left join tblresources on
tblresources.id = x.id and tblresources.resourcetype = x.resourcetype
where TotalBookedNo <= tblresources.AvailableNo
i tried to reproduce this code as far as i could, i am logged in at a farther location from the sql server at the moment.
My problem does not consists whether this works, my problem is based performance wise. On many records, more then 10.000 bookings, this is time consuming, not because of the fact i run those Where 2 times, not because there are too many datetime operations (a while ago i had a simple a.BookingStartDateTime < b.BookingEndDateTime and
b.BookingStartDateTime < a.BookingEndDateTime , which didn't comply with first case scenario presented: the actor), it may be because the filter(overlapping dates filter) is based on a self join that may have up to 10.000 x 10.000 records (or my logic here is somehow wrong).
as suggested, i will try to index BSD and BED and see what happens...
August 13, 2009 at 12:55 am
I'm just curious (and a tad concerned) but is this for a live app or a uni project or something?
Because honestly, I have no idea why you'd take the approach of writing complex SQL to check for duplicate bookings instead of just stopping it happening in the first place. Or do you not have any say in how the app which makes the bookings works?
It's a very messy approach.
I don't know if it helps you but here's some very simple code to check for duplicates based on your criteria. If any rows are returned then there are duplicates, if not then there aren't. Keep it simple, where possilbe!
-- Get duplciate bookings
-- nb. If at least one duplicate found then all items for the rescource are listed
-- including those that don't overlap.
SELECT *
FROM tblBookings ta
WHERE EXISTS
(
SELECT TOP 1 ID FROM tblBookings
WHERE ResourceId = ta.ResourceId
AND ResourceType = ta.ResourceType
AND (BSD BETWEEN ta.BSD AND ta.BED
OR BED BETWEEN ta.BSD AND ta.BED)
)
You could use similar logic to this to stop the duplicates being inserted in the first place. If any rows returned, don't allow the booking to be made..
-- See if bookings already exist for this rescource
SELECT *
FROM tblBookings ta
WHERE ResourceId = @YourResID
AND ResourceType = @YourResType
AND (@YourBSD BETWEEN ta.BSD AND ta.BED
OR @YourBED BETWEEN ta.BSD AND ta.BED)
August 13, 2009 at 2:18 am
That is right, i don't have too much power over the client's ideas, but after all it's a feature of the application.
I tried to do the sum of BookedNo with
select id, resourceid, resourcetype, bookedno, sum(bookedno) OVER PARTITION(group by resourceid, resourcetype) from ...
this way only on time the booking is checked && the all required fields are selected, including tblBookings.Id (the one that is actually needed for the result && cannot be appended to the simple group by. But this approach proved to be a lot more time consuming.
Keep it simple. yes, i tend to complicate things && do not see the simple solution often. i rewrote this statement more than a dozen times.
thank you very much for the code snippet presented, but as i mentioned earlier, part of the functionality includes time booking also, which does permit a booking to be booked(doesn't raise overlap message) for different times in the day(the JimCarrey example in a post above). Also, not all resources are '1piece'... so for those, the SUM(of bookings items) must be made.
the beginning of the thread asked if one may use tally tables for this type of operations. && i must say this sounds interesting. but i can't figure out what the tally should contain && how to be addressed..
There is one solution that could prove useful, saving the overbooking && overbooked items in a separate table && update it on triggers. this way telling whether a booking is overlapping || overlapped would be very fast.
But i think it isn't quite effective for a very large number of databases the application runs on due to, i think, large and often dba.
August 13, 2009 at 2:29 am
My code should cater for time as well, but I have to admit I haven't tested it.
i.e. 17:00-18:00 on the 13/08/2009 is not between 10:00 13/08/2009 - 16:00 13/08/2009 even though it's the same day. So my code would not return it as a duplicate.
August 13, 2009 at 2:37 am
Yes it does, that was the initial filter for dates that i used, but seemed not to work for some cases (actors, rooms).
consider a booking from Monday to Friday from 08:00 to 16:00. that is saved as :
id, Name, StartDate, EndDate
1, Work, '2009-08-10 08:00', '2009-08-14 16:00'
And another:
id, Name, StartDate, EndDate
2, HaveFun, '2009-08-11 18:00', '2009-08-11 20:00'
Having fun is out of working hours theoretically && functionally speaking. Mathematically,
'2009-08-10 08:00' to '2009-08-14 16:00' includes all hours between Monday at 8 && Friday at 16.
August 13, 2009 at 2:59 am
Oh, you mean it won't work once it goes past a single day booking 🙂
Could you give this a try?
-- Get duplicate bookings
-- nb. If at least one duplicate found then all items for the rescource are listed
-- including those that don't overlap.
-- 1, Get duplciates where there is only one rescource e.g. an actor
SELECT ta.*
FROM tblBookings ta
INNER JOIN tblRescource tr
ON tr.ID = ra.ResourceId
WHERE EXISTS
(
SELECT TOP 1 ID FROM tblBookings tb
WHERE tb.ResourceId = ta.ResourceId
AND tb.ResourceType = ta.ResourceType
AND (
(tb.BSD BETWEEN ta.BSD AND ta.BED
AND CONVERT(char(8), tb.BSD, 108) -- Also check time overlaps
BETWEEN CONVERT(char(8), ta.BSD, 108) AND CONVERT(char(8), ta.BED, 108))
OR
(tb.BED BETWEEN ta.BSD AND ta.BED
AND CONVERT(char(8), tb.BED, 108) -- Also check time overlaps
BETWEEN CONVERT(char(8), ta.BSD, 108) AND CONVERT(char(8), ta.BED, 108))
)
AND tb.ID != ta.ID
)
AND tr.AvailableNo = 1
-----
UNION
-----
-- 2, Get duplicates where there is more than one unit of rescource e.g. a vending machine
-- Only get duplicates where the total units booked > the total units avilable
SELECT ta.*
FROM tblBookings ta
INNER JOIN tblRescource tr
ON tr.ID = ra.ResourceId
WHERE EXISTS
(
SELECT TOP 1 ID FROM tblBookings tb
WHERE tb.ResourceId = ta.ResourceId
AND tb.ResourceType = ta.ResourceType
AND (
(tb.BSD BETWEEN ta.BSD AND ta.BED
AND CONVERT(char(8), tb.BSD, 108) -- Also check time overlaps
BETWEEN CONVERT(char(8), ta.BSD, 108) AND CONVERT(char(8), ta.BED, 108))
OR
(tb.BED BETWEEN ta.BSD AND ta.BED
AND CONVERT(char(8), tb.BED, 108) -- Also check time overlaps
BETWEEN CONVERT(char(8), ta.BSD, 108) AND CONVERT(char(8), ta.BED, 108))
)
AND tb.ID != ta.ID
)
AND
(
SELECT SUM(BookedNo) FROM tblBookings tc
WHERE tc.ResourceId = ta.ResourceId
AND tc.ResourceType = ta.ResourceType
AND (
(tc.BSD BETWEEN ta.BSD AND ta.BED
AND CONVERT(char(8), tc.BSD, 108) -- Also check time overlaps
BETWEEN CONVERT(char(8), ta.BSD, 108) AND CONVERT(char(8), ta.BED, 108))
OR
(tc.BED BETWEEN ta.BSD AND ta.BED
AND CONVERT(char(8), tc.BED, 108) -- Also check time overlaps
BETWEEN CONVERT(char(8), ta.BSD, 108) AND CONVERT(char(8), ta.BED, 108))
)
) > tr.AvailableNo
AND tr.AvailableNo > 1
August 13, 2009 at 4:26 am
janine.rawnsley (8/13/2009)
I'm just curious (and a tad concerned) but is this for a live app or a uni project or something?Because honestly, I have no idea why you'd take the approach of writing complex SQL to check for duplicate bookings instead of just stopping it happening in the first place. Or do you not have any say in how the app which makes the bookings works?
Many hotels do overbook the rooms because their experience says 5-10 percent of the guest never arrive.
This is quite common in other areas too, such as ferries, flights and so on...
N 56°04'39.16"
E 12°55'05.25"
August 13, 2009 at 4:30 am
Isn't this simpler?
SELECT *
FROM tblBookings AS b1
INNER JOIN tblBookings AS b2 ON b2.ResourceType = b1.ResourceType
AND b2.RoomID b1.RoomID
WHERE b1.StartTime = b1.StartTime
N 56°04'39.16"
E 12°55'05.25"
August 13, 2009 at 6:31 am
jack ha (8/13/2009)
This is the main definitions used in this case scenario, the tables contain more columns, but these are the columns specific to this situationtblBooking :
id int
resourceId int, -- related to tblResources.Id
BookingStartDate smalldatetime, -- seconds do not matter
...
resourcesTypes:
id, name
1, 20ProductsVendingMachine
2, 30ProductsVendingMachine
3, actors
...
Thanks for the response, Jack. I see that a couple of folks are already on this. For future reference, it is very helpful to us if the table DDL's are the actual DDL statements (i.e., CREATE TABLE ...) and iin the data is provided in the form of INSERT statements. Here's an article by Jeff Moden that explains the what and why of it: http://qa.sqlservercentral.com/articles/Best+Practices/61537/. Getting the CREATE TABLEs should be easy, just script them from Enterprise Manager. Generating the INSERTs can be automated as well: http://qa.sqlservercentral.com/articles/scripting/64884/.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply