Jan 03 2013

Comment by Seth Spearman on Can SQL Server perform an update on rows with a set operation on the aggregate max or min value?

Guys...it should only drop ONE for high and ONE for low in the case of a TIE. So if my score for JUMPS are 7,8,9,9 it should get the average of 8 and 9. It does not matter WHICH one gets dropped as long as one of the 9s (but only 1) gets dropped from the aggregation.
Jan 03 2013

Can SQL Server perform an update on rows with a set operation on the aggregate max or min value?

I am a fairly experienced SQL Server developer but this problem has me REALLY stumped. I have a FUNCTION. The function is referencing a table that is something like this...
PERFORMANCE_ID, JUDGE_ID, JUDGING_CRITERIA, SCORE  
--------------------------------------------------  
101, 1, 'JUMP_HEIGHT', 8   
101, 1, 'DEXTERITY', 7  
101, 1, 'SYNCHRONIZATION', 6  
101, 1, 'SPEED', 9  
101, 2, 'JUMP_HEIGHT', 6   
101, 2, 'DEXTERITY', 5  
101, 2, 'SYNCHRONIZATION', 8  
101, 2, 'SPEED', 9  
101, 3, 'JUMP_HEIGHT', 9   
101, 3, 'DEXTERITY', 6  
101, 3, 'SYNCHRONIZATION', 7  
101, 3, 'SPEED', 8  
101, 4, 'JUMP_HEIGHT', 7   
101, 4, 'DEXTERITY', 6  
101, 4, 'SYNCHRONIZATION', 5  
101, 4, 'SPEED', 8  
In this example there are 4 judges (with IDs 1, 2, 3, and 4) judging a performance (101) against 4 different criteria (JUMP_HEIGHT, DEXTERITY, SYNCHRONIZATION, SPEED).
(Please keep in mind that in my real data there are 10+ criteria and at least 6 judges.) I want to aggregate the results in a score BY JUDGING_CRITERIA and then aggregate those into a final score by summing...something like this...
SELECT SUM (Avgs) FROM
(SELECT AVG(SCORE) Avgs 
  FROM PERFORMANCE_SCORES
  WHERE PERFORMANCE_ID=101
  GROUP BY JUDGING_CRITERIA) result 
BUT... that is not quite what I want IN THAT I want to EXCLUDE from the AVG the highest and lowest values for each JUDGING_CRITERIA grouping. That is the part that I can't figure out. The AVG should be applied only to the MIDDLE values of the GROUPING FOR EACH JUDGING_CRITERIA. The HI value and the LO value for JUMP_HEIGHT should not be included in the average. The HI value and the LO value for DEXTERITY should not be included in the average. ETC. I know this could be accomplished with a cursor to set the hi and lo for each criteria to NULL. But this is a FUNCTION and should be extremely fast. I am wondering if there is a way to do this as a SET operation but still automatically exclude HI and LO from the aggregation? Thanks for your help. I have a feeling it can probably be done with some advanced SQL syntax but I don't know it. One last thing. This example is actually a simplification of the problem I am trying to solve. I have other constraints not mentioned here for the sake of simplicity. Seth
Jan 03 2013

Can SQL Server perform an update on rows with a set operation on the aggregate max or min value?

        <p>I am a fairly experienced SQL Server developer but this problem has me REALLY stumped.</p>

I have a FUNCTION. The function is referencing a table that is something like this...

PERFORMANCE_ID, JUDGE_ID, JUDGING_CRITERIA, SCORE  
--------------------------------------------------  
101, 1, 'JUMP_HEIGHT', 8   
101, 1, 'DEXTERITY', 7  
101, 1, 'SYNCHRONIZATION', 6  
101, 1, 'SPEED', 9  
101, 2, 'JUMP_HEIGHT', 6   
101, 2, 'DEXTERITY', 5  
101, 2, 'SYNCHRONIZATION', 8  
101, 2, 'SPEED', 9  
101, 3, 'JUMP_HEIGHT', 9   
101, 3, 'DEXTERITY', 6  
101, 3, 'SYNCHRONIZATION', 7  
101, 3, 'SPEED', 8  
101, 4, 'JUMP_HEIGHT', 7   
101, 4, 'DEXTERITY', 6  
101, 4, 'SYNCHRONIZATION', 5  
101, 4, 'SPEED', 8  

In this example there are 4 judges (with IDs 1, 2, 3, and 4) judging a performance (101) against 4 different criteria (JUMP_HEIGHT, DEXTERITY, SYNCHRONIZATION, SPEED).
(Please keep in mind that in my real data there are 10+ criteria and at least 6 judges.)

I want to aggregate the results in a score BY JUDGING_CRITERIA and then aggregate those into a final score by summing...something like this...

SELECT SUM (Avgs) FROM
(SELECT AVG(SCORE) Avgs 
  FROM PERFORMANCE_SCORES
  WHERE PERFORMANCE_ID=101
  GROUP BY JUDGING_CRITERIA) result 

BUT... that is not quite what I want IN THAT I want to EXCLUDE from the AVG the highest and lowest values for each JUDGING_CRITERIA grouping. That is the part that I can't figure out. The AVG should be applied only to the MIDDLE values of the GROUPING FOR EACH JUDGING_CRITERIA. The HI value and the LO value for JUMP_HEIGHT should not be included in the average. The HI value and the LO value for DEXTERITY should not be included in the average. ETC.

I know this could be accomplished with a cursor to set the hi and lo for each criteria to NULL. But this is a FUNCTION and should be extremely fast.

I am wondering if there is a way to do this as a SET operation but still automatically exclude HI and LO from the aggregation?

Thanks for your help. I have a feeling it can probably be done with some advanced SQL syntax but I don't know it.

One last thing. This example is actually a simplification of the problem I am trying to solve. I have other constraints not mentioned here for the sake of simplicity.

Seth