J
Jen
The query this is based off has many fields but the most important are:
Rep, Office Location, TotalAlert, and Conversion%
I need to create a query that will give me the top 10 reps (those with the
lowest Conversion% in ascending order with the TotalAlert in descending
order) for each Office Location.
For example:
Rep OfficeLoc TotalAlert Conversion%
Mike Hawaii 100 0.0%
Rachel Hawaii 99 0.0%
Sam Hawaii 101 1.0%
Dan Hawaii 80 2.0%
I have tried the following sql (which I found here somewhere), but the
ranking is wrong & all over the place. And, if I use Top 10, then I don't
receive the Top 10 for each location - which is what I need.
Help!
SELECT qryYTDRepSkidAlertStats.OGIDNO, qryYTDRepSkidAlertStats.SignON,
qryYTDRepSkidAlertStats.Rep, qryYTDRepSkidAlertStats.Accept,
qryYTDRepSkidAlertStats.Decline, qryYTDRepSkidAlertStats.TotalAlert,
qryYTDRepSkidAlertStats.[Conversion%], qryYTDRepSkidAlertStats.Phone,
qryYTDRepSkidAlertStats.Phneweb, qryYTDRepSkidAlertStats.TotalPhoneOrders,
qryYTDRepSkidAlertStats.[Opportunity%], qryYTDRepSkidAlertStats.[Office
Location],
(Select Count(*) from qryYTDRepSkidAlertStats as T1 where T1.[Office
Location]=qryYTDRepSkidAlertStats.[Office Location] And
T1.[Conversion%]<=qryYTDRepSkidAlertStats.[Conversion%] AND
T1.[TotalAlert]>=qryYTDRepSkidAlertStats.[TotalAlert]) AS Rank
FROM qryYTDRepSkidAlertStats
WHERE (Select Count(*) from qryYTDRepSkidAlertStats as T1 where T1.[Office
Location]=qryYTDRepSkidAlertStats.[Office Location] And
T1.[Conversion%]<=qryYTDRepSkidAlertStats.[Conversion%] AND
T1.[TotalAlert]>=qryYTDRepSkidAlertStats.[TotalAlert]) <= 10
ORDER BY qryYTDRepSkidAlertStats.[Office Location],
qryYTDRepSkidAlertStats.[Conversion%], qryYTDRepSkidAlertStats.TotalAlert
DESC;
This is what it returns:
TotalAlert Conversion% Rank
144 0.00% 1
121 0.00% 2
81 0.00% 3
79 0.00% 4
62 0.00% 5
56 0.00% 6
36 0.00% 7
17 0.00% 8
92 2.17% 3
44 2.27% 9
44 2.27% 9
168 2.38% 1
Rep, Office Location, TotalAlert, and Conversion%
I need to create a query that will give me the top 10 reps (those with the
lowest Conversion% in ascending order with the TotalAlert in descending
order) for each Office Location.
For example:
Rep OfficeLoc TotalAlert Conversion%
Mike Hawaii 100 0.0%
Rachel Hawaii 99 0.0%
Sam Hawaii 101 1.0%
Dan Hawaii 80 2.0%
I have tried the following sql (which I found here somewhere), but the
ranking is wrong & all over the place. And, if I use Top 10, then I don't
receive the Top 10 for each location - which is what I need.
Help!
SELECT qryYTDRepSkidAlertStats.OGIDNO, qryYTDRepSkidAlertStats.SignON,
qryYTDRepSkidAlertStats.Rep, qryYTDRepSkidAlertStats.Accept,
qryYTDRepSkidAlertStats.Decline, qryYTDRepSkidAlertStats.TotalAlert,
qryYTDRepSkidAlertStats.[Conversion%], qryYTDRepSkidAlertStats.Phone,
qryYTDRepSkidAlertStats.Phneweb, qryYTDRepSkidAlertStats.TotalPhoneOrders,
qryYTDRepSkidAlertStats.[Opportunity%], qryYTDRepSkidAlertStats.[Office
Location],
(Select Count(*) from qryYTDRepSkidAlertStats as T1 where T1.[Office
Location]=qryYTDRepSkidAlertStats.[Office Location] And
T1.[Conversion%]<=qryYTDRepSkidAlertStats.[Conversion%] AND
T1.[TotalAlert]>=qryYTDRepSkidAlertStats.[TotalAlert]) AS Rank
FROM qryYTDRepSkidAlertStats
WHERE (Select Count(*) from qryYTDRepSkidAlertStats as T1 where T1.[Office
Location]=qryYTDRepSkidAlertStats.[Office Location] And
T1.[Conversion%]<=qryYTDRepSkidAlertStats.[Conversion%] AND
T1.[TotalAlert]>=qryYTDRepSkidAlertStats.[TotalAlert]) <= 10
ORDER BY qryYTDRepSkidAlertStats.[Office Location],
qryYTDRepSkidAlertStats.[Conversion%], qryYTDRepSkidAlertStats.TotalAlert
DESC;
This is what it returns:
TotalAlert Conversion% Rank
144 0.00% 1
121 0.00% 2
81 0.00% 3
79 0.00% 4
62 0.00% 5
56 0.00% 6
36 0.00% 7
17 0.00% 8
92 2.17% 3
44 2.27% 9
44 2.27% 9
168 2.38% 1