SELECT DATE_FORMAT(bd_ordenesservicio.fecha_orden, '%Y%m') AS agno_mes,
YEAR(bd_ordenesservicio.fecha_orden) AS agno,
bd_ordenesservicio.Regional,
bd_ordenesservicio.tipo_entrega,
bd_ordenesservicio.MedioOperacion,
COUNT(*) AS Total_ordenes,
SUM(IF(bd_ordenesservicio.estadoauditoria = 'Cerrado', 1, 0)) AS Aud_Cerrado,
SUM(IF(bd_ordenesservicio.estadoauditoria = 'Revisado', 1, 0)) AS Aud_Revisado,
SUM(IF(bd_ordenesservicio.estadoauditoria = 'Pendiente', 1, 0)) AS Aud_Pendiente,
COUNT(DISTINCT bd_ordenesservicio.visitaid) AS Total_Visitas
FROM bd_ordenesservicio
LEFT JOIN bd_visitas ON bd_visitas.visitasid = bd_ordenesservicio.visitaid
WHERE cast(bd_ordenesservicio.fecha_orden AS date) BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW()), '%Y-%m-%d 23:59:59')
AND bd_ordenesservicio.empresa <> 3589077
AND bd_ordenesservicio.identificador_paciente NOT LIKE '%PRUEBA%'
AND (bd_visitas.estadovisita LIKE '%exitosa%'
OR bd_visitas.estadovisita LIKE '%fallida%')
AND bd_ordenesservicio.estado_orden <> 'ANULADA'
GROUP BY bd_ordenesservicio.Regional,
bd_ordenesservicio.tipo_entrega,
bd_ordenesservicio.MedioOperacion
|