I'm trying to retrospectively (re)create a screen that is populated by a sql table (Activity_Log). I'm having some problems trying to figure out the logic/schema to make this work. I've come at this from a couple of angles and am stuck.
The screen is a vehicle resourse screen that shows where everyone is and what their status is currently. The fields are radio_name (the vehicle ID), activity (status), date_time (timestamp). I'm trying to capture this information at a particular point in time time_callenteredqueue. My problem is that the log table is a log and if I try to look at the data in the log at a particular point there may be one entry. I want to create something that shows what all of the units' statuses at that time.
10/10/2012 1:00:00 PM <- time_callenteredqueue
123 - Available - 12:50:00 PM (this is the time the unit went into the current status, but it is not necessary to show it)
456 - Enroute - 12:59:00 PM
789 - At Scene - 12:45:00 PM
Ideally these units would stay in this status until it changed in the log table. The units could always be there and only status(ed) when they had a status or a change. I've tried a portal of the activity_log table that looked back 15:00 but some of these units change status 2-4 times in 15:00.
Anyone have even a bit of guidance, I'm stuck.