I have a table, say:

create table Package (
  PackageID int identity(1,1),
  Guests int,
  Rooms int
)

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

I've made several attempts by doing something like:

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
  )

I've previously attempted to use lag() to distribute the guests, but it messes up for some edge 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

I can't find a nice way of saying "I've got 3 guests to distribute over 2 rooms".

Note: Each room as a max occupancy of 2 guests

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.

I have a table, say:

create table Package (
  PackageID int identity(1,1),
  Guests int,
  Rooms int
)

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

I've made several attempts by doing something like:

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
  )

I've previously attempted to use lag() to distribute the guests, but it messes up for some edge cases:

I can't find a nice way of saying "I've got 3 guests to distribute over 2 rooms".

Note: Each room as a max occupancy of 2 guests

I have a table, say:

create table Package (
  PackageID int identity(1,1),
  Guests int,
  Rooms int
)

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

I've made several attempts by doing something like:

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
  )

I've previously attempted to use lag() to distribute the guests, but it messes up for some edge 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

I can't find a nice way of saying "I've got 3 guests to distribute over 2 rooms".

Note: Each room as a max occupancy of 2 guests