Styjun

I have a ledger table, and right now I have the ability to find the date or NULL if someone is delinquent based on their payment history. I need a query that allows me to find all delinquent members instead of just a specific one.

I need the ability to run a query that gets any member that is delinquent and return to me the member_id and the date of delinquency.

Basically what the original query to find delinquency for a specific member does, just doing every member instead of a specific one.

I have tried:

SELECT DISTINCT member_id, created_at FROM member_ledger_items WHERE 
balance > 0 and id > (
 IFNULL(
 (SELECT id from member_ledger_items WHERE balance <= 0 and member_ledger_items.deleted_at is NULL GROUP BY member_id ORDER BY created_at, id desc LIMIT 1),
 0
 )
) and `member_ledger_items`.`deleted_at` is null GROUP BY member_id order by created_at asc, id asc;

This is the query to find if a specific member is delinquent:

select `created_at` from `member_ledger_items` where `member_id` = ? and `balance` > 0 and `id` > 
(
IFNULL(
(select `id` from `member_ledger_items` where `member_id` = ? and `balance` <= 0 and `member_ledger_items`.`deleted_at` is null order by `created_at` desc, `id` desc limit 1)
, 0)
) 
and `member_ledger_items`.`deleted_at` is null order by `created_at` asc, `id` asc limit 1;

Here is the create syntax of the member_ledger_items table:

CREATE TABLE `member_ledger_items` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `member_id` int(10) unsigned NOT NULL,
 `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `type` enum('credit','debit') COLLATE utf8_unicode_ci NOT NULL,
 `category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `memo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `amount` decimal(13,3) DEFAULT NULL,
 `autopay` tinyint(1) DEFAULT NULL,
 `late` tinyint(1) DEFAULT NULL,
 `date` date NOT NULL,
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 `deleted_at` timestamp NULL DEFAULT NULL,
 `balance` decimal(13,3) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53596 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I need rows with member_id and date of starting delinquency.

Is this possible?

Any help would be appreciated!

SELECT `member_id`, 
 (SELECT `created_at` 
 FROM `member_ledger_items` AS MLI2 
 WHERE `balance` > 0 
 AND MLI2.`member_id` = MLI.`member_id` 
 AND `id` > ( Ifnull((SELECT `id` 
 FROM `member_ledger_items` AS MLI3 
 WHERE `balance` <= 0 
 AND MLI3.`member_id` = 
 MLI2.`member_id` 
 AND MLI3.`deleted_at` IS NULL 
 ORDER BY `created_at` DESC, 
 `id` DESC 
 LIMIT 1), 0) ) 
 AND MLI2.`deleted_at` IS NULL 
 ORDER BY `created_at` ASC, 
 `id` ASC 
 LIMIT 1) AS created_date 
FROM `member_ledger_items` AS MLI 
GROUP BY `member_id`; 

Ended up being the solution