Room booking system - calculating room occupancy

  • andrew.robinson 9160

    Old Hand

    Points: 336

    thumbnailHi 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:

    1. A room can be booked my two different people at the same time but only counts the same as being occiplied by one person.
    2. We only want to calculate occupancy from 08:30 to 16:00.

    Thankyou

    Andy

     

     

    Attachments:
    You must be logged in to view attached files.
  • pietlinden

    SSC Guru

    Points: 62820

    Post your SQL statements here... nobody's going to download files from people they don't know.

  • Jeff Moden

    SSC Guru

    Points: 996689

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • pietlinden

    SSC Guru

    Points: 62820

    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?

     

     

    • This reply was modified 3 months, 3 weeks ago by  pietlinden. Reason: my code was wrong
  • andrew.robinson 9160

    Old Hand

    Points: 336

    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

     

     

     

  • as1981

    SSCrazy

    Points: 2741

    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

     

  • pietlinden

    SSC Guru

    Points: 62820

    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?

  • as1981

    SSCrazy

    Points: 2741

    andrew.robinson 9160 wrote:

    Hi all,

    Two things to bear in mind are:

      <li style="list-style-type: none">

    1. A room can be booked my two different people at the same time but only counts the same as being occiplied by one person.
    2. 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?

     

  • pietlinden

    SSC Guru

    Points: 62820

    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.

  • andrew.robinson 9160

    Old Hand

    Points: 336

    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.

     

     

     

  • as1981

    SSCrazy

    Points: 2741

    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,date

    I still get 6.5, and not 5.5, for the first value though?

  • Jacob Wilkins

    One Orange Chip

    Points: 27886

    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!

  • ScottPletcher

    SSC Guru

    Points: 98448

    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_

    • This reply was modified 3 months, 3 weeks ago by  ScottPletcher. Reason: Added comment about tally table size
    • This reply was modified 3 months, 3 weeks ago by  ScottPletcher. Reason: Changed the tally table to be 10K rows instead of 1000

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • andrew.robinson 9160

    Old Hand

    Points: 336

    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

  • andrew.robinson 9160

    Old Hand

    Points: 336

    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

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply