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 (
      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,
    convert(int, ceiling((p.Guests+0.0)/p.Rooms)), 
    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

Failed - trying to hosts four guests in three rooms

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.


You could try along:

Occupancy as (
    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 (
      v.number + 1 as RoomID
      from master.dbo.spt_values v
      where v.name is null and v.number < p.Rooms
  ) r
OccupancyCheck_ as (
    sum(GuestsInRoomWithSparesAndMissing) as NumberOfGuestsServed,
    count(RoomID) as NumberOfRoomsUsed
  from Occupancy
  group by PackageId
OccupancyCheck as (
  select top 100000
  from Package p
  join OccupancyCheck_ c
    on p.PackageId = c.PackageId
  order by p.PackageId
  * 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.

  • Thanks, this is a great implementation, I came to a similar solution a few days ago, so this is good that it aligns with my solution. Apparently, I can't upvote yet (not enough reputation), but please accept my e-thanks! Jul 16 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.