mysql filter and match data INTERVAL 30 day-CodePudding

I am trying to get data that customer who didnt have any new transaction with type deposit within 30 days

Eg: customer MS00001 wont be query because it still have new record with deposit within 30 day, the result should only show me MS00002 because it didnt have and type of deposit within 30 days

meanwhile I have a customer table which have the customer details.

so how could I use 1 query to solve all of this

target output : transaction table customer table who didnt have new record within 30 days

below is the query that I trying to get customer who didnt have any new record in transaction table.

SELECT t1.* 
    FROM transaction t1
LEFT JOIN (
    SELECT customer FROM transaction 
    WHERE date <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)
) t2 ON t2customer = t1.customer
WHERE t1.date <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)
AND t1.type = 'deposit'
AND t2.customer  IS NULL
ORDER BY trans_id DESC

CodePudding user response：

Answer depends a bit what you want see from transaction table. The simpliest way to do this with one query is to group transactions by customer and then select customers whose last deposit was made over 30 days ago. In this way you are able to see their last transactions details in select

SELECT transaction.*, customer.*
FROM transaction
JOIN customer
    ON customer.id = transaction.customer_id
WHERE transaction.type = "deposit"
GROUP BY customer.id
HAVING MAX(transaction.date) <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)

CodePudding user response：

thank you for maksa for giving me idea to solve this question.

SQL below get the result that I want.

SELECT *
FROM transaction
JOIN customers
    ON customers.username = transaction.customer
WHERE transaction.type = "deposit"
GROUP BY customers.username, transaction.customer
HAVING MAX(transaction.date) <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)

And If you receive any problem of aggregation, in my case : phpmyadmin go to variables => sql mode

Edit sql mode variable and remove the ONLY_FULL_GROUP_BY text from the value, then save.

or follow this:

and finally, thanks maksa again