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