Differentiate Newmarket Rowley Mile vs Newmarket July courses

Question

Results from the Newmarket Rowley Mile and Newmarket July courses can be quite different. For example Richard Fahey has an SR (1/1/2000 -> present) on Rowley Mile of 7.3% vs. 12.7% on the July course. That data is from Raceform Interactive which treats these courses as distinct.  How can I separate out Newmarket into 2 courses in Smartform. The aggregated SR for Richard Fahey reported by an appropriate query from Smartform is 9.6%.

Is the only way to have a record of the date range for use of the July course in each year and programatically split one course into two ?

In case anyone's insterested, here's the query I'm using to get this data:

USE smartform;
SELECT
    sub.course,
    sub.race_type,
    sub.trainer_name,
    sub.RaceCount,
    sub.WinCount,
    sub.LosCount,
    sub.PlcCount,
    FORMAT(IF(sub.RaceCount > 30, (sub.WinCount / sub.RaceCount) * 100, NULL), 1) AS WinPct,
    FORMAT(IF(sub.RaceCount > 30, (sub.PlcCount / sub.RaceCount) * 100, NULL), 1) AS PlcPct
FROM (
    SELECT
        course,
        race_type,
        trainer_name,
        COUNT(*) AS RaceCount,
        COUNT(IF(finish_position = 1, 1, NULL)) AS WinCount,
        COUNT(IF(finish_position > 1, 1, NULL)) AS LosCount,
        COUNT(IF(finish_position < 4, 1, NULL)) AS PlcCount
    FROM
        historic_races
            JOIN
        historic_runners USING (race_id)
    WHERE
        course = 'Newmarket'
            AND race_type_ID IN (12, 15)
            AND trainer_name IS NOT NULL
            AND unfinished IS NULL
    GROUP BY trainer_id) AS sub
ORDER BY sub.course , sub.trainer_name

Answer 1

Yes, that's correct, though the July course at Newmarket is only used in Summer, so you should make your default the Rowley mile, then strip out all races in June, July and August - those are your July course races.