Today, I want to share with you that a few days ago, the authorities at my current job requested a report containing information on all the patients who passed away. This report needed to include all their data, such as their name, case number, gender, date of birth, as well as the date of their last surgery, date of death, the name of the surgeon who performed their last surgery, and the specialty of that surgeon. Additionally, the request specified that the information should be filtered to include only cardiovascular surgeons.
This was a complex information request, as the data was distributed across multiple tables in the database. Therefore, I created a complex query divided into five steps to finally obtain the requested list with all the specified details.
The first step was to create a table containing all the case numbers from the cirugiasDefunciones table, with the goal of having a unique record for each case number and the date of their last surgery.
select expediente as expediente, MAX(fechaProgramacion) as ultimaCirugia into #tempCirDef from cirugiasDefunciones GROUP by expedienteThe second step was to cross-reference the table with unique case numbers and the date of the last surgery, created in the previous step, with the table containing data on patients who have passed away. This allowed us to retrieve the relevant patient information for each case number.
select pd.*, cd.ultimaCirugia into #tempXcir from pacientesDefSIIMA pd left join #tempCirDef cd on cd.expediente = pd.expedienteThe third step was to obtain the ID of the surgeon who last operated on the patient and their specialty. To achieve this, a subquery with the ROW_NUMBER() function and the OVER clause was used. This subquery aimed to create a table that contains the surgeon’s ID and specialty, but only for the last surgery performed on the patient. The resulting table from the subquery was then cross-referenced with the temporary table created in the previous step, using the case number as the key. As a result, up to this step, we have a table that contains all the data on patients who passed away, along with the date of their last surgery (steps 1 and 2), and the surgeon’s ID and specialty for that last surgery (step 3).
SELECT tc.*, cd_last.claveCirujano1, cd_last.claveEspecialidad into #tempX FROM #tempXcir tc LEFT JOIN ( SELECT cd.expediente, cd.claveCirujano1, ce.claveEspecialidad, ROW_NUMBER() OVER (PARTITION BY cd.expediente ORDER BY cd.fechaProgramacion DESC) AS RowNum --Esta parte asigna un número secuencial a cada fila dentro de un grupo particionado por la columna cd.expediente FROM cirugiasDefunciones cd LEFT JOIN catalogoMedicoSIIMA cm ON cm.claveEmpleado = cd.claveCirujano1 LEFT JOIN catalogoEspeSIIMA ce ON ce.claveEspecialidad = cd.claveEspecialidad1 ) AS cd_last ON tc.expediente = cd_last.expediente AND cd_last.RowNum = 1;The fourth step was similar to the previous one. In the subquery of this query, a table was generated that extracts from the egresosDef table the case number, the discharge date, and a new column that assigns a consecutive number, partitioned by the case number and ordered by discharge date in descending order. This means that the case number with the most recent discharge date will receive the number 1 in the new column, and so on. With this resulting table, a cross-reference was made with the temporary table created in the previous step, using the case number as the key. As a result, all the fields from the previously created temporary table were obtained, but with the addition of the most recent discharge date, which corresponds to the patient’s date of death.
SELECT tx.*, ed_last.fechaEgreso AS fechaDefuncion into #temp FROM #tempX tx LEFT JOIN ( SELECT expediente, fechaEgreso, ROW_NUMBER() OVER (PARTITION BY expediente ORDER BY fechaEgreso DESC) AS RowNum FROM egresosDef ) AS ed_last ON tx.expediente = ed_last.expediente AND ed_last.RowNum = 1;The fifth and final step involved cross-referencing the previous temporary table with the physician and specialty catalogs to obtain the names of the physicians and specialties, allowing us to replace the IDs with actual names. Finally, the data was filtered to include only records related to cardiovascular surgeons (I applied the filter using the surgeon’s name).
select t.*, cm.nombreMedico, ce.especialidad from #temp t left join catalogoMedicoSIIMA cm on cm.claveEmpleado = t.claveCirujano1 left join catalogoEspeSIIMA ce on ce.claveEspecialidad = t.claveEspecialidad where nombreMedico like 'ROGELIO%' or nombreMedico like 'MITZI%' OR nombreMedico like 'HUGO CABRERA%' OR nombreMedico like '%COLIN%'The final result was a table that contains the case number, full name of the patient, gender, date of birth, date of their last surgery, date of death, the name of the surgeon who performed that last surgery, and the surgeon’s specialty.
