Stored proc returning customers a user cannot support I've created a stored proc to allocate a customer to a user based on the number and type of customer requests versus the skills of the user. Below is an extract of part of the stored proc - status = 0 means unallocated.

SELECT TOP 1 gdd.customerReference
FROM customerRequests gdd
LEFT OUTER JOIN userSkills us ON us.requestTypeId = gdd.requestTypeId AND us.userId = @pinUserId
LEFT OUTER JOIN requestAttributes dt ON dt.requestTypeId = gdd.requestTypeId
WHERE gdd.status = 0
GROUP BY gdd.requestDateTime, gdd.customerReference, gdd.requestId, gdd.requestTypeId
HAVING COUNT(*) = COUNT(us.userId)


Example data

**Customer Requests**                       
requestId   requestTypeId   policyNumber    customerReference   requestDateTime userId  status
1   3   Policy A    Customer 1  30/11/2015 10:13        0
2   4   Policy A    Customer 1  30/11/2015 10:33        0
3   11  Policy B    Customer 2  26/11/2015 15:26        0
4   17  Policy B    Customer 2  26/11/2015 15:27        0
5   1   Policy B    Customer 2  27/11/2015 10:05        0


Users Skills        

skillId userId  requestTypeId
1   user1   3
2   user1   17
3   user1   11
4   user1   1
5   user2   1
6   user2   3
7   user4   4


Request Attributes          

requestTypeId   description priority    tolerance
1   Type A  200 90
2   Type B  999 999999
3   Type C  100 7
4   Type D  100 5
5   Type E  50  5
6   Type F  100 999999
7   Type G  999 999999
8   Type H  999 999999
9   Type I  999 999999
10  Type J  999 999999
11  Type K  100 999999
12  Type L  999 999999
13  Type M  999 999999
14  Type N  999 999999
15  Type O  100 5
16  Type P  100 10
17  Type Q  100 10


By using TOP 1 in the select the query is correctly returning Customer 2 when I substitute @pinUserId with user1. The problem is that if I take TOP 1 out of the query I also see Customer 1 in the result set even though user1 is not able to handle both of Customer 1's requests. If Customer 1's requests came in before Customer 2 user1 would have been incorrectly allocated Customer 1.

Can anyone suggest a solution to this problem.
This will get everything a user is qualified for. Add a TOP 1 and an order by based on priority to get a single request.

select CustomerRequests.customerReference
from CustomerRequests
join userSkills on  userSkills.skillid = CustomerRequests.requestTypeId
and userSkills.userId = 1 --@pinUserId
--join RequestAttributes on RequestTypeId = CustomerRequests.requestTypeId
where requestid not in (
    select CustomerRequests.requestid
    from CustomerRequests
    left outer join userSkills
        on userSkills.skillid = CustomerRequests.requestTypeId
    where userSkills.skillid is null
    )
and CustomerRequests.status = 0