Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Why is My Query Failing Logically?

Status
Not open for further replies.

VAST39

Programmer
Sep 21, 2012
26
US
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:

Code:
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.





 
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."
 
Why so complex?
Didn't this work?
Code:
select distinct recip_ssn_nbr
       FROM DSNP.PR01_T_ANTY_PYMT
WHERE ANTY_PYMT_DT >= '20130601'


Borislav Borissov
VFP9 SP2, SQL Server
 
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.
 
I tinkered with it some more, and it looks like it is working now. Just needed to see if I was completely off track.
 
This should give you the same list:

Code:
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....

Code:
; 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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top