ChrisM@home (3/23/2014)
This query works only because of the sequence of RepairId in table2, which already orders most-used tyres ahead of least-used tyres, probably by accident. If table2 really is artificially ordered in this way, you're ok, otherwise you will get incorrect results. Here's a copy of Lyn's dataset slightly adjusted to show this:
DROP TABLE dbo.Table1
CREATE TABLE dbo.Table1 (DealerId INT, RepairId INT);
INSERT INTO dbo.Table1 VALUES
(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),
(1,8),(1,9),(1,10),(2,11),(2,12),(2,13),
(3,14),(3,15),(3,16);
DROP TABLE dbo.Table2
CREATE TABLE dbo.Table2 (RepairId INT, tire VARCHAR(15));
INSERT INTO dbo.Table2 VALUES (9,'225/65R17 102T'),
(7,'225/65R17 102T'),(3,'225/65R17 102T'),(4,'235/60R18 102V'),
(5,'235/60R18 102V'),(6,'235/60R18 102V'),(8,'235/60R18 102V'),
(2,'205/55R16 89H'),(1,'205/70R15 89H'),(13,'225/65R17 102T'),
(14,'235/60R18 102V'),(15,'235/60R18 102V'),(16,'235/60R18 102V');
and a query which works:
SELECT DealerID, tire, RepairsOfThisTire, TotalRepairs,
PercentOfTotal = (RepairsOfThisTire*100.00)/TotalRepairs
FROM (
SELECT DealerID, TotalRepairs, tire, RepairsOfThisTire,
Sector = NTILE(5) OVER(PARTITION BY DealerID ORDER BY RepairsOfThisTire DESC, tire DESC)
FROM (
SELECT
t1.DealerID, t2.tire,
TotalRepairs = COUNT(*) OVER (PARTITION BY t1.DealerID),
RepairsOfThisTire = COUNT(*) OVER(PARTITION BY t1.DealerID, t2.tire)
FROM dbo.Table1 t1
LEFT JOIN dbo.Table2 t2 ON t2.RepairId = t1.RepairId
) d
WHERE tire IS NOT NULL
) e
WHERE Sector < 5
GROUP BY DealerID, TotalRepairs, tire, RepairsOfThisTire--, PercentOfTotal
ORDER BY DealerID, RepairsOfThisTire DESC, tire;
The problem with this query is it returns data for dealer id 3. The business rule I was given was that they only want to see the top 80%. In the case of dealer id 3 the query is showing the top 100%. In this example the top 80% cannot be calculated since all the orders are the same tire. The main issue I see and need to get clarification from the business on is what happens if the top tire is > 80%?