The Preagg query is just counting the total number or records per dealer in table 2.

I switched the inner join to a left outer join and got some crazy results. The only thing I can think of to correct it is to do this:

SELECT

DealerId,

COUNT(RepairID) AS 'RepairsForThisDealer'

INTO

#temp

FROM

#Table1

GROUP BY

DealerId

;WITH

Preagg AS (

SELECT

d.DealerID,

Tire,

COUNT(r.RepairID) AS RepairsOfThisTyre,

RepairsForThisDealer

FROM

#Table1 d

INNER JOIN #Table2 r ON r.RepairID = d.RepairID

INNER JOIN #temp t ON t.DealerId = d.DealerId

GROUP BY d.DealerID, Tire, RepairsForThisDealer

),

FinalAgg AS (

SELECT DealerID, Tire, RepairsOfThisTyre, RepairsForThisDealer,

x.PCT,

q = SUM(x.PCT) OVER(PARTITION BY DealerID ORDER BY x.PCT DESC, Tire),

rn = ROW_NUMBER() OVER(PARTITION BY DealerID ORDER BY RepairsOfThisTyre DESC)

FROM Preagg

CROSS APPLY (SELECT PCT = (RepairsOfThisTyre*100.00)/RepairsForThisDealer) x

)

SELECT f.*

FROM FinalAgg f

CROSS APPLY (

SELECT TOP 1 *

FROM FinalAgg fi

WHERE fi.DealerID = f.DealerID

AND q >= 80

ORDER BY rn

) x

WHERE f.rn <= x.rn

ORDER BY f.DealerID, f.RepairsOfThisTyre DESC

I get the correct percents, but now it doesn't show dealer 2.