Time slots to dates
I am working in the Healthcare sector. We have a Vertica Analytics database containing a lot of data. One thing is Capacity Management. Here we have beds with an ID, Status, a start and stop time for that status.
Now I want to aggregate the time each bed had the different statuses by Bed (ID), Status and Date.
The start and stop is timestamp and I can use TIMESTAMPDIFF to calculate the time between the start and stop time.
But how can I aggregate to see each Bed, Status and the number of seconds (or minutes) . If a Bed was occupied for several Days we can have a start time in the afternoon Day 1 and an end time in the morning several Days later. I want to see how much time belongs to each date in that intervall.