Distribute Guests over Rooms Evenly

Question

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

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.

Answer 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.