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:

[![Edge case - 4 guests in 3 rooms][1]][1]

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


  [1]: https://i.sstatic.net/iEQq7.png