Improve speed in occupancy calculation

Hi Anonymous,

You can create a separate calendar table that lists every hour in the period you want to analyze. This table is much smaller than expanding all hospitalizations into hourly rows. Then, for each hour in the calendar, you look up and match only the hospitalizations where the patient was actually present during that hour, that is, where the hospitalization start time is before or equal to the hour and the end time is after it. This avoids unnecessary expansion and keeps the dataset manageable. Once you have matched the hospitalizations to each hour, you can expand and group the data by hour and department, counting how many hospitalizations overlap each hour to calculate occupancy. This method is flexible, allowing you to adjust the time period and increment (like hourly or half hourly), and it dramatically reduces the number of rows and memory load compared to expanding every hospitalization for its full duration. Filtering the calendar to the exact range needed and using techniques like buffering can further improve performance.

Please refer to below similar thread solved in community and let me know if this helps?

Solved: Calculating hourly occupancy of a medical clinic (... - Microsoft Fabric Community

Thanks,

prashanth

MS fabric community support