0

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.

0

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
0

Thanks for this. The answer didn't quite do what I was after but after a couple of changes it looks to be giving what I wanted. Shown below, without the TOP 1 clause
SELECT CustomerRequests.customerReference FROM CustomerRequests JOIN UsersSkills ON UsersSkills.requestTypeId = CustomerRequests.requestTypeId AND UsersSkills.userId = 'User 1' --join RequestAttributes on RequestTypeId = CustomerRequests.requestTypeId WHERE CustomerRequests.customerReference NOT IN ( SELECT CustomerRequests.customerReference FROM CustomerRequests LEFT OUTER JOIN usersSkills ON UsersSkills.requestTypeId = CustomerRequests.requestTypeId AND UsersSkills.userId = 'User 1' WHERE UsersSkills.userId IS NULL ) AND CustomerRequests.status = 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.