SQL 逐月检查 ID 的状态变化

Question

我正在尝试每月为患者找到风险级别的变化。使用下面的数据。我想看看 - 有多少患者在一个月内风险水平增加。例如:在下表中，约翰在 2021 年 5 月 9 日的风险级别为“低”，而在 2021 年 5 月 10 日，风险增加到“高”。所以在五月，如果我为风险增加的患者创建条形图，我会算上约翰

+----+------------+------------+----------------+
| 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     |
+----+------------+------------+----------------+

查询

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

预期输出

+----+------------+--------------+------------------------+------------------------+
| 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                    |
+----+------------+--------------+------------------------+------------------------+

由于 Sam 的风险级别从“中”更改为“低”，因此降低了 Decrease_In_Risk_Level 标志更新为"is"

Answer 1

带窗函数的使用lag() over()您可能会注意到 CASE确定增加/减少。理想情况下，您会有一个数字风险评级，但这适用于当前数据集。
示例

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

结果

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