salix/db/routines/bi/procedures/defaultersFromDate.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `bi`.`defaultersFromDate`(IN vDate DATE)
BEGIN

	SELECT t1.*, c.name Cliente, w.code workerCode, c.payMethodFk pay_met_id, c.dueDay Vencimiento
	FROM (
	-- Filtramos aquellos clientes cuyo saldo se ha incrementado de ayer a hoy
		select * from(
			select today.client, today.amount todayAmount, yesterday.amount yesterdayAmount, round(yesterday.amount - today.amount,2) as difference, defaulterSince
			from
				(select client, amount, defaulterSince
				from defaulters
				where date = vDate and hasChanged) today
					join
				(select client, amount
				from defaulters
				where date = TIMESTAMPADD(DAY,-1,vDate)) yesterday using(client)

					having today.amount > 0 and difference <> 0
					) newDefaulters
	)t1 left join vn.client c ON c.id = t1.client
		left join vn.worker w ON w.id = c.salesPersonFk;
END$$
DELIMITER ;