I'm creating a dashboard to display the future occupancy of a hotel's apartments, based on information from the reservations that have been made in the system. But I found myself in a problem that I still haven't found a solution. When I load the system data that are saved in the DB SQL SERVER, the table that has the reservation information returns the following data.
Each ID shown in the image above represents a reservation, then the arrival date and departure date and finally the number of adults and children that are in this reservation. To create this availability panel, I need to distribute the number of adults and children over the period they stayed, and on the day of departure these people cannot be counted. I'm using two reservations below for example.
On the 04/03th, 8 adults from the ID 5 reservation entered and 4 adults from the ID 6 reservation entered
On the 04/03th, 8 children from the ID 5 reservation entered and 3 children from the ID 6 reservation entered
Adults = 8+4=12
Children = 8+3=11
Result of the day 04/03 - 12 Adults and 11 Children
On 05/04 - There was no entry and exit, so I will only count who is already staying
On 06/04 the ID 6 reservation came out so I have to subtract the amount of children and adults
Adults = 12-4=8
Children = 11-3=8
Result of the day 06/03 - 8 Adults and 8 Children
On 06/04 the ID 5 reservation came out so I have to subtract the amount of children and adults
Adults = 8-8=0
Children = 8-8=0
Result of the day 07/03 - 0 Adults and 0 Children
Is it possible to do this data modeling within BP?
Thank you for your help