January 4, 2017 at 9:42 am
I have the following query and results. I need to group them further such that Adam 1 thru Adam 5 are combined into one group with that combined average etc. I'm stuck
Select Battalion, concat (Minutes, ':', Seconds) as "Minutes:Seconds"
From
(Select Battalion, (ResponseTime/60) as 'Minutes', (ResponseTime%60) as 'Seconds'
From
(Select Battalion, AVG(DATEDIFF (ss,Time_First_Unit_Assigned, Time_First_Unit_Arrived)) as ResponseTime
From Response_Master_Incident
Where Response_Date Between '2016/12/01 00:00:00' and '2016/12/20 23:59:59'
and (Time_First_Unit_enroute is Not null and Time_First_Unit_Arrived is Not null and Elapsed_Enroute2FirstAtScene > '00:00:00'
and (Time_First_Unit_Assigned < Time_First_Unit_Arrived))
and (Battalion like 'cpd A%' or Battalion like 'cpd b%' or Battalion like 'cpd C%')
and Call_Disposition <> 'D06_In Service Duplicate Event'
Group By Battalion) s) s
BattalionMinutes:Seconds
CPD Adam 112:42
CPD Adam 1010:18
CPD Adam 1110:7
CPD Adam 1211:14
CPD Adam 1313:36
CPD Adam 213:38
CPD Adam 310:17
CPD Adam 411:41
CPD Adam 513:57
CPD Adam 69:5
CPD Adam 79:32
CPD Adam 88:3
CPD Adam 98:33
CPD Baker 111:20
CPD Baker 210:14
CPD Baker 38:13
CPD Baker 410:53
CPD Baker 510:11
CPD Baker 68:47
CPD Baker 711:20
CPD Baker 89:47
CPD Baker 912:24
CPD Charlie 113:36
CPD Charlie 212:31
CPD Charlie 318:26
CPD Charlie 413:46
CPD Charlie 514:7
CPD Charlie 613:40
CPD Charlie 718:23
CPD Charlie 815:15
CPD Charlie 914:15
January 4, 2017 at 9:55 am
Lots of issues/questions here
1) You cannot do an average of an average, which it seems you are asking for.
2) You want Adam 1 through Adam 5 combined. What rules allow us to determine that? What about the other Adam NN rows, and the Bakers and Charlies?
3) Please provide a create table statement, INSERT statements with all necessary sample data to cover all cases and expected output from that sample data. Help us help you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 4, 2017 at 10:14 am
We definitely need DDL and DLM for this one and more logic. As Kevin said, why Adam 1 - 5. What about Adam 6?
I've given you a sample DDL, which I would suggest is not correct, however, I have made your SQL statement into something that didn't cause my eyes to bl,eed:
USE DevTestDB;
GO
CREATE TABLE Response_Master_Incident (Battalion VARCHAR(3),
Time_First_Unit_Assigned DATETIME,
Time_First_Unit_Arrived DATETIME,
Elapsed_Enroute2FirstAtScene TIME,
Time_First_Unit_enroute TIME,
Response_Date DATE,
Call_Disposition VARCHAR(100));
GO
SELECT ss.Battalion,
concat (ss.[Minutes], ':', ss.Seconds) AS [Minutes:Seconds]
FROM (SELECT s.Battalion,
(s.ResponseTime/60) AS [Minutes],
(s.ResponseTime%60) AS [Seconds]
FROM (SELECT RMI.Battalion,
AVG(DATEDIFF (SECOND,RMI.Time_First_Unit_Assigned, RMI.Time_First_Unit_Arrived)) AS ResponseTime
FROM Response_Master_Incident RMI
WHERE RMI.Response_Date BETWEEN '2016/12/01 00:00:00' AND '2016/12/20 23:59:59'
AND RMI.Time_First_Unit_enroute IS NOT NULL
AND RMI.Time_First_Unit_Arrived IS NOT NULL
AND RMI.Elapsed_Enroute2FirstAtScene > '00:00:00'
AND RMI.Time_First_Unit_Assigned < RMI.Time_First_Unit_Arrived
AND (RMI.Battalion LIKE 'cpd A%'
OR RMI.Battalion LIKE 'cpd b%'
OR RMI.Battalion LIKE 'cpd C%')
AND RMI.Call_Disposition <> 'D06_In Service Duplicate Event'
GROUP BY RMI.Battalion) s) ss
GO
DROP TABLE Response_Master_Incident;
GO
It seems that you really using the subselects to create your presentation data. You should be doing that in your presentation layer, not your SQL. Either way, the subselects are redundant as you don't need to do the formatting over 3 layers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 4, 2017 at 12:04 pm
"You cannot do an average of an average, which it seems you are asking for."
That basically answered my question. I'll take a different direction with this.
January 4, 2017 at 12:07 pm
Unfortunately this is not my database to design, just query. The Elapsed_enroute2FirstAtScene coloumn is already establish as VARCHAR. Nothing I can do about that so I have to re-calculate it. Without all the Wheres in there I get a variety of negative results which I have to filter out and this was what I ended up with that gave me realistic results. I am basically completely self taught at this and thrown into the fire so doing the best I can.
I did clean up the first two selects into a single CONCAT though
Thanks for the helps.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply