Here's a quick-and-dirty first try -- might or might not work. Also, you need to get rid of the DISTINCT if at all possible -- it's a big drag on performance.
IF OBJECT_ID('tempdb..#race_counts') IS NOT NULL
DROP TABLE #race_counts
CREATE TABLE #race_counts (
universalid int NOT NULL,
RunID int NOT NULL,
RunnerID int NOT NULL,
race_date_count int NULL,
UNIQUE CLUSTERED ( universalid, RunID, RunnerID )
)
--EXEC tempdb..sp_help #race_Counts
INSERT INTO #race_counts
SELECT
universalid, RunID, RunnerID,
COUNT(DISTINCT RaceDate) AS race_date_count
FROM dbo.races r WITH (NOLOCK)
WHERE
RaceDate >= @CutoffDate
GROUP BY
universalid, RunID, RunnerID
SELECT DISTINCT
@SeasonStamp,
r.RunnerID,
r.RunID AS RunID,
(SELECT COUNT(*) FROM #race_counts WHERE universalid = r.universalid AND RunID = r.RunID) AS NUMOFSponsorS,
(SELECT SUM(race_date_count) FROM #race_counts WHERE universalid = r.universalid AND RunID = r.RunID AND RunnerID like r.RunnerID + '%') AS NUMBEROFinhouseMeets,
(SELECT SUM(race_date_count) FROM #race_counts WHERE universalid = r.universalid AND RunID = r.RunID AND RunnerID NOT like r.RunnerID + '%') AS NUMBEROFoutsideMeets,
r.universalid
FROM
RACES r WITH (NOLOCK)
WHERE
r.RaceDate >= @CutoffDate
AND r.RunnerID like @Sponsor+'%'
AND r.ZorX = @ZorX
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.