CODEWITHSUNDEEP
sqlsql-server
Brendan Gooden
Brendan Gooden

Reputation: 1551

SQL - Find only rows with different payment terms

I have an SQL Query that finds customers, and their payment terms from several different tables.

The query is as follows. 

SELECT 
    c.CustomerCode, c.CustomerName, cst.PaymentTermCode 
FROM 
    CustomerShipTo cst
JOIN 
    Customer c ON cst.CustomerCode = c.CustomerCode
WHERE  
    cst.IsActive = 1 AND c.IsProspect = 0 AND c.IsActive = 1

I want to find customers that have multiple shipping addresses, but that don't all have the same payment terms. In this sample data, the last 2 rows, the same customer (CUST-006002) has 2 different shipping addresses with 2 different payment codes, so I want to select only these rows.

I tried adding a HAVING COUNT(CustomerCode) > 1 clause to the end but that didn't give the desired output because sometimes there can be customers with multiple shipping address (in the case of Customer E) but with the same Payment Term.

╔═══════════════╦═════════════════════════════╦═══════════════════╗
║ Customer Code ║ Shipping Address            ║ Payment Term Code ║
╠═══════════════╬═════════════════════════════╬═══════════════════╣
║ CUST-016714   ║ Company A - Sample Address  ║ NET30EOM          ║
╠═══════════════╬═════════════════════════════╬═══════════════════╣
║ CUST-017457   ║ Company B - Sample Address  ║ NET30EOM          ║
╠═══════════════╬═════════════════════════════╬═══════════════════╣
║ CUST-016464   ║ Company C - Sample Address  ║ COD               ║
╠═══════════════╬═════════════════════════════╬═══════════════════╣
║ CUST-017215   ║ Company D - Sample Address  ║ COD               ║
╠═══════════════╬═════════════════════════════╬═══════════════════╣
║ CUST-006001   ║ Company E - Sample Address1 ║ NET30EOM          ║
╠═══════════════╬═════════════════════════════╬═══════════════════╣
║ CUST-006001   ║ Company E - Sample Address2 ║ NET30EOM          ║
╠═══════════════╬═════════════════════════════╬═══════════════════╣
║ CUST-006002   ║ Company F - Sample Address1 ║ COD               ║
╠═══════════════╬═════════════════════════════╬═══════════════════╣
║ CUST-006002   ║ Company F - Sample Address2 ║ NET30EOM          ║
╚═══════════════╩═════════════════════════════╩═══════════════════╝

Upvotes: 1

Views: 141

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

One method uses window functions. SQL Server does not support COUNT(DISTINCT) as a window function. However, you can compare the minimum and maximum to see if there is more than one value:

SELECT c.*
FROM (SELECT c.CustomerCode, c.CustomerName, cst.ShippingAddress, 
             cst.PaymentTermCode,
             MIN(cst.ShippingAddress) OVER (PARTITION BY c.CustomerCode) as minsa,
             MAX(cst.ShippingAddress) OVER (PARTITION BY c.CustomerCode) as maxsa,
             MIN(cst.PaymentTermCode) OVER (PARTITION BY c.CustomerCode) as minptc,
             MAX(cst.PaymentTermCode) OVER (PARTITION BY c.CustomerCode) as maxptc
      FROM CustomerShipTo cst JOIN
           Customer c
           ON cst.CustomerCode = c.CustomerCode
      WHERE cst.IsActive = 1 AND c.IsProspect = 0 and c.IsActive = 1
     ) c
WHERE minptc <> maxptc AND minsa <> maxsa;

Upvotes: 4

Related Questions