|
SELECT
#01 DATE_FORMAT(DATE_FORMAT((NOW()),
'%Y-%m-01 00:00:00'),
'%Y%m') AS `agno_mes`,
#02 (SELECT
`crm`.`vtiger_accountgrouprelation`.`groupname`
FROM
`crm`.`vtiger_accountgrouprelation`
WHERE
(`crm`.`vtiger_accountgrouprelation`.`accountid` = `crm`.`vtiger_pacientesii`.`accountid`)) AS `regional`,
`crm`.`vtiger_pacientesii`.`departamento` AS `departamento`,
`crm`.`vtiger_pacientesiicf`.`cf_1619` AS `EstadoPaciente`,
`crm`.`vtiger_account`.`epsconsolidada` AS `epsconsolidada`,
`crm`.`vtiger_pacientesii`.`sexo` AS `sexo`,
`crm`.`vtiger_pacientesii`.`tipo_regimen` AS `tipo_regimen`,
`crm`.`vtiger_pacientesii`.`grupoetareo` AS `grupoetareo`,
`crm`.`vtiger_servicioscf`.`cf_2799` AS `DescripcionServicio`,
`crm`.`vtiger_pacientesii`.`pacientesiiid` AS `pacientesiiid`,
`crm`.`vtiger_pacientesii`.`docid` AS `docid`,
`crm`.`vtiger_pacientesii`.`fechaactivacion` AS `fechaactivacion`,
`crm`.`vtiger_pacientesii`.`fincontrato` AS `fincontrato`,
#03 SUM(IF(((`crm`.`vtiger_pacientesii`.`fechaactivacion` <= DATE_FORMAT(LAST_DAY((NOW() - INTERVAL 1 MONTH)),
'%Y-%m-%d 23:59:59'))
AND (ISNULL(`crm`.`vtiger_pacientesii`.`fincontrato`)
OR (`crm`.`vtiger_pacientesii`.`fincontrato` > DATE_FORMAT(LAST_DAY((NOW() - INTERVAL 1 MONTH)),
'%Y-%m-%d 23:59:59')))),
1,
0)) AS `Activos`,
#04 SUM((IF(((`crm`.`vtiger_pacientesii`.`fechaactivacion` <= DATE_FORMAT(LAST_DAY((NOW() - INTERVAL 1 MONTH)),
'%Y-%m-%d 23:59:59'))
AND (ISNULL(`crm`.`vtiger_pacientesii`.`fincontrato`)
OR (`crm`.`vtiger_pacientesii`.`fincontrato` > DATE_FORMAT(LAST_DAY((NOW() - INTERVAL 1 MONTH)),
'%Y-%m-%d 23:59:59')))),
1,
0) + IF((`crm`.`vtiger_pacientesii`.`fincontrato` BETWEEN DATE_FORMAT((NOW() - INTERVAL 1 MONTH),
'%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY((NOW() - INTERVAL 1 MONTH)),
'%Y-%m-%d 23:59:59')),
1,
0))) AS `Atendidos`,
#05 SUM(IF((`crm`.`vtiger_pacientesii`.`fincontrato` BETWEEN DATE_FORMAT((NOW() - INTERVAL 1 MONTH),
'%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY((NOW() - INTERVAL 1 MONTH)),
'%Y-%m-%d 23:59:59')),
1,
0)) AS `Retirados`,
#06 SUM(IF((`crm`.`vtiger_pacientesii`.`fechaactivacion` BETWEEN DATE_FORMAT((NOW() - INTERVAL 1 MONTH),
'%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY((NOW() - INTERVAL 1 MONTH)),
'%Y-%m-%d 23:59:59')),
1,
0)) AS `Nuevos`
FROM
JOIN `crm`.`vtiger_pacientesiicf` ON ((`crm`.`vtiger_pacientesii`.`pacientesiiid` = `crm`.`vtiger_pacientesiicf`.`pacientesiiid`)))
LEFT JOIN `crm`.`vtiger_account` ON ((`crm`.`vtiger_pacientesii`.`accountid` = `crm`.`vtiger_account`.`accountid`)))
LEFT JOIN `crm`.`vtiger_accountgrouprelation` ON ((`crm`.`vtiger_accountgrouprelation`.`accountid` = `crm`.`vtiger_pacientesii`.`accountid`)))
LEFT JOIN `crm`.`vtiger_empresas` ON ((`crm`.`vtiger_pacientesii`.`empresa` = `crm`.`vtiger_empresas`.`empresasid`)))
LEFT JOIN `crm`.`vtiger_accountscf` ON ((`crm`.`vtiger_pacientesii`.`accountid` = `crm`.`vtiger_accountscf`.`accountid`)))
LEFT JOIN `crm`.`vtiger_servicios` ON ((`crm`.`vtiger_pacientesii`.`ultimoservicio` = `crm`.`vtiger_servicios`.`serviciosid`)))
LEFT JOIN `crm`.`vtiger_servicioscf` `serv_cf` ON ((`crm`.`vtiger_pacientesii`.`ultimoservisahos` = `serv_cf`.`serviciosid`)))
LEFT JOIN `crm`.`vtiger_servicioscf` ON ((`crm`.`vtiger_pacientesii`.`ultimoservicio` = `crm`.`vtiger_servicioscf`.`serviciosid`)))
LEFT JOIN `crm`.`vtiger_cf_1480` ON ((`crm`.`vtiger_pacientesiicf`.`cf_1480` = `crm`.`vtiger_cf_1480`.`cf_1480`)))
WHERE
((NOT ((`crm`.`vtiger_pacientesiicf`.`cf_1619` LIKE '%nunca%')))
AND ((`crm`.`vtiger_pacientesii`.`fincontrato` BETWEEN DATE_FORMAT((NOW() - INTERVAL 1 MONTH),
'%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY((NOW() - INTERVAL 1 MONTH)),
'%Y-%m-%d 23:59:59'))
OR ISNULL(`crm`.`vtiger_pacientesii`.`fincontrato`)
OR (`crm`.`vtiger_pacientesii`.`fincontrato` > DATE_FORMAT(LAST_DAY((NOW() - INTERVAL 1 MONTH)),
'%Y-%m-%d 23:59:59')))
AND (`crm`.`vtiger_pacientesii`.`empresa` <> 3130039)
AND (`crm`.`vtiger_pacientesii`.`estadocomodato` IS NOT NULL)
AND (`crm`.`vtiger_pacientesii`.`estadocomodato` <> '')
AND (NOT ((`crm`.`vtiger_pacientesii`.`identificador_paciente` LIKE '%PRUEBA%')))
AND (`crm`.`vtiger_pacientesii`.`pacientesiiid` <> '7007366')
AND (`crm`.`vtiger_pacientesii`.`pacientesiiid` <> '7797925')
AND (`crm`.`vtiger_pacientesii`.`pacientesiiid` <> '8514902')
AND (`crm`.`vtiger_pacientesii`.`pacientesiiid` <> '8515843')
AND (`crm`.`vtiger_pacientesii`.`estadocomodato` <> 'ANULADO')
AND ((SELECT
`crm`.`vtiger_accountgrouprelation`.`groupname`
FROM
`crm`.`vtiger_accountgrouprelation`
WHERE
(`crm`.`vtiger_accountgrouprelation`.`accountid` = `crm`.`vtiger_pacientesii`.`accountid`)) <> '')
AND (NOT ((`crm`.`vtiger_pacientesiicf`.`cf_1619` LIKE 'Por Act%')))
AND (`crm`.`vtiger_servicios`.`nombre_servicio` <> '')
AND (NOT ((`crm`.`vtiger_servicios`.`nombre_servicio` LIKE '%PAP%'))))
GROUP BY `agno_mes` , `crm`.`vtiger_account`.`epsconsolidada` , `regional` , `crm`.`vtiger_pacientesiicf`.`cf_1619` , `crm`.`vtiger_pacientesii`.`sexo` , `crm`.`vtiger_pacientesii`.`tipo_regimen` , `crm`.`vtiger_pacientesii`.`grupoetareo` , `crm`.`vtiger_pacientesii`.`departamento` , `crm`.`vtiger_servicioscf`.`cf_2799`
| 
