SELECT DATE_FORMAT(CURDATE(), '%Y%m') AS agno_mes,
YEAR(CURDATE()) AS agno,
Regional,
count(*) AS Total_cuentas,
SUM(IF(tipodeuda LIKE '%Cuota Moderadora%', 1, 0)) AS Cuota_Moderadora,
SUM(IF(tipodeuda LIKE '%y Multas%', 1, 0)) AS Dagnos_Multas,
SUM(IF(tipodeuda NOT LIKE '%y Multas%'
AND tipodeuda NOT LIKE '%Cuota Moderadora%', 1, 0)) AS Otros,
SUM(Valor_Pendiente) AS Valor_Pendiente,
SUM(IF(tipodeuda LIKE '%Cuota Moderadora%', Valor_Pendiente, 0)) AS Valor_Cuota_Moderadora,
SUM(IF(tipodeuda LIKE '%y Multas%', Valor_Pendiente, 0)) AS Valor_Dagnos_Multas,
SUM(IF(tipodeuda NOT LIKE '%y Multas%'
AND tipodeuda NOT LIKE '%Cuota Moderadora%', Valor_Pendiente, 0)) AS Valor_Otros
WHERE Modulo_Relacionado = 'PacientesII'
AND Relacionado_Con NOT LIKE '%PRUEBA%'
AND (empresa <> 3130039
AND empresa <> 3589077)
AND (estadocuenta = 'Pendiente'
OR estadocuenta = 'Parcial'
OR estadocuenta = 'Programado')
AND (Regional NOT LIKE '%OXIAYUDA%'
AND Regional <> "")
GROUP BY Regional
|