I have two tables. One is Contact Registration table and one is Payments table One contact registration can have multiple payments. eg: 1 CR can have 15 payments

I need the Contact Registration records which satisfies (RegStatus = Suspended or registered) and RenewalStatus = Pending from Contact registration table and if the payments are not created this year

So the output should be

How to do this?

I tried using below query. But it didn't work.

select distinct cr.ContactRegId , COUNT(PaymentId) as count1
from contactregistration cr left join payments p
on cr.ContactRegId = p.ContactRegId 
where (cr.RegStatus   = 'Registerd' or cr.RegStatus   = 'Suspended')
and cr.RenewalStatus = 'Pending'
and  YEAR(p.CreatedOn) = YEAR(getdate()) 
group by cr.ContactRegId having COUNT(PaymentId) = 0

CodePudding user response：

I need the Contact Registration records which satisfies (RegStatus = Suspended or registered ) and RenewalStatus = Pending from Contact registration table and if the payments are not created this year

You can filter the registration table with a subquery that ensures that the contact had no payment this year:

select r.*
from contactRegistration r
where r.RegStatus in ('Suspended', 'Registered')
    and r.RenewalStatus = 'Pending'
    and not exists (
        select 1
        from payments p
        where 
            p.ContactRegId = r.ContactRegId 
            and p.CreatedOn >= datefromparts(year(getdate()), 1, 1)
    )