SQL query - how to do an iterative query?

Posted on 2005-04-13
Last Modified: 2008-03-06

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:

SELECT  A.SalesLocationCode
               , AP.CustomerKey
               , A.AgreementKey
               , COUNT(AP.AgreementParticipantTypeCode)
FROM Agreement A
INNER JOIN AgreementParticipant AP
ON A.AgreementKey = AP.AgreementKey
GROUP BY A.SalesLocationCode
         , AP.CustomerKey
         , A.AgreementKey
         , AP.AgreementParticipantTypeCode

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.
Question by:julescantwell
    LVL 28

    Expert Comment

    SELECT A.AgreementKey, COUNT(*)
    FROM (
    SELECT DISTINCT A.AgreementKey, AP.CustomerKey
    FROM Agreement A INNER JOIN AgreementParticipant AP
    ON A.AgreementKey = AP.AgreementKey) A
    GROUP BY A.AgreementKey
    HAVING COUNT(*) > 1

    This will give you the agreements with more than 1 Customer Type.
    LVL 68

    Expert Comment

    SELECT SalesLocationCode
                   , CustomerKey
    FROM AgreementParticipant AP
    GROUP BY SalesLocationCode
             , CustomerKey
    HAVING COUNT(DISTINCT AgreementParticipantTypeCode) > 1
    LVL 68

    Accepted Solution

    If you need to see additional columns, you can use an outer query to enhance the results:

    SELECT APMulti.SalesLocation,
    FROM (
        SELECT SalesLocation
                       , CustomerKey
        FROM AgreementParticipant AP1
        GROUP BY SalesLocation
                 , CustomerKey
        HAVING COUNT(DISTINCT AgreementParticipantTypeCode) > 1
    ) AS APMulti
    INNER JOIN AgreementParticipant AP2 ON AP2.SalesLocation = APMulti.SalesLocation AND AP2.CustomerKey = APMulti.CustomerKey
    ORDER BY 1, 2, 3

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now