SQL check change in status for ID month by month

user_1482566 0
Viewed 201

I'm trying to find a change in Risk level for patients on a monthly basis. Using the data below. I wanted to see - How many patients had an increase in risk level in a month. Eg: In the below table John had a Risk Level as 'Low' as of 05/09/2021 and the risk increased to 'High' on 05/10/2021. So in May I'd count John if I create a bar graph for patient with increase risk

+----+------------+------------+----------------+
| ID | MemberName | Risk level | Discharge Date |
+----+------------+------------+----------------+
| 1  | John Doe   | Low        | 03/05/2021     |
+----+------------+------------+----------------+
| 1  | John Doe   | Medium     | 05/10/2021     |
+----+------------+------------+----------------+
| 1  | John Doe   | High       | 06/10/2021     |
+----+------------+------------+----------------+
| 2  | Sam        | Medium     | 05/10/2021     |
+----+------------+------------+----------------+
| 2  | Sam        | Low        | 05/20/2021     |
+----+------------+------------+----------------+

Query

SELECT [ID], [MemberName], [Risk level], [Discharge Date],
DATEADD(month, DATEDIFF(month, 0,  [Discharge Date]), 0) as StartOfMonth,
COUNT(*) OVER (PARTITION BY ID, MONTH([Discharge Date])) as Increase_Level
--Decrease level
from Member_Risk

Expected Output

+----+------------+--------------+------------------------+------------------------+
| ID | MemberName | StartOfMonth | Increase_In_Risk_Level | Decrease_In_Risk_Level |
+----+------------+--------------+------------------------+------------------------+
| 1  | John Doe   | 03/01/2021   | No                     | No                     |
+----+------------+--------------+------------------------+------------------------+
| 1  | John Doe   | 05/01/2021   | Yes                    | No                     |
+----+------------+--------------+------------------------+------------------------+
| 1  | John Doe   | 06/01/2021   | Yes                    | No                     |
+----+------------+--------------+------------------------+------------------------+
| 2  | Sam        | 05/01/2021   | No                     | Yes                    |
+----+------------+--------------+------------------------+------------------------+

Since Sam had a change in risk level from Medium to low which is decrease the Decrease_In_Risk_Level flag is updated as 'Yes'

partition window-functions sql-server sql
2 Answers

With the use of the window function lag() over()

You may notice the CASE determining the Increase/Decrease. Ideally you would have a numeric risk rating, but this will work with the current dataset.

Example

Declare @YourTable Table ([ID] int,[MemberName] varchar(50),[Risk level] varchar(50),[Discharge Date] date)
Insert Into @YourTable Values 
 (1,'John Doe','Low','03/05/2021')
,(1,'John Doe','Medium','05/10/2021')
,(1,'John Doe','High','06/10/2021')
,(2,'Sam','Medium','05/10/2021')
,(2,'Sam','Low','05/20/2021')

;with cte as (
      Select * 
            ,RiskLag = lag([Risk Level],1,[Risk Level]) over (partition by id order by [Discharge Date])
            ,RN = row_number() over (partition by id,year([Discharge Date]),month([Discharge Date]) order by [Discharge Date] desc)
       From  @YourTable
)
Select ID
      ,MemberName
      ,StartOfMonth  = convert(date,dateadd(MONTH,datediff(MONTH,0,[Discharge Date]),0))
      ,Increase_Risk = case when right(RiskLag,1)>right([Risk level],1) 
                            then 'Yes'
                            else 'No'
                       end
      ,Derease_Risk = case when right(RiskLag,1)<right([Risk level],1) 
                            then 'Yes'
                            else 'No'
                       end

 From  cte
 Where RN=1

Results

ID  MemberName  StartOfMonth    Increase_Risk   Derease_Risk
1   John Doe    2021-03-01      No              No
1   John Doe    2021-05-01      Yes             No
1   John Doe    2021-06-01      Yes             No
2   Sam         2021-05-01      No              Yes
user_15700000

I have created a dummy column called riskScore which assigns the following scores: Low = 1, Med = 2, High = 3

This column is used as a comparison from the prior months. I gather the prior months risk score by using LAG(). Once I have the prior months value, I do a case statement to compare the riskscore vs prior_risk as follows:

WITH base as (
SELECT 
    *, 
    CASE WHEN risklevel = 'Low' THEN 1 
    WHEN risklevel = 'Medium' THEN 2 
    WHEN risklevel = 'High' THEN 3 END AS RiskScore 
FROM patient), 

score as (
SELECT 
   *, 
   LAG(riskscore) OVER (PARTITION BY id ORDER BY DischargeDt ASC) 
   AS prior_risk 
FROM base)

SELECT 
   *, 
  CASE WHEN prior_risk is NULL THEN NULL 
  WHEN riskscore > prior_risk THEN 'Increased' 
  ELSE 'Decreased' END AS status 
FROM score

Sample Output

NOTE: your start of month logic is correct, you can add that in to the provided logic if required. Also, the riskScore and prior_score columns are for demo purposes, you can remove it from your final output.

user_63535630
Related