Search code examples
mysqldatabaseadminer

Select from database where there aren't any records since the last three months

I need to select the defaulting companies from my table (those who doesn't register a payment since october)

This is an example of what my table looks like:

id company_id deposit_date year month
1 578 2021-10-12 2021 10
2 254 2021-11-17 2021 11
3 465 2021-12-15 2021 12
4 159 2022-01-12 2022 1

I must use month as reference and not deposit_date

Any help will be much appreciated. Thanks in advance.

Solution

  • Check for those where last payment prior to nov 2021

    drop table if exists t;
create table t(id   int,company_id  int,deposit_date    date,year   int,month int);
insert into t values
(1  ,578    ,'2021-10-12'   ,2021   ,10),
(2  ,254    ,'2021-11-17'   ,2021   ,11),
(3  ,465    ,'2021-12-15'   ,2021   ,12),
(4  ,159    ,'2022-01-12'   ,2022   ,1);

select company_id ,year*100+month
from t
group by company_id
having max(year*100+month) < 202111;