Occupancy % by hour

[Josh_I]

Hello All, 

I am trying to get the hourly occupancy.

I the have a start date / start time and end date / end time of each transaction (see example of dataset below). The total stalls per Lot is in a different table (REVLOTS [StallCount])

Appreciate the help!

[kentyler]

You might be able to use a calander table and an hour table (hours in a day).

Then it might help to change the granularity of your data and turn it into a set of records that records one record for each hour of occupancy.

Then your calculations of percent of occupancy should be much simpler.

[Josh_I]

What would be the best way to change the granularity of each transaction to show it per hour instead of the time range?

[kentyler]

for example, your first record, that goes from 2 pm one day to 12 pm the next would expand into 22 records, one for each day/hour of occupancy.