Hi There,
I have been struggling day and night with the creation of a store procedure due to not being able to retieve the rows I need for SUM and AVG functions.
I have two tables ('actions' & 'incident_types') which both have a score value for each record. In my database, I have reports that contain both action codes and incident_type codes based on a personnel_code.
In simple terms I'm trying to do the following:-
For all reports, find each personnel member and thier attached incident_types and action codes and then, for each score from the actions and incident_types tables, create the SUM of the 'combined' scores.
I have successfully retrieved the output for the scores individually but I need the TOP(10) of the combined output.....
I'm currently using to seperate queries as follows :--
--This gives me the incident type output...... Creating the 'Volume' column which is the total Score for incidetn_types
SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(incident_types.type_score) as 'Volume', avg(incident_types.type_score) as 'Average' INTO 'Incident_Scores' from Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND report_basedon.personnel_code <> 0
AND report_header.record_status=@.recordStatus
group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname
-- I then use the following to get the total of all action scores, again in the 'Volume' column
SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(actions.action_score) as 'Volume', AVG(actions.action_score) as 'Average' from profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND personnel_details.personnel_code <> 0
AND report_header.record_status=@.recordStatus
AND profile.record_status=@.recordStatus
group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname
So my question is - How can I get the sum(incident_types.type_score) + sum(actions.action_score) and then get the TOP(10) rows?
Is it possible to output these 2 result to a new table and then join the new tables?
Thanks for any assistance, this is really draining me at the moment..... :-/
here it is,
Select TOP (@.Number)
incident_Data.personnel_code
, incident_Data.personnel_forename
, incident_Data.personnel_Surname
, incident_Data.Volume
, incident_Data.Average
, actions_data.Volume
, actions_data.Average
, incident_Data.Volume + actions_data.Volume as Total_Volume
, incident_Data.Average + actions_data.Average as Total_Average
from
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(incident_types.type_score) as 'Volume'
, avg(incident_types.type_score) as 'Average'
from
Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND report_basedon.personnel_code <> 0
AND report_header.record_status=@.recordStatus
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as incident_Data
Inner Join
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(actions.action_score) as 'Volume'
, AVG(actions.action_score) as 'Average'
from
profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND personnel_details.personnel_code <> 0
AND report_header.record_status=@.recordStatus
AND profile.record_status=@.recordStatus
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as actions_data
On actions_data.personnel_code = incident_Data.personnel_code
|||
Thank you so much for your reply this has really made my day!
I forgot to mention that the action record is not always present but the incident record is. Therefore I made the actions_data join = 'Left Outer Join' (see code below)... This gives me a problem in that when the action_data record contains 'NULL' the Volume is also 'NULL' - is it possible to make NULL = 0 [zero] when there is no action record?
Thanks again (see code and output).....
[code I've removed some of the selection criteria from previous post for testing purposes]
Select TOP (1000)
incident_Data.personnel_code
, incident_Data.personnel_forename
, incident_Data.personnel_Surname
, incident_Data.Volume
, incident_Data.Average
, actions_data.Volume
, actions_data.Average
, incident_Data.Volume + actions_data.Volume as Total_Volume
, incident_Data.Average + actions_data.Average as Total_Average
from
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(incident_types.type_score) as 'Volume'
, avg(incident_types.type_score) as 'Average'
from
Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE
report_basedon.personnel_code <> 0
AND report_header.record_status='1'
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as incident_Data
Left Outer Join
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(actions.action_score) as 'Volume'
, AVG(actions.action_score) as 'Average'
from
profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE
personnel_details.personnel_code <> 0
AND report_header.record_status='1'
AND profile.record_status='1'
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as actions_data
On actions_data.personnel_code = incident_Data.personnel_code
Order by incident_data.personnel_code
[example of results]
15 Oscar Freeman 0 0 NULL NULL NULL NULL16 Sofia Daniels 0 0 NULL NULL NULL NULL
19 Megan Hardy 25 12 120 40 145 52
20 Finley Randall -25 -25 NULL NULL NULL NULL|||
observations
1. since actions_data subquery may be missing [i.e. OUTER JOIN] you should use this line instead
, incident_Data.Volume + ISNULL(actions_data.Volume,0) as Total_Volume
, incident_Data.Average + ISNULL(actions_data.Average,0) as Total_Average
2. the Total_Average calculation is flawed, e.g.
4 rows of incident_Data with average of 50 and 1 row of actions_data with average of 20 would yield a Total_Average of 70
when what you probably wanted was 44 (or 35 for simplicity if you don't care about weighting)
Dick
|||Thank you Dick, for both points, your absolutly correct. I was so concerned with not being ale to extract the data that I hadn't thought this though. You've propably saved me a lot more pain!
Just for completeness, I had to go on and resolve a divide by zero error when calculating the final averages etc etc and I found a very useful link http://www.sql-server-helper.com/error-messages/msg-8134.aspx which shows 3 ways of dealing with this error.
Thanks again for everyones help - I can now get on with developing the program!!!!
|||
Yes. ISNULL will fix the issue... If the average is your issue the following query might be a best one..
Not sure why we need a TOP here.. It wont give any performance gain.
Select TOP (@.Number)
personnel_code
, personnel_forename
, personnel_Surname
, sum(case when type='incident' then Score end) as incident_volume
, avg(case when type='incident' then Score end) as incident_average
, sum(case when type='actions' then Score end) as actions_volume
, avg(case when type='actions' then Score end) as actions_average
, sum(Score) as Total_Volume
, avg(Score) as Total_Average
From
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, (incident_types.type_score) as 'Score'
, 'incident' Type
from
Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND report_basedon.personnel_code <> 0
AND report_header.record_status=@.recordStatus
UNION ALL
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, (actions.action_score) as 'Score'
, 'actions' Type
from
profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND personnel_details.personnel_code <> 0
AND report_header.record_status=@.recordStatus
AND profile.record_status=@.recordStatus
) as data
Group By
personnel_code
, personnel_forename
, personnel_Surname
