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.