SQL query - how to do an iterative query?
Posted on 2005-04-13
I have two tables – Agreement (primary key is AgreementKey) and AgreementParticipant (has a foreign key which is AgreementKey from the Agreement table). AgreementParticipant has many fields but I am interested in are CustomerKey (foreign key to the Customer table), ParticipantCustomerType, and SalesLocation.
Basically, I need to identify all cases whereby there are more than one different ParticipantCustomerTypes for a particular customer in a particular sales location.
For example, where CustomerKey = 111 and SalesLocation = IE, I need to find all the different values of ParticipantCustomerType that fall under this bracket (specifically I need to identify all cases where there are two or more different values within the bracket).
However, I don’t need to do this for one particular customerkey, instead I need a report showing me all cases where there are many ParticipantCustomerTypes within a particular CustomerKey/SalesLocation combination. Unfortunately my SQL skills are limited and after many attempts my best attempt is:
FROM Agreement A
INNER JOIN AgreementParticipant AP
ON A.AgreementKey = AP.AgreementKey
GROUP BY A.SalesLocationCode
I would be very appreciative if anyone can help me to either get a count of the number of customers with more than one ParticipantCustomerType within a certain SalesLocation, or a report that I can use to pivot on in excel to give me this breakdown.