Calculate Totals per each Month based on Start and End date
I am calculating a Length of Stay (LOS), for which I use 2 fields (start_date & end_date) and the following logic.
end_date - start_date = Length of Stay (LOS in days).
The following is an example of my table and results.
What I need is to calculate the number of LOS days occurred in each month. For example. ID 3, has an LOS 30 days, but in 20 of the days occurred in the month May and 10 days occurred in the month June.
I believe my end result should be a table like this
Which would allow to me to calculate that the Total Patient Stay days in May = 20 and in June = 16.
I am not sure how to tackle this problem.
If anyone can help, that would be greatly appreciated.