Why is My Query Failing Logically?

[VAST39]

Hey guys,

I am trying to write a query that will do the following:

List all people who do not have a payment date of 06-01-2013 or later. My payments table contains a single record for each payment date.

ex. SSN anty_pymt_dt
123445555 06-01-2013
123445555 07-01-2013



I wrote the following:

select distinct recip_ssn_nbr

FROM DSNP.PR01_T_ANTY_PYMT A


WHERE NOT EXISTS

(SELECT                                                                         
     DISTINCT RECIP_SSN_NBR,
     ANTY_PYMT_DT                                                                
FROM DSNP.PR01_T_ANTY_PYMT B

where A.RECIP_SSN_NBR = B.RECIP_SSN_NBR AND                            
      A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR


and ANTY_PYMT_DT >= '2013-06-01')

Since I am using the NOT EXISTS clause, I was expecting it to pull only people who do not meet the condition of ANTY_PYMT_DT >= '2013-06-01'. Yet, it returns people with payment dates of 06-01-2013 and later.

Can anyone tell me why it is failing? I'll need to INNER join this result set to a different table for more criteria which is why I went this route with NOT EXISTS.

 

[JohnDuncanTB]

Assuming the date fields are actual datetimes not strings, I suspect the answer lies with BENEF_SEQ_NBR. You are only selecting RECIP_SSN_NBR for the result set but the NOT EXISTS includes BENEF_SEQ_NBR equality criteria.

--JD
"Behold! As a wild ass in the desert go forth I to my work."

 

[bborissov]

Why so complex?
Didn't this work?

select distinct recip_ssn_nbr
       FROM DSNP.PR01_T_ANTY_PYMT
WHERE ANTY_PYMT_DT >= '20130601'

Borislav Borissov
VFP9 SP2, SQL Server

 

[VAST39]

Boris, that query would give me the exact opposite of what I need. I need to find people who do not have a date 06-01-2013 or greater. If I try to change it and say <> then it pulls every single payment record for each person that is not equal to 06-01-2013 or greater. The NOT EXISTS has got around that problem for me before.

 

[VAST39]

I tinkered with it some more, and it looks like it is working now. Just needed to see if I was completely off track.

 

[gmmastros]

This should give you the same list:

SELECT  RECIP_SSN_NBR        
FROM    DSNP.PR01_T_ANTY_PYMT B
Group By RECIP_SSN_NBR
Having Max(ANTY_PYMT_DT) < '2013-06-01'

Then you said....

I'll need to INNER join this result set to a different table for more criteria

You can do an inner join to a query in a similar way that you inner join a table. In fact, there are several ways to do this. One of them is....

; With OldPayments As
(
  SELECT  RECIP_SSN_NBR        
  FROM    DSNP.PR01_T_ANTY_PYMT B
  Group By RECIP_SSN_NBR
  Having Max(ANTY_PYMT_DT) < '2013-06-01'
)
Select  *
From    [!]OtherTable[/!]
        Inner Join OldPayments
          On [!]OtherTable[/!].RECIP_SSN_NBR = OldPayments.RECIP_SSN_NBR

You will, of course, need to change the name of the "Other Table" in the code above where I highlighted it in read. This method is called a common table expression. Specifically, the top part that start with ";With" and includes the first query I showed above.

This is a nice method because it allows you to write smaller queries and then join them to other queries and/or tables.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom