Brendan Gooden Brendan Gooden - 22 days ago 9
SQL Question

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 dont 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 ║
╚═══════════════╩═════════════════════════════╩═══════════════════╝

Answer

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;