To rank based on number of cases created for customers:
SELECT
ROW_NUMBER() OVER(ORDER BY COUNT(incidentid) DESC) AS 'Ranking',
Contact,
COUNT(incidentid) 'Incident Count'
FROM incident
GROUP BY Contact
ORDER BY COUNT(incidentid) DESC
Results:
Ranking Contact Incident Count
------- -------------------- --------------
1 DEEPAK 110
2 GANESH LAL SHRESTHA 78
3 MIZANUR RAHMAN 36
4 WELLER 34
5 MICHELLE TONG 19
To rank based on number of cases of particular Case Type created for customers:
SELECT
Contact,
ROW_NUMBER() OVER(PARTITION BY Contact ORDER BY COUNT(incidentid) DESC) AS 'Ranking',
CaseType,
COUNT(incidentid) 'Incident Count'
FROM #incident
GROUP BY Contact, CaseType
ORDER BY Contact, COUNT(incidentid) DESC
Results:
Contact Rank Case Type Case Count
--------------------- ------ -------------- ----------
DEEPAK 1 APPEAL 21
DEEPAK 2 FEEDBACK 20
DEEPAK 3 COMPLIMENT 16
DEEPAK 4 MP APPEAL 15
DEEPAK 5 SUGGESTION 14
DEEPAK 6 COMPLAINT 13
DEEPAK 7 ENQUIRY 11
GANESH LAL SHRESTHA 1 ENQUIRY 16
GANESH LAL SHRESTHA 2 FEEDBACK 14
GANESH LAL SHRESTHA 3 APPEAL 13
GANESH LAL SHRESTHA 4 COMPLIMENT 10
GANESH LAL SHRESTHA 5 SUGGESTION 10
GANESH LAL SHRESTHA 6 COMPLAINT 9
GANESH LAL SHRESTHA 7 MP APPEAL 6
MICHELLE TONG 1 COMPLIMENT 6
MICHELLE TONG 2 COMPLAINT 4
MICHELLE TONG 3 ENQUIRY 3
MICHELLE TONG 4 SUGGESTION 3
MICHELLE TONG 5 APPEAL 2
MICHELLE TONG 6 MP APPEAL 1
MIZANUR RAHMAN 1 ENQUIRY 7
MIZANUR RAHMAN 2 COMPLIMENT 7
MIZANUR RAHMAN 3 FEEDBACK 6
MIZANUR RAHMAN 4 COMPLAINT 5
MIZANUR RAHMAN 5 MP APPEAL 4
MIZANUR RAHMAN 6 SUGGESTION 4
MIZANUR RAHMAN 7 APPEAL 3
WELLER 1 SUGGESTION 8
WELLER 2 FEEDBACK 7
WELLER 3 MP APPEAL 5
WELLER 4 ENQUIRY 5
WELLER 5 COMPLIMENT 4
WELLER 6 APPEAL 3
WELLER 7 COMPLAINT 2
