COUNTIF() equivalent and discharge 'status typing' equivalent in SSMS or ADF

Question

I am currently trying to create a table in SSMS that holds patient record information. Ideally, I can achieve my goals in SSMS alone, but I can also utilize ADF should that be a better tool.

The ultimate goal is outlined in part two, however, to achieve this I have had to do step one first:

1. The number of admissions prior and admissions ahead to any given row for an individual
2. Discharge 'status typing': the type of discharge from a facility

An example table is provided below. Here is a breakdown of each column/variable:

• Patient ID: ID attached to a patient when they admit to a facility
• Patient Master ID: ID attached to an individual that stays with them, even if they admit to more than one facility
• Admission: The date when a patient admits to a facility
• Discharge: The date when a patient discharges from a facility
• Level of Care (LOC): Term referring to the level of care required to adequately address the patient's needs; Higher numbers indicate greater levels of care and more severe symptom severity
• Discharge Date

Patient ID	Patient Master ID	Admission	Discharge	Level of Care (LOC)	Discharge Date
1234-5	aBcDD-Ecnad9-09as	2022-09-01	2022-10-01	3	2022-10-01
123-6	aBcDD-Ecnad9-09as	2022-10-01	2022-10-31	2	2022-10-31

Below is an outline of what I've done in separate programs (excel for step one, and R for step two):

1. Admissions prior and admissions ahead: I originally did this in excel with a COUNTIF() function in excel, where each row counted the number of Patient Master ID before a given observation/row (admissions prior) and after a given observation/row (admissions ahead)

Therefore, the example table would look something like this:

Patient ID	Patient Master ID	Admission	Discharge	(LOC)	Admissions Ahead	Admissions Prior
1234-5	aBcDD-Ecnad9-09as	2022-09-01	2022-10-01	3	1	0
123-6	aBcDD-Ecnad9-09as	2022-10-01	2022-10-31	2	0	1

2. I would then load this table into R and create custom functions to accomplish the 2nd goal: The type of discharge from a facility. (Please note that although I am looking to add an Admission Type column in my work, I will leave it out, as it involves similar logic as Discharge Type)

A brief description of discharge types:

• True discharge: When a patient discharges, but does not readmit at another facility (i.e., Admissions Ahead == 0)
• Stepdown: When a patient discharges, but admits to a lower level of care (LOC) (i.e., Admissions Ahead == 1 & LOC_new < LOC_old
• Lateral Transfer: When a patient discharges, but admits to the same level of care (LOC) (i.e., Admissions Ahead == 1 & LOC_new == LOC_old
• Step-Up: When a patient discharges, but admits to a higher level of care (LOC) (i.e., Admissions Ahead == 1 & LOC_new > LOC_old

At the end the table would look something like this (note that I have truncated the table by removing Admissions Prior):

Patient ID	Patient Master ID	Admission	Discharge	(LOC)	Admissions Ahead	Discharge Type
1234-5	aBcDD-Ecnad9-09as	2022-09-01	2022-10-01	3	1	Stepdown
123-6	aBcDD-Ecnad9-09as	2022-10-01	2022-10-31	2	0	True Discharge

In practice there will be many rows, and I know I will have to have the table sorted by Admission in order to have these functions work properly. What I'm hoping to get out of this post is to determine if I can do something similar in SSMS (or ADF if needed) to achieve this result in an efficient manner.

One post that's close to achieving my first step is here. However, if I understand the suggested queries using partition by wouldn't work for counting Admissions Prior.

Thank you for your time.

Answer 1

To count the prior and following admission counts per patient, you can use a windowed COUNT() function constrained with OVER( PARTITION BY ... ORDER BY ... ROWS ... ), where ROWS limits the window to either all preceding or all following admissions.

COUNT(*) OVER(
    PARTITION BY PatientMasterID
    ORDER BY Admission
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS AdmissionsBefore,
COUNT(*) OVER(
    PARTITION BY PatientMasterID
    ORDER BY Admission
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
    ) AS AdmissionsAfter,

Note that this will count all preceding or following admissions for a patient, not just those with consecutive date ranges (transfers). Achieving the latter gets way more complicated and would involve preprocessing and assigning admission-group numbers that can later be used to limit calculations.

In order to calculate Discharge Type you will need to first extract the Level of Care from the next admission to compare with the current. Although I didn't see it outlined in your requirements, I expect you really only want to consider immediate following admissions, not those weeks, months, or years down the road. To determine that, you will also need to capture the next admission date for comparison with the current discharge date.

Both can be done using the LEAD() function with similar OVER PARTITION BY ... ORDER BY ...) constraints.

LEAD(Admission) OVER(PARTITION BY PatientMasterID ORDER BY Admission) AS NextAdmission,
LEAD(LevelOfCare) OVER(PARTITION BY PatientMasterID ORDER BY Admission) AS NextLevelOfCare

If you then wrap all that up in a subselect, the outer select can reference the available information in a CASE expression to select the appropriate discharge type.

I've pulled all this together with a few more rows of sample data in this db<>fiddle.

I'm not sure what the difference between Discharge and Discharge Date was in your sample data, so I ignored the latter.

And as for calculating the Admission Type, I expect you will need to access the prior LOC and perhaps Discharge date, which can be done using the LAG() function in a manner similar to LEAD(). (Left as an exercise.)