0

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.

user3783243
  • 5,368
  • 5
  • 22
  • 41
Cristian Uhrig
  • 49
  • 7
  • 'I must use month as reference and not deposit_date' - that's an odd requirement have you been told to do this for some reason? – P.Salmon Jan 17 '22 at 16:01
  • I don't understand your data - are companies supposed to make a monthly payment or is the payment a one off? – P.Salmon Jan 17 '22 at 16:04
  • Yes because some people pay months in advance or late, example: |2020-07-07| 2020| 06, they don't care when they paid for it, only if the month was paid – Cristian Uhrig Jan 17 '22 at 16:04
  • monthly payments – Cristian Uhrig Jan 17 '22 at 16:05
  • Why must `month` be used and not deposit date? In all examples month and year come from deposit values. Seems `select company_id from table where deposit_date < now() - interval 3 month` would work – user3783243 Jan 17 '22 at 16:16

2 Answers2

0

You can use select and where clause to filter the data

Preksha Sharma
  • 11
  • 4
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 17 '22 at 16:50
0

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;
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • I was not requested, but you could add something like `HAVING max(year*100+month) <= DATE_FORMAT(DATE(NOW() - INTERVAL 3 MONTH),'%Y%m')` to have a dynamic query. Currently, it would give us 202110 and each month it would move one month up. This would extend the result set in February for all companies which did not make a payment since November. – Uwe Jan 17 '22 at 17:34