## All we need is an easy explanation of the problem, so here it is.

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.

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 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.

**Note: Use and implement method 1 because this method fully tested our system.Thank you 🙂**

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0