CustomerID | payerorder | payername |
---|---|---|
00001 | 1 | root |
00001 | 2 | spade |
00001 | 9 | ginny |
00002 | 1 | spade |
00002 | 3 | root |
00002 | 9 | ginny |
00003 | 9 | ginny |
Result I would like to have:
CustomerID | payerorder | payername |
---|---|---|
000003 | 9 | ginny |
I am trying to pull customers that only have a payer order 9 payer name ginny, they can not have any other payer order attached to them. The issue I am running into is all customers have a payer order 9 payer name ginny. As you can see the billing order is not always a simple 1-8 listing, as depending how it is put into the system, they may not use billing order 2 but skip to billing order 3. How would I go about grabbing just customers with billing order 9 payer name ginny? I would say the ginny isnt needed in the sql due to payer order 9 will always be payer name ginny.
I did try doing:
WHERE payerorder < 9 AND payername is NULL
Which ended up not working, it would only pull one row that had a payer order but a blank payer name but the customer, when I checked the profile, still had other payer orders like 1,3,5 with payer names.
I also tried:
payerorder = 9 and payername = ginny
But the issue with that is everybody has payer order 9 payer name ginny. I only need to pull customers who only have payer order 9 and payer name ginny.
One method would be to use an EXISTS
to check no rows for that client exist with a value other than 9
:
SELECT DISTINCT
YT.CustomerID,
YT.payerorder,
YT.payername
FROM dbo.YourTable YT
WHERE YT.payerorder = 9
AND NOT EXISTS (SELECT 1
FROM dbo.YourTable E
WHERE E.payername= YT.payername
AND E.payerorder <> 9);
An alternative method would be with a HAVING
to check the MIN
and MAX
values are both 9
:
SELECT MAX(YT.CustomerID_,
MAX(YT.payerorder) AS payerorder,
YT.payername AS payername
FROM dbo.YourTable YT
GROUP BY YT.payername
WHERE MAX(YT.payerorder) = 9
AND MIN(YT.payerorder) = 9;