Healthcare delivery SQL

Question

I'm having an issue with a practice assessment regarding a health dataset. I have been tasked with counting the total number of emergency room visits for individuals diagnosed with schizophrenia with the conditions that anyone with icd10_category = f20 needs to have all of the records recorded. I was originally thinking that I should use an IF else statement because I come from a back end mindset however, I've never done anything like this in sql and I'm having a hard time finding documentation for this issue. Below I have a mockup of the code I had theorized but I don't know if this will work. Can someone please help? This is also my first time posting so any guidance or constructive criticism is better than heavy judgements :)

begin

declare @newId varchar
declare @sum int


select * from dbo.['Test dataset - SQL - Sep# 2021 $']


/*

If icd10_category == 'f20'
id=newId;
+=newId;


else
return;


return sum(newId);
*/

Answer 1

I think the general issue to solve is this: you're imagining code written in a procedural style, then trying to squeeze it into a SQL-shaped box.

Specifically, you're trying to set variables and issue a series of instructions to get the thing you want.

This doesn't work as well as you might think, because SQL is a declarative language - you tell the database engine what you want it to give you and leave it to figure out the details (as opposed to giving it a set of instructions which it follows blindly, which is the procedural approach).

For this reason it will be hard to find documentation. Ultimately this will just take practice with SQL - learning a different approach to things and figuring out when to apply it. It's quite a nice way of doing things, once you get into the groove. But for now let's solve the concrete problem:

  SELECT patient_id, COUNT(*) AS n_visits
    FROM your_database.emergency_room_visits
   WHERE icd10_category = 'f20'
GROUP BY patient_id

Now, I lack your domain knowledge, so here I have imagined that emergency_room_visits is a table that looks like this:

patient_id | icd10_category |   date     | lots | of | other | stuff
-------------------------------------------------------------------
     1             'f19'      2022-01-01   ...
     2             'f20'      2022-01-01   ...
     2             'f20'      2022-02-14   ...
     3             'f20'      2022-02-15   ...

It will be up to you to look at the real table, but I imagine that:

• each record (row) corresponds to a patient visiting the emergency room
• each patient will have a unique ID (I called it patient_id)
• each patient will have something called icd10_category (I imagine there are a few values this can take, but only 'f20' indicates the condition of interest)

If we have all of these then the query I outlined above will return

patient_id | n_visits
---------------------
     2          2
     3          1

Now, let's try to understand the query:

  SELECT patient_id, COUNT(*) AS n_visits
    FROM your_database.emergency_room_visits
   WHERE icd10_category = 'f20'
GROUP BY patient_id

Breaking it down:

• ignore the SELECT to start with
• the data are coming FROM the table of interest
• we only care about records for patients with this condition, so only retrieve data WHERE icd10_category = 'f20'
• we want to return the number of records per patient, so we must GROUP BY patient_id

Now, returning to SELECT:

• we ask for patient_id (the thing we grouped by) and
• COUNT(*) - a shorthand for "the number of records (rows) associated with each patient_id" - which we rename to n_visits, because that's the interpretation of this number.

(COUNT is an example of an aggregation function that reduces many records/rows down to one result)

Hopefully you can see what I mean by this being a 'declarative' style. We told the database engine what we wanted. It then grabs the table you mentioned, does some processing on it, and returns a results table in the format you requested.