Overcoming Federal Challenges: How I Resolved Information Requests Using SQL

In the past few weeks, the hospital where I work transitioned from being under the Secretariat of Health of the State of Puebla to a federal regime called IMSS-BIENESTAR. Due to this change, many adjustments were made at the data level. For example, this new regime started sending us many more forms that needed to be filled out with data, statistics, and indicators. One of these forms was sent by federal authorities, aiming to gather information from various hospitals and medical units to strategically plan actions for the effective operation of the public health sector within the State of Puebla.

This form posed a small challenge in obtaining and calculating the data correctly as requested. Normally, information was reported using monthly metrics, for which I have everything automated to calculate the indicators monthly. However, this time, weekly information was required, which disrupted the usual way I handle data. Nonetheless, it wasn’t a major problem. I got to work and, in a single SQL script, created the necessary queries to obtain the required weekly data. Below are some examples of what they asked us to report and the query I used to obtain the data:

• Number of high-specialty surgical procedures performed: I performed a count of surgical procedures using information from the tables containing postoperative notes combined with the table of surgical procedures associated with those notes. I applied three conditions: the first to count only those procedures with the status «R» for completed, and the subsequent two conditions to define the reporting period of days requested:

 select count(rp.clavePx) as TotalProcedimientosQx from ciruDiaria cd INNER JOIN relacionCxPx rp ON rp.numNota = cd.numeroNota where rp.estatus = 'R' and fechaProgramacion <= '20240703 23:59' and fechaProgramacion >= '20240627 00:00'

• Specialty/high-specialty consultations performed: I declared two variables: one to store the dental consultations, which are found in a specific table, and another to store the consultations from the other hospital specialties, which are in a different table. In each variable, I recorded the number of consultations for the required reporting period. Finally, I summed both variables to obtain the total number of specialty consultations provided:

declare @consultaEspecialidad int; select @consultaEspecialidad = count(*) from consDiaria where fechaNota <= '20240703 23:59' and fechaNota >= '20240627 00:00' print @consultaEspecialidad declare @consultaEstomato int; select @consultaEstomato = count(*) from consDiariaEstomato where fechaNota <= '20240703 23:59' and fechaNota >= '20240627 00:00' print @consultaEstomato print @consultaEspecialidad + @consultaEstomato

• Emergency consultations performed: I counted the number of records, which represents the total number of emergency consultations, but only for the specific period requested in the report:

select count(*) from consDiariaUrg where fechaNota <= '20240703 23:59' and fechaNota >= '20240627 00:00'

• Emergency occupancy rate (this refers to how full or empty the emergency area was based on the patients who came in for emergency care): First, I had to modify the table containing the records of patients discharged from the hospital and from emergency services. The modification involved adding a new column that calculates the difference in days between the discharge date and the admission date (if the result is zero days, it should be recorded as one day). Next, I declared the variables needed to calculate the emergency occupancy rate. I then assigned values to each variable; for example, the value for the first variable is the sum of the values from the new column, but only for records where both the admission and discharge services correspond to the emergency area. Finally, using all the variables, I calculated the percentage of occupancy in the emergency area:

ALTER TABLE egrdspo ADD diasPaciente AS ( CASE WHEN DATEDIFF(DAY, fechaIngreso, fechaEgreso) = 0 THEN 1 ELSE DATEDIFF(DAY, fechaIngreso, fechaEgreso) END ); declare @totalDiasPacienteUrg INT; declare @diasCama int; declare @ocupacionUrgencias decimal(5,2); set @totalDiasPacienteUrg = ( select SUM(diasPaciente) from egrdspo where servicioEgreso IN ('CAR', 'OBS', 'URG') and servicioIngreso IN ('CAR', 'OBS', 'URG') and fechaEgreso <= '20240626 23:59' and fechaEgreso >= '20240620 00:00' ); set @diasCama = 14 * 7; set @ocupacionUrgencias = (CAST(@totalDiasPacienteUrg as DECIMAL(10, 2)) / @diasCama) * 100; print @ocupacionUrgencias

The previous examples illustrate some of the data I was asked for and how I obtained it. I really enjoyed creating this script, and now, responding to the federal form week by week is much easier.