CODEWITHSUNDEEP
sql
Elias Girma
Elias Girma

Reputation: 13

SQL Query to retrieve members who didn't make any payment for the past six months

I have tried to create a query to retrieve members who didn't complete a payment in the past six months. I have two tables, one for the members' details and the other for payment management. I have tried the below code but it doesn't work perfectly.

SELECT * 
FROM tbl_members e 
LEFT OUTER JOIN tbl_paymgt s on e.MemberID=s.memberID 
WHERE (((DATEDIFF(NOW(), s.paidDate)<365) 
    AND (DATEDIFF(NOW(), s.paidDate)>180))) 
    AND (e.MembershipStatus=1);

This code only retrieves payments completed within the past six months.

Upvotes: 1

Views: 347

Answers (3)

cod3-jr
cod3-jr

Reputation: 96

I'd recommend using a CTE to capture the members that have made a payment in the past 6 months and then select members who are not in that CTE result set.

something like:

with payers as (
    select MemberID
    from tbl_paymgt
    where (DATEDIFF(NOW(), paidDate) < 180)
        
)
Select *
from tbl_members
where memberID not in (select memberID from payers)
and (MembershipStatus=1)

Upvotes: 1

Kevin
Kevin

Reputation: 46

Here is an alternative way to use keyword not exists to exclude the members who completed a payment in the past six months.

SELECT *
FROM tbl_members m
WHERE MembershipStatus = 1 
  AND NOT EXISTS (
    SELECT 1
    FROM tbl_paymgt p
    WHERE DATEDIFF(NOW(), p.paidDate) < 180
       AND p.MemberID = m.MemberID);

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

Paraphrased; select all customers that are currently members And have no payments in the last 6 months...

Fixing your query directly, that gives...

SELECT
  * 
FROM
  tbl_members e 
LEFT OUTER JOIN
  tbl_paymgt s
    ON  e.MemberID=s.memberID 
    AND s.PaidDate > NOW() - INTERVAL 6 MONTH
WHERE
      e.membershipStatus = 1
  AND s.MemberID IS NULL

But, using NOT EXISTS() is more readable and often less costly to run.

SELECT
  *
FROM
  tbl_members m
WHERE
  MembershipStatus = 1 
  AND
  NOT EXISTS (
    SELECT *
      FROM tbl_paymgt p
     WHERE p.PaidDate > NOW() - INTERVAL 6 MONTH
       AND p.MemberID = m.MemberID
  );

Upvotes: 2

Related Questions