May 22, 2014 at 9:57 am
I need to count the number of beds occupied on a location. There are half-week trips, week-trips, two-week trips using the location, each two dates to mark the start and the end of the trip.
E.G. (all dates in May 2014):
Trip Occupied
1 |--5--| 12–14 may
2 |--7--| 15-18 may
3 |-----4------| 12–18 may
4 |-------------2------------| 12-25 may
5 |-----------2--------------| 05-18 may
6 |----1-------| 05-11 may
7 |--3--| 05-07 may
8 |--0--| 08-11 may
At the start of trip 1 there are 5+4+2+2= 13 beds occupied, etc.
The results I want are:
for each Trip the amount of beds occupied on the location at the start of the trip:
Trip Occupied
1 13
2 15
3 15
4 15
5 15
6 6
7 6
8 3
The reason I want these results, is to count whether a location is full.
E.g. With 15 beds available at the location, trip 2, 3, 4, 5 are full.
Here are some data for testing:
CREATE TABLE [Trips]
(
[TripID] [int] NOT NULL,
[DateFrom] [smalldatetime] NOT NULL,
[DateTill] [smalldatetime] NOT NULL
)
CREATE TABLE [Bookings]
(
[BookingID] [int] IDENTITY(1,1) NOT NULL,
[TripID] [int] NOT NULL
)
INSERT INTO Trips VALUES(1, '2014-05-12', '2014-05-14')
INSERT INTO Trips VALUES(2, '2014-05-15', '2014-05-18')
INSERT INTO Trips VALUES(3, '2014-05-12', '2014-05-18')
INSERT INTO Trips VALUES(4, '2014-05-12', '2014-05-25')
INSERT INTO Trips VALUES(5, '2014-05-05', '2014-05-18')
INSERT INTO Trips VALUES(6, '2014-05-05', '2014-05-11')
INSERT INTO Trips VALUES(7, '2014-05-05', '2014-05-07')
INSERT INTO Trips VALUES(8, '2014-05-08', '2014-05-11')
INSERT INTO Bookings (TripID) VALUES (1)
INSERT INTO Bookings (TripID) VALUES (1)
INSERT INTO Bookings (TripID) VALUES (1)
INSERT INTO Bookings (TripID) VALUES (1)
INSERT INTO Bookings (TripID) VALUES (1)
INSERT INTO Bookings (TripID) VALUES (2)
INSERT INTO Bookings (TripID) VALUES (2)
INSERT INTO Bookings (TripID) VALUES (2)
INSERT INTO Bookings (TripID) VALUES (2)
INSERT INTO Bookings (TripID) VALUES (2)
INSERT INTO Bookings (TripID) VALUES (2)
INSERT INTO Bookings (TripID) VALUES (2)
INSERT INTO Bookings (TripID) VALUES (3)
INSERT INTO Bookings (TripID) VALUES (3)
INSERT INTO Bookings (TripID) VALUES (3)
INSERT INTO Bookings (TripID) VALUES (3)
INSERT INTO Bookings (TripID) VALUES (4)
INSERT INTO Bookings (TripID) VALUES (4)
INSERT INTO Bookings (TripID) VALUES (5)
INSERT INTO Bookings (TripID) VALUES (5)
INSERT INTO Bookings (TripID) VALUES (6)
INSERT INTO Bookings (TripID) VALUES (7)
INSERT INTO Bookings (TripID) VALUES (7)
INSERT INTO Bookings (TripID) VALUES (7)
Using the Query
SELECT (SELECT COUNT(*) FROM Bookings WHERE TripID = T.TripID) AS Bed, * FROM Trips T
I get the result:
Bed TripID DateFrom DateTill
512014-05-12 00:00:002014-05-14 00:00:00
722014-05-15 00:00:002014-05-18 00:00:00
432014-05-12 00:00:002014-05-18 00:00:00
242014-05-12 00:00:002014-05-25 00:00:00
252014-05-05 00:00:002014-05-18 00:00:00
162014-05-05 00:00:002014-05-11 00:00:00
372014-05-05 00:00:002014-05-07 00:00:00
082014-05-08 00:00:002014-05-11 00:00:00
But how to go from here to get the Occupied (at the Location) amount?
Any help on how to approach this challenge is greatly appreciated.
May 22, 2014 at 12:10 pm
Sounds like a classic Gaps and Islands problem so you might want to look at this article[/url]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 22, 2014 at 1:48 pm
Hi
Here's how I would do it, however there is likely to be better ways:-)
It doesn't return the expected results that you have posted, but they don't match the rules you described
WITH cteTally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e1(N) ,
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e2(N)
),
BookingsPerTrip AS (
SELECT t.dateFrom, t.dateTill, b.bed
FROM trips t
CROSS APPLY (SELECT COUNT(*) bed FROM Bookings bk WHERE t.tripID = bk.tripID) b
),
BedPerDay AS (
SELECT DATEADD(day, N, mdf) bookingDay, b.totalBed
FROM (SELECT MIN(DateFrom) mdf, MAX(DateTill) mdt FROM Trips) d
CROSS APPLY (SELECT TOP (DATEDIFF(day,mdf,mdt) + 1) N - 1 N FROM cteTally) x
CROSS APPLY (SELECT SUM(Bed) totalBed FROM BookingsPerTrip WHERE DATEADD(day, N, mdf) between dateFrom and dateTill) b
)
SELECT t.tripID, b.totalBed
FROM Trips t
INNER JOIN BedPerDay b ON t.dateFrom = b.bookingDay
ORDER BY t.tripID;
May 22, 2014 at 2:39 pm
Jack,
Thank you for your answer.
But I don't think it is the classic gaps and islands problem.
Still I'll have a look at the article you recommended, maybe there is something in the article I can use.
Btw. I read the link on Forum etiquette you gave. I thought I posted the code and data in the right way, but the link showed me an easier and better way to post these sql.
May 22, 2014 at 3:03 pm
mickyT (5/22/2014)
Here's how I would do it, however there is likely to be better ways:-)It doesn't return the expected results that you have posted, but they don't match the rules you described
Thanks mickeyT for your solution.
I tried the sql, and it gave me:
tripID totalBed
113
215
313
413
56
66
76
83
As these figures look almost correct, there is difference at trip 3, 4 and 5.
The values ought to be 15, not 13 or 6.
Let me explain this, e.g. using "trip 3 from 12 till 18 may":
It has 4 bookings, but at the end of the week trip 2 has 7 bookings. So in this week at the end I need 4 + 7 = 11 beds.
Adding the bookings of trip 4 and 5 (2 + 2), I need 15 beds.
But I thank you for your effort and the time you took to help me.
Is there anyone who has a solution?
May 22, 2014 at 3:20 pm
So if I'm understanding this correctly, you would like the maximum occupancy at any point in the trip rather than the occupancy at the start of the trip?
Here's a revised version. I've made a couple of other changes to improve the performance slightly. You will need to look at indexing as well. I created PKs, an index across the dates and an index for the tripID in bookings.
WITH cteTally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e1(N) ,
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e2(N)
),
BookingsPerTrip AS (
SELECT t.dateFrom, t.dateTill, COUNT(*) bed
FROM trips t
INNER JOIN Bookings b ON t.tripID = b.tripID
GROUP BY t.datefrom, t.datetill
),
BedPerDay AS (
SELECT DATEADD(day, N, mdf) bookingDay, SUM(Bed) totalBed
FROM (SELECT MIN(DateFrom) mdf, MAX(DateTill) mdt FROM Trips) d
CROSS APPLY (SELECT TOP (DATEDIFF(day,mdf,mdt) + 1) N - 1 N FROM cteTally) x
INNER JOIN BookingsPerTrip b ON DATEADD(day, N, mdf) between dateFrom and dateTill
GROUP BY N, mdf
)
SELECT t.tripID, MAX(b.totalBed) totalBed
FROM Trips t
INNER JOIN BedPerDay b ON b.bookingDay BETWEEN t.dateFrom and t.dateTill
GROUP BY t.tripID
ORDER BY t.tripID;
May 24, 2014 at 9:08 am
mickyT (5/22/2014)So if I'm understanding this correctly, you would like the maximum occupancy at any point in the trip rather than the occupancy at the start of the trip?
Yes, I'm sorry for my inadequate explanation.
Here's a revised version. I've made a couple of other changes to improve the performance slightly.
Thank you, the code works fine, it gives exactly the result I want to show.
But it's a bit too hard for me to understand at a glance. I'm working on it.;-)
If I don't succeed, would you explain to me what this code does in outline?
You will need to look at indexing as well. I created PKs, an index across the dates and an index for the tripID in bookings.
Sure, the example code is a simplification from a real world database, correctly designed with PK's, FK's, indexes, etc. See e.g. the tab dates & prices (data & prijzen) at http://vinea.nl/7-10-jaar/actie-en-avontuur/kids-adventure-7-10
June 4, 2014 at 6:05 am
[font="Tahoma"]Table Value Constructor not available in SqlServer2005[/font]
I managed to understand the code and to incorporate the example in my (far much more complex) code.
The first part:
WITH cteTally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e1(N) ,
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e2(N)
),
is a 100 numbers Tally table made on the fly, using a Table Value Constuctor (I had never seen this before).
I enhanced the code so it gave me 400 numbers (sufficient for a whole year).
But when I tried to install the query from my Developer SqlServer2012 machine to the SqlServer 2005 production environment it didn't work.
I found out that on the fly creation of a Tally table with a Table Value Constructor is not possible in SqlServer2005.
Is there another solution possible in 2005?
June 4, 2014 at 6:25 am
Sorry, I found the solution myself in an article from Jeff Moden:
"Generation of testdata Part 1 - Generating Random Integers and Floats"
http://www.sqlservercentral.com/articles/Data+Generation/87901/
SELECT TOP (400)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns
This also works in SqlServer 2005 🙂
June 4, 2014 at 12:59 pm
Good to hear you worked it out 🙂 Another option is to build a physical Tally[/url] table or use a construct like the following
WITH base10 (N) AS (
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM base10 a, base10 b, base10 c -- 10 x 10 x 10 = 1000
)
SELECT TOP 50 N FROM cteTally;
June 4, 2014 at 1:42 pm
Yeah, a physical Tally table is a good option.
But I like the solution you gave me, a "Tally table on the fly". 😉
I met another problem when I tried to wrap the code in a VIEW.
First I did it the visual way, in SSMS (sql server management studio).
But SSMS-2005 dropped dead on this code, I think on the Diagram and Graphical presentation.
I had to restart SSMS. Lucky for me SqlServer itself, stayed alive: production environment !
But it was a shock, to see an empty screen after I hit the Execute button.
After that I made the View in sql-code, and then all went well. 😎
I thank you again for the wonderful solution you gave me. I learned a lot when I tried to figure out the way it works.
And everybody is happy: with this code installed, there is no "over-booking" any more. :satisfied:
June 4, 2014 at 1:52 pm
I avoid that create/design view window myself. It has a tendency to get itself mucked up and I don't particularly like the way it formats things. Though I haven't really looked at it for a while.
Happy to hear all is good:-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply