1 of 1 people found this helpful
I would start with a single calc that determines the "block" then use that block number.
1 of 1 people found this helpful
It looks like I will need to create 96 Calculation fields to address each 15 minute time block and then 96 summary fields to count the totals. Is there a better way and what is it?
I believe you should define a Slots table with 96 records. Have each record calculate its start and end timestamps using a global view date and a serial number. Use a relationship to summarize the incidents related to the calculated slot.
Michael, thank you but I am not understanding the concept of a Slots table. Any chance you could explain a little more. I don't know if I explained what I am trying to do correctly. We might have up to 9 or 10 units assigned to single emergency or as little as 1 or 2 units on the average of 40 to 50 fire, medical, special operations and other type of emergency calls during a 24 hour period. We hope to look at the data daily, monthly, yearly and so on with a number count and visual hi-lighting showing us how many of our units are busy during any 15 minute block during the 24 hour period.
Thanks and Sorry for my senior moments...
Do you mean you just want to summarize the events by the 15 minute blocks? For example:
Date: March 10, 2012
00:00 - 00:15: 6 events
00:15 - 00:30: 4 events
00:30 - 00:45: 9 events
23:45 - 00:00: 7 events
Date: March 11, 2012
00:00 - 00:15: 3 events
I attached a pdf of what I started to work on that shows what I am trying to do. I need to display each emergency call with some info and then indicate when the unit was busy. We could have a unit busy for 5 minutes or they could be tied up on the call for hours, and crossing over midnight. I hope the attached pdf explains thing a little better. I needed to split the pdf to 2 pages.
Test_Status Copy1.pdf 32.7 K
To get that format, you would indeed need 96 calculation fields (or a repeating calculation field with 96 repetitions).
The other issue is the events that crossed midnight: suppose you have an event from Sunday 22:00 until Monday 02:00. When you do a find for events on Sunday, this event will incorrectly contribute to the morning columns; when you report on Monday, the same event will incorrectly add to the evening columns.
I spent the past couple of days researching and attempting to build a calculation formula to compare date and time ranges.
What I am having trouble with is a formula that is able to compare a range ( Start Timestamp and an End Timestamp against another block range and if so, calculate a value of 1 [Number] ).
Example 3/1/2012 00:05:50 Start TS
3/1/2012 00:21:39 End TS
How to determine if the Start TS or End TS is in the first time block range (1 to 900) seconds? Also, the second Time Block range would need to evaluate this as being in the range.
First Time Block Start Range would be 3/1/2012 00:00:01
First Time Block End Range would be 3/1/2012 00:15:00
Second Time Block Start Range would be 3/1/2012 00:15:01
Second Time Block End Range would be 3/1/2012 00:30:00
I keep running into a dead end, as you can see above, the Start TS is > than the first block begin time and the End TS is > than the end time block range. I have 96 block ranges for the 24 hours starting a 1 second ending at 86400 before a date change. I have tried to use the Timestamps as number but still no luck… I am so lost with this…
What you want to test is the following:
For the 1st time block (0:00-0:15),
( Time( Start_TS ) <= Time( 0 ; 15 ; 0 ) ) and ( Time( End_TS ) > Time( 0 ; 0 ; 0 ) )
2nd block: (0:15:00 - 0:30:00)
( Time( Start_TS ) <= Time( 0 ; 30 ; 0 ) ) and ( Time( End_TS ) > Time( 0 ; 15 ; 0 ) )
3rd block: (0:30:00 - 0:45:00
( Time( Start_TS ) <= Time( 0 ; 45 ; 0 ) ) and ( Time( End_TS ) > Time( 0 ; 30 ; 0 ) )
With the proper modification, the above could be placed in a single repeating field (with 96 repetitions) - soyou would not need to create 96 separate calculations.
As Michael Horak mentioned, since the Event can start on one date and end on another date, the "15 minute array" information will be incorrect for the (Start) Date. In such cases, you could split the Event into 2 (or more) Events, each covering just a single date; that is, the Start_Date and End_Date would be the same. FOr example, if an Event starts at 3/11/2012 22:18:03 and ends on 3/12/2012 03:12:15, you could split this event into 2 events: one event from 3/11/2012 22:18:03 to 3/11/2012 23:59:59 and the 2nd event from 3/12/2012 00:00:00 to 3/12/2012 03:12:15. Doing so would properly report each day's events and totals correctly.
On the other hand, if you acknowledge that any Event that starts on one day and ends on the next day will be counted solely in the Starting Date's totals, then you can do as you are planning.
Also note that, depending on all your reporting requirements (and we have seen only 1 or 2 of them here), a different approach may be needed to handle all requirements. Remember that just because you get the information in the format from the Call Center does not mean that you are restricted to using that format.
I've included a quick demo that implements a report similar to the one you showed in your pdf. Good luck!
Jason L. DeLooze
Thanks Jason, I tried your formula but was unable to get it to work. I created and attached a very simple test db and documented the field definitions. If you could look and see what I am doing wrong I would appreciate it. I will take a look at what you just sent while I was writing this. Thanks-Mike
Unit_Status.fp7.zip 10.8 K
Please address the issue of events crossing midnight. I could simply fix your calculations, but it seems pointless if you want accurate results.
Jason & Michael,
Your advice and the CrewLog file is outstanding and looks like this will solve the issue and I will test it for the next week or so with live data.
Thanks so much for all your help,