How to select persons who didn't pay they'r bill for two mounth

mohammadkaab

hi , i have two table first table name is person and second table name is payment what i'd like to do is that , i want to select those persons who haven't paid they'r bill for two month

select person.* from (person INNER JOIN
payment ON person.national_number = payment.national_number)
where (payment.payment_date > 'two month ago from now')

but this w'll fetch those persons who paid their bill for the two month ( i don't want them) . but i need those who didn't pay they'r bill for the two month i don't know if you understand it or no .

Lost User

Surely

where (payment.payment_date < 'two month ago from now')

?

Richard Deeming

Assuming that one person can have multiple payments, an INNER JOIN will return a record for every payment over two months old, regardless of whether the person has paid within the last two months. What you need to do is find the person records which don't have a payment record within the last two months. To do that, you can use a Not Exists filter:

DECLARE @Today date = GetUtcDate();
DECLARE @CutoffDate date = DateAdd(month, -2, @Today);

SELECT
*
FROM
person
WHERE
Not Exists
(
SELECT 1
FROM payment
WHERE payment.national_number = person.national_number
And payment.payment_date > @CutoffDate
)
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Lost User

So.. I would never show up in your list if I paid a dollar each month? :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

PIEBALDconsult

SELECT person.*
FROM person
LEFT OUTER JOIN payment
ON person.national_number = payment.national_number
AND payment_date > 'two month ago from now'
WHERE payment_date IS NULL

You'll never get very far if all you do is follow instructions.

PIEBALDconsult

Won't that have to look up each national_number in payment separately? It always looks like it does. X| I don't use EXIST.

You'll never get very far if all you do is follow instructions.

Richard Deeming

No, the SQL optimizer is pretty good at generating a sensible query plan. :)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Mycroft Holmes

Eddy Vluggen wrote:

Bastard Programmer from Hell

Oh so appropriate. Should be renamed The Edge Case from Hell

Never underestimate the power of human stupidity RAH