Room booking system - calculating room occupancy
-
Hi all,We have a room booking system at work and i am trying to work set up a sql view that calculates occupancy.
I have tried to illustrate how the data appears on SQL MS in the image and the attachment - and the required outcome.
Two things to bear in mind are:
- A room can be booked my two different people at the same time but only counts the same as being occiplied by one person.
- We only want to calculate occupancy from 08:30 to 16:00.
Thankyou
Andy
- This topic was modified 4 years, 1 month ago by andrew.robinson 9160.
-
Post your SQL statements here... nobody's going to download files from people they don't know.
-
I recommend that you onvert that spreadsheet to actual INSERTs to a table to help us help you. Please see the first link in my signature line below for details.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Andy,
I created the table and added the insert scripts... (although the table really needs indexing!)
If you really need help, help us help you. Read this link: How to post code problems (from Jeff's post). If you post CREATE TABLE and INSERT scripts, people here can copy & paste into SSMS and recreate your scenario and get on with actually trying to solve your problem. The one thing we're still missing is your expected result.
SELECT x.RoomNo
, x.BookDate
, x.TimeSlot
, COUNT(*) AS BookingsInRoom
FROM
(
SELECT b.BookDate
, b.RoomNo
--, b.StartHour
--, b.FinishHour
--, b.Duration
--, hrs.hr
, TimeSlot = b.StartHour + hrs.hr
FROM
(SELECT RoomNo
, BookDate
, StartHour = DATEPART(HOUR,StartTime)
, FinishHour = DATEPART(HOUR,EndTime)
, Duration = DATEDIFF(hour,StartTime,EndTime)
FROM Bookings) b
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5)) hrs(hr)
WHERE hrs.hr < b.Duration
) x
WHERE x.TimeSlot <= 16
GROUP BY x.RoomNo
, x.BookDate
, x.TimeSlot
ORDER BY x.RoomNo
, x.BookDate
, x.TimeSlot;I think this is a screwy way of doing it, but I'm exploding the {StartTime, EndTime} into one hour time slots, and then grouping by date, time slot, and Room. The counts look high, but maybe it's too late at night for me, and I need to look at it in the morning.
Hopefully people with less time and more brains can help you now. Please read Jeff's article on how to post - he explains what we need to help you and how you should post if you want a tested answer.
Pieter
--- okay, I knew my initial result looked screwy. I somehow left the TimeSlot out of the totals query last time, but I fixed the SQL so this makes more sense. Two bookings at 14 and 15...
Is that what you were looking for?
-
Thank you pietlinden!
I will try that asap.
The expected results based on the example I've given would be:
ROOM DATE OCCUPANCY (HRS)
A 22/11/19 5.5
B 22/11/19 6
A 23/11/19 4
B 23/11/19 7.5
Also, apologies for the way I asked the question - I'm not too experienced on these forums.
I've also added the code below:
CREATE TABLE Bookings (
Room varchar(255),
Date_ date,
Booking_start_time time,
Booking_end_time time
);
INSERT INTO Bookings (Room, Date_, Booking_start_time, Booking_end_time)
VALUES
(
'A',
'2019-11-22',
'08:00',
'11:00'
),
(
'A',
'2019-11-22',
'10:00',
'12:00'
),
(
'A',
'2019-11-22',
'14:00',
'16:00'
),
(
'B',
'2019-11-22',
'09:00',
'11:00'
),
(
'B',
'2019-11-22',
'11:00',
'13:00'
),
(
'B',
'2019-11-22',
'14:00',
'16:00'
),
(
'A',
'2019-11-23',
'10:00',
'12:00'
),
(
'A',
'2019-11-23',
'10:00',
'13:00'
),
(
'A',
'2019-11-23',
'15:00',
'16:00'
),
(
'B',
'2019-11-23',
'08:00',
'11:00'
),
(
'B',
'2019-11-23',
'11:00',
'16:00'
),
(
'B',
'2019-11-23',
'14:00',
'18:00'
)
;?Thanks
Andy
-
pietlinden probably has a better solution than me. I'm also testing a solution but I can't work out why the value for Room A on the 22nd is 5.5? I've probably misread something but I can see the following three booking?:
8:00 - 11:00
10:00-12:00
14:00-16:00
That totals 7 hours so if I ignore the first 30 minutes of the first booking because it's before 8:30 I get 6.5 hours?
Thanks
-
How can you get a non whole number answer if everything starts and ends on the hour? Because each room is effectively for two meetings at once?
- andrew.robinson 9160 wrote:
Hi all,
Two things to bear in mind are:
- <li style="list-style-type: none">
- A room can be booked my two different people at the same time but only counts the same as being occiplied by one person.
- We only want to calculate occupancy from 08:30 to 16:00.
I think it's because of item 2 in the above list? The first 30 minutes of the 8am booking is ignored?
-
Oh, right! That's what I was missing. The times before 8:30 and after 16:00 don't count. That's where the half hour came from. I didn't chop those off. Explains why my numbers were wrong.
I would just use a CASE statement or IIF to modify those before doing the SUM and then it would work, I think. You'd have to use DATEDIFF and measure in minutes, though.
-
Hi pietlinden
Because we only want to calculate occupancy from 08:30 -16:00, (even though some meetings actually start at 08:00 - don't ask!!:-))
I've looked at your code (which was extremely useful!!) and I think I can adapt it for what I want. I am basically going to calculate it in 15 minute intervals as opposed to hourly intervals.
- pietlinden wrote:
Oh, right! That's what I was missing. The times before 8:30 and after 16:00 don't count. That's where the half hour came from. I didn't chop those off. Explains why my numbers were wrong.
I would just use a CASE statement or IIF to modify those before doing the SUM and then it would work, I think. You'd have to use DATEDIFF and measure in minutes, though.
Something like this should work?:
select room, date_, sum(datediff(minute, case
when booking_start_time <'08:30:00' then
'08:30:00'
else booking_start_time
end,
case
when booking_end_time>'16:00:00'
then '16:00:00'
else booking_end_time
end
)/60.0)
from bookings
group by room, date_
order by room,dateI still get 6.5, and not 5.5, for the first value though?
-
I still get 6.5, and not 5.5, for the first value though?
You're just adding up all the times, which doesn't give the correct number because of this requirement from the original post:
A room can be booked my two different people at the same time but only counts the same as being occiplied by one person.
The 8-11 and 10-12 bookings both include the hour from 10 to 11, and you're counting it twice instead of once.
Cheers!
-
I think this will give you what you need. I went down to the minute to allow bookings like '10:10' to '10:45'. You may not have that now, but it could happen in the future (or at least you should allow for it to happen in the future).
Edit: I just realized 1000 might not be enough minutes. Probably best to increase the tally to 10000, the WHERE clause will limit the results to what you actually need.
Edit2: I went ahead and changed the tally table to 10K rows instead of 1000.
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally10K AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3 CROSS JOIN cte_tally10 c4
)
SELECT Room, Date_, CAST(COUNT(*) / 60.0 AS decimal(4, 2)) AS hours
FROM (
SELECT DISTINCT Room, Date_, DATEADD(MINUTE, t.number, Booking_start_time) AS Booking_minute
FROM dbo.Bookings
INNER JOIN cte_tally10K t ON t.number BETWEEN 1 AND DATEDIFF(MINUTE, Booking_start_time, Booking_end_time)
WHERE DATEADD(MINUTE, t.number, Booking_start_time) BETWEEN '08:31' AND '16:00'
) AS derived
GROUP BY Room, Date_
ORDER BY Room, Date_SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Thanks for replying as1981,
The reason it is 5.5 hours is because there is a 1 hour overlap (10-11) where two bookings are in the same room at the same time, so although the total hours is 6.5, the room is only actually occupied for 5.5 hours.
Thanks
Andy
-
Thanks ScottPletcher,
I'll have a look at that.
That was where I was ultimately hoping to get to because as you say we do have bookings to the 5 minutes.
Andy
Hi all,Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply