Database

Gary Wheeler wrote:

ODBC: I want independence from the actual DBMS

Colin Angus Mackay wrote:

If you write your DAL against SQL Server 2005 Express Edition then it will work without change if you migrate to a fuller SQL Server edition.

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!!!