Sleekplan Logo
we run on Sleekplan

Daily Shelter Occupancy Report

There is a problem with the **Daily Shelter Occupancy HIFIS Report** in that the numbers do not match with other reports that they are using and I believe I've identified the cause. The formula used in this report goes like this: (SELECT Count(*) FROM vw_StaysSummary WHERE convert(date, vw_StaysSummary.StayDateStart) <= CurrentDate AND (vw_StaysSummary.StayDateEnd IS NULL OR convert(date, vw_StaysSummary.StayDateEnd) > CurrentDate) AND vw_StaysSummary.OrganizationID IN {?ServiceProvider}) and it is used to determine the number of stays on a particular day. Note that **CurrentDate** is a date variable, not a datetime variable. The problem with this is that it's an incorrect calculation. Let's imagine that I want to know who stayed in my shelter on January 22. While my words might say "who stayed on Jan 22" what I really mean is "who stayed **on the night of** Jan 22." In other words, I only want to know whose stay occurred at the end of Jan 22 and/or the morning of Jan 23. However, the above formula is just checking who stayed on the calendar date of Jan 22, which includes the night before and the night after the day of Jan 22. Shelters operate overnight, so the calendar date of Jan 22 includes both the night of Jan 21 and the night of Jan 22. In an extreme example, let's imagine that I had 10 people stay in my shelter on the night of Jan 21. They all book in at 20:00 Jan 21, and then they all book out at 06:00 Jan 22. There's even a button in HIFIS 4 that allows this, it's called Block Book Out. Then 10 people show up and book in at 20:00 Jan 22 and then book out at 06:00 Jan 23. The report will indicate that there were 20 stays on Jan 22, when the answer should clearly be 10.