June 26, 2007 at 2:14 pm
Greetings,
I'm in charge of creating reports for a multi-agency fire department that shows averages of Fire Truck response times. For example, last month, what was the average "Enroute" time for each fire truck; what was the average "Dispatched to OnScene" for each truck, etc...
The table that the data is stored in is in one single table in the (summarized) following format:
Year IncdNo Truck Action Date/Time
07 123 E121 Dispatched 1/1/07 12:34:30
07 123 L121 Dispatched 1/1/07 12:34:30
07 123 E121 Enroute 1/1/07 12:36:00
07 123 L121 Enroute 1/1/07 12:36:13
07 123 E121 On Scene 1/1/07 12:40:00
07 123 E121 On Scene 1/1/07 12:40:12
07 123 L121 On Scene 1/1/07 12:40:30
07 123 E121 In Service 1/1/07 13:03:23
07 123 L121 In Service 1/1/07 13:04:00
07 124 E121 Dispatched 1/1/07 16:14:30
07 124 E121 Enroute 1/1/07 16:16:00
07 124 E121 Enroute 1/1/07 16:16:19
07 124 E121 On Scene 1/1/07 16:20:00
07 124 E121 On Scene 1/1/07 16:20:12
07 124 E121 In Service 1/1/07 17:00:23
What I'm looking to do is put the above data is a Cross-Tab style layout (maybe in a table???) so that I can run calculations on the time differences. What I need is something like the following...
Year IncdNo Truck Dispatched Enroute On Scene In Service
07 123 E121 12:34:30 12:36:00 12:40:00 13:03:23
07 123 L121 12:34:30 12:36:13 12:40:30 13:04:00
07 124 E121 16:14:30 16:16:00 16:20:00 17:00:23
What I want to do is create colums (in a view) at the end of each row and calculate differences of needed times (such as the difference between "Dispatched" - "Enroute" and "Dispatched" - "On Scene", so I can calculate averages over a long period of time (month, year, etc.) My question is how do I (can I) get the data in the above format? I can get the data into a Cross-tab report in Crystal Reports so it looks like the data above, but I don't think I can get the averages/calculations that I'm looking for.
This is all further complicated by the fact that there are some double entries for some of the trucks, like E121 has 2 On Scene times for #123 and 2 Enroute times for #124. I got the desired result by taking the "Minimum" date/time when making the cross-tab report.
Thanks for any help/input that you might have!
J Miller
June 26, 2007 at 2:39 pm
declare
@sample table (year char(2), incdno tinyint, truck char(4), action varchar(10), dt datetime)
insert
@sample
select
'07', 123, 'E121', 'Dispatched', '1/1/07 12:34:30' union all
select
'07', 123, 'L121', 'Dispatched', '1/1/07 12:34:30' union all
select
'07', 123, 'E121', 'Enroute', '1/1/07 12:36:00' union all
select
'07', 123, 'L121', 'Enroute', '1/1/07 12:36:13' union all
select
'07', 123, 'E121', 'On Scene', '1/1/07 12:40:00' union all
select
'07', 123, 'E121', 'On Scene', '1/1/07 12:40:12' union all
select
'07', 123, 'L121', 'On Scene', '1/1/07 12:40:30' union all
select
'07', 123, 'E121', 'In Service', '1/1/07 13:03:23' union all
select
'07', 123, 'L121', 'In Service', '1/1/07 13:04:00' union all
select
'07', 124, 'E121', 'Dispatched', '1/1/07 16:14:30' union all
select
'07', 124, 'E121', 'Enroute', '1/1/07 16:16:00' union all
select
'07', 124, 'E121', 'Enroute', '1/1/07 16:16:19' union all
select
'07', 124, 'E121', 'On Scene', '1/1/07 16:20:00' union all
select
'07', 124, 'E121', 'On Scene', '1/1/07 16:20:12' union all
select
'07', 124, 'E121', 'In Service', '1/1/07 17:00:23'
SELECT
Year,
IncdNo
,
Truck
,
MAX(CASE WHEN Action = 'Dispatched' THEN CONVERT(char(8), dt, 108) END) AS Dispatched,
MAX(CASE WHEN Action = 'Enroute' THEN CONVERT(char(8), dt, 108) END) AS Enroute,
MAX(CASE WHEN Action = 'On Scene' THEN CONVERT(char(8), dt, 108) END) AS [On Scene],
MAX(CASE WHEN Action = 'In Sevice' THEN CONVERT(char(8), dt, 108) END) AS [In Service]
FROM
@Sample
GROUP
BY Year,
IncdNo
,
Truck
ORDER
BY Year,
IncdNo
,
Truck
N 56°04'39.16"
E 12°55'05.25"
June 27, 2007 at 4:05 pm
Thank You, Exactly what I was looking for...
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply