Database

SELECT matchid, AVG((CASE Code WHEN 'A' THEN 4.0 
                               WHEN 'B' THEN 3.0 
                               WHEN 'C' THEN 2.0 
                               WHEN 'D' THEN 1.0 
                               ELSE 0.0 END)
FROM ScoutingReport AS sr
WHERE sr.Type = 1
AND sr.ScoutId = 11619
GROUP BY matchid

SELECT MAX(average)
FROM
(
    SELECT matchid, AVG((CASE Code WHEN 'A' THEN 4.0 
                                   WHEN 'B' THEN 3.0 
                                   WHEN 'C' THEN 2.0 
                                   WHEN 'D' THEN 1.0 
                                   ELSE 0.0 END) AS average
    FROM ScoutingReport AS sr
    WHERE sr.Type = 1
    AND sr.ScoutId = 11619
    GROUP BY matchid
)

SELECT TOP 1 matchid, average
FROM
( 
    SELECT matchid, AVG(CASE Code WHEN 'A' THEN 4.0 
                                  WHEN 'B' THEN 3.0 
                                  WHEN 'C' THEN 2.0 
                                  WHEN 'D' THEN 1.0 
                                  ELSE 0.0 END) AS average 
    FROM ScoutingReport AS sr 
    WHERE sr.Type = 1 
    AND sr.ScoutId = 11619 
    GROUP BY matchid
) as t
ORDER BY average DESC

ToddHileHoffer wrote:

Is it really bad design to have a date field that allows nulls?

Colin Angus Mackay wrote:

GET THAT TABLE NORMALISED MAN!!! JUMP TO IT!!!! JUMP TO IT!!!

IMC2006 wrote:

Line1: Incorrect Syntax near 'prc_QA_Setup_ins'.

cmd.CommandText = "prc_QA_Setup_ins"

cmd.CommandType = CommandType.StoredProcedure