Urgent Cardiology and Cardiovascular Surgery Productivity Report for 2024: Data Analysis and Insights

Today, I was urgently asked for productivity data of cardiology and cardiovascular surgery specialists. Specifically, they wanted to know the total number of consultations, surgical procedures, and the average consultation deferral for each doctor so far in 2024. They requested the data grouped by doctor, with their specialty detailed.

To achieve this, I first identified the necessary tables to extract the information:

• agendaDiaria
• consDiaria
• ciruDiaria
• catalogoMedicoSIIMA
• catalogoEspeSIIMA

The first three tables contain the requested information, while the last two are dimension tables that, through the use of JOINS, allow obtaining the names of the doctors and the descriptions of their specialties via the corresponding IDs.

Below are the queries used to obtain all the requested information:

Consultations Provided by Doctor:

select cm.nombreMedico, ce.especialidad, count(*) as TotalConsultas from consDiaria cd INNER join catalogoMedicoSIIMA cm on cm.claveEmpleado = cd.claveMedico INNER JOIN catalogoEspeSIIMA ce on ce.claveEspecialidad = cd.claveEspecialidad where (ce.especialidad like '%CARDIO%') and cd.fechaNota >= '20240101 00:00' GROUP BY cm.nombreMedico, ce.especialidad ORDER BY TotalConsultas desc

Surgical Procedures Performed by Doctor:

select cm.nombreMedico, ce.especialidad, count(*) as TotalCirugías from ciruDiaria cd INNER JOIN catalogoMedicoSIIMA cm on cm.claveEmpleado = cd.claveCirujano1 inner join catalogoEspeSIIMA ce on ce.claveEspecialidad = cd.claveEspecialidad1 inner join relacionCxPx rp on rp.numNota = cd.numeroNota WHERE (rp.estatus = 'R') and (ce.especialidad like '%CARDIO%') and cd.fechaProgramacion >= '20240101' GROUP BY cm.nombreMedico, ce.especialidad ORDER BY TotalCirugías desc

Average Consultation Deferral by Doctor:

ALTER TABLE agendaDiaria ADD diferimientoAgenda AS ( DATEDIFF(DAY, fechaSolicitudCons, fechaProgramacion) ); select cm.nombreMedico, ce.especialidad, AVG(ad.diferimientoAgenda) as DiferimientoPromedioConsulta from agendaDiaria ad INNER JOIN catalogoMedicoSIIMA cm on cm.claveEmpleado = ad.claveMedico inner join catalogoEspeSIIMA ce on ce.claveEspecialidad = ad.claveEspecialidad WHERE (ce.especialidad like '%CARDIO%') and ad.fechaProgramacion >= '20240101' GROUP BY cm.nombreMedico, ce.especialidad ORDER BY DiferimientoPromedioConsulta SELECT * FROM catalogoEspeSIIMA WHERE especialidad NOT IN ('NO ESPECIFICADA') AND especialidad NOT LIKE '%RESIDENTE%';