捕获列中事件的顺序

Question

我正试图找到一种方法，根据每个患者id的月份编号来捕获和统计药品订单。要标记的id是一对一的关系。基本上，每年我都想统计每个患者的药物路径数量，并按标签进行汇总。药物路径是通过从较低月份到较高月份来确定的。一条路径上也可能有2种以上的药物。以下是示例输入和输出。

我正在使用SSMS。

输入：

Year
2020/21		2020/21		2020年/21年		2020/21年		2020/21年		2020/21		2020/21年			2020年/21年		2020/21年		2020/21		2020年/21年		2020年/21年		2020/21年		2021/22		2021/22		2021/22		2021/22		2021/22		2021/22		2021/22		2021/22		2021/22		2021/22		2021/22		2021/22		2021/22		2021/22

您可以尝试以下操作：

With CTE AS(
Select *, ROW_NUMBER() Over (Partition By ID,Year,Tag Order By Year, Month) mn_rn,
ROW_NUMBER() Over (Partition By ID,Year,Tag Order By Year DESC, Month DESC) mx_rn
From datatable
),
CTE2 As
(
Select *, MIN(Case When mn_rn = 1 Then Drug End) Over (Partition By ID,Year,Tag) from_d,
MAX(Case When mx_rn = 1 Then Drug End) Over (Partition By ID,Year,Tag) to_d from cte
Where mn_rn =1 or mx_rn=1
)
Select Year, (from_d + Case When from_d<>to_d then '->' + to_d Else '' End) [Drug Order],
COUNT(Distinct ID) [Count], Tag
From CTE2
Group By Year, from_d, to_d, Tag
Order By Year

请参阅db<gt；不停摆弄

更新，将中间药物值包括在药品订单中：

With CTE AS(
Select *,
ROW_NUMBER() Over (Partition By ID,Year,Tag Order By Year, Month) -
ROW_NUMBER() Over (Partition By ID,Year,Tag,drug Order By Year, Month) grp
From datatable
),
CTE2 As
(
Select *,
STUFF((Select N'->' + MAX(T.Drug) From cte T Where T.id = D.id and T.year = D.year And T.tag = D.tag 
Group By T.id,T.year,T.tag,T.grp,T.drug Order by max(t.month) 
FOR XML PATH(N''), TYPE).value('(./text())[1]','varchar(max)'),1, 2, N'') drug_order
from cte D
)
Select Year,  drug_order [Drug Order],
COUNT(Distinct ID) [Count], Tag
From CTE2
Group By Year,Tag,drug_order
Order By Year

请参阅db<gt；不停摆弄