Analysing what people come in for medical care can be important to identify what procedures are needed the most to improve and ehance support. Procedures are not necessarily surgical procedures, but could be medication reconcilation or lab result interpretation.
Let's look the different types of procedures performed in 2019 and their total number.
SELECT * FROM
(SELECT DESCRIPTION, count(*) AS Total_Procedures
FROM procedures
WHERE DATE >= '2019-01-01'
and DATE < '2020-01-01'
GROUP BY DESCRIPTION
) PROCS
ORDER BY Total_Procedures DESC
How many procedures were performed across the inpatient and ambulatory care settings?
SELECT ENC.encounterclass, count(*) 'Total Procedures per Class'
FROM procedures PROCS
join encounters ENC on PROCS.encounter = ENC.id
WHERE DATE >= '2019-01-01'
and DATE < '2020-01-01'
GROUP BY enc.encounterclass
Organizations that perfomed the most inpatient procedures in 2019.
Joining tables encounters (giving it an alia ENC) and organizations (giving it an alia ORG) at column "id".
SELECT * FROM procedures PROCS
join encounters ENC on PROCS.encounter = ENC.id
join organizations ORG on ENC.organization = ORG.id
WHERE DATE >= '2019-01-01'
and DATE < '2020-01-01'
right click and open in new tab to zoom in
Find the total number of procedures per organization that received inpatient care:
SELECT ENC.organization, count(*) as 'Total Procedures'
FROM procedures PROCS
join encounters ENC on PROCS.encounter = ENC.id
join organizations ORG on ENC.organization = ORG.id
WHERE DATE >= '2019-01-01'
and DATE < '2020-01-01'
and encounterclass = 'inpatient'
group BY enc.organization
Dataset source: https://synthetichealth.github.io/synthea/
Github repository: https://github.com/ib99