Distribute Guests over Rooms Evenly

All we need is an easy explanation of the problem, so here it is.

I have a table of packages:

create table Package (
  PackageID int,
  Guests int,
  Rooms int
);
insert into Package(PackageId, Guests, Rooms) values
  (1, 1, 1),
  (2, 2, 1),
  (3, 2, 2),
  (5, 3, 2),
  (6, 3, 3),
  (8, 4, 2),
  (9, 4, 3),
  (10, 4, 4)
;

I’m trying to generate a series of Occupancy (PackageID, RoomID, Guests) records by distributing the Guests over the Rooms evenly.

I’ve made several attempts – such as / towards:

select p.PackageID, p.Guests, p.Rooms, r.RoomID, r.Guests as GuestsInRoom
  from Package p
  cross apply (
    select 
      v.[number] + 1 as [RoomID],
      // Guests in room?
      from master..spt_values v
        where v.name is null and v.number<p.Rooms
  ) r

… with no success so far.

I’ve previously attempted to use lag() to distribute the guests. But it does not work for some specific cases:

select p.PackageID, p.Guests, p.Rooms, r.RoomID,
  lag(
    convert(int, ceiling((p.Guests+0.0)/p.Rooms)), 
    1, 
    convert(int, ceiling((p.Guests+0.0/p.Rooms)))
    over (partition by p.PackageID order by r.RoomID
    - case when (r.RoomID=max(RoomID) from #packageRooms pr where pr.PackageID=p.PackageID) and (p.Guests % p.Rooms) > 0 then 1 else 0 end as GuestsInRoom
  from Package p
    inner join #packageRooms r
      on p.PackageID=r.PackageID

Distribute Guests over Rooms Evenly

Here, in total five people are assigned to three rooms whilst there are just four guests in package #9. (It works fine, though, for many/most other packages.)

Basically, I can’t find a nice way of saying "I’ve got four guests to distribute over three rooms."

Note: Each room as a max occupancy of two guests.

NB: Sorry – I’ve somehow lost track of all the the loose ends of my different attempts. Leaving me right now with (broken) code and an image not matching perfectly right now.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You could try along:

;with
Occupancy as (
  select
    p.PackageId,
    p.Guests,
    p.Rooms,
    r.RoomID,
    case when r.RoomId < p.Guests - p.Rooms + 1 then 2 else 1 end as GuestsInRoom,
    case when r.RoomId < p.Guests - p.Rooms + 1 then 2 when r.RoomId > p.Guests then 0 else 1 end as GuestsInRoomWithSparesAndMissing
  from Package p
  cross apply (
      select 
      v.number + 1 as RoomID
      from master.dbo.spt_values v
      where v.name is null and v.number < p.Rooms
  ) r
),
OccupancyCheck_ as (
  select
    PackageId,
    sum(GuestsInRoomWithSparesAndMissing) as NumberOfGuestsServed,
    count(RoomID) as NumberOfRoomsUsed
  from Occupancy
  group by PackageId
),
OccupancyCheck as (
  select top 100000
    p.PackageId,
    p.Guests,
    p.Rooms,
    c.NumberOfGuestsServed,
    c.NumberOfRoomsUsed
  from Package p
  join OccupancyCheck_ c
    on p.PackageId = c.PackageId
  order by p.PackageId
)
select
  * from Occupancy
  --* from OccupancyCheck
;

See it in action: SQL Fiddle – It has more examples to demonstrate come corner cases – more rooms than people and more people than two times the number of rooms…

Please comment, if and as this requires adjustment / further detail.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply