SQL按账户查询目标子账户后下一个唯一admit_date及对应discharge_date

为每个账户提取目标子账户之后的下一个唯一admit_date及对应的discharge_date，若不存在符合要求的下一个唯一admit_date，则标注为"No Readmit"。

• 本次涉及的目标账户为AAA、BBB、CCC、DDD
• 目标子账户为121、214、315、414、416
• CCC无后续唯一admit_date，需返回"No Readmit"
• DDD有两个目标子账户均存在对应的后续唯一admit_date
• 子账户不保证按数值顺序排列，例如BBB的子账户起始为221、结束为216

CREATE TABLE random_table
(
  account VarChar(50),
  subaccount VarChar(50),
  admit_date DATETIME,
  discharge_date DATETIME
);

INSERT INTO random_table
VALUES
('AAA',111,'6/20/2021','6/25/2021'),
('AAA',121,'6/20/2021','6/25/2021'),
('AAA',131,'7/1/2021','7/3/2021'),
('AAA',141, '8/2/2021', '8/5/2021'),
('BBB',216,'4/1/2021','4/3/2021'),
('BBB',213,'4/1/2021','4/3/2021'),
('BBB',221,'4/1/2021','4/3/2021'),
('BBB',215,'4/1/2021','4/3/2021'),
('BBB',216,'4/5/2021','4/10/2021'),
('CCC',313,'11/1/2020','11/5/2020'),
('CCC',314,'11/15/2020','11/17/2020'),
('CCC',315,'12/23/2020','12/24/2020'),
('CCC',316,'12/23/2020','12/24/2020'),
('DDD',414,'7/1/2021','7/3/2021'),
('DDD',412,'7/6/2021','7/7/2021'),
('DDD',416,'8/1/2021','8/5/2021'),
('DDD',417,'8/10/2021','8/15/2021');

select
    cte2.*
    ,case when cte2.subaccount in (111,121,131,141,216,213,221,215,216,313,314,315,316,414,412,416,417
    ) then lead(cte2.admit_date) over (order by cte2.account, cte2.row_nums)
          else null
          end second_admit
    from (
    select
        cte.*
        ,row_number() over (partition by cte.account order by  cte.row_num) row_nums
        from (
                select distinct
                hsp.subaccount
                ,row_number() over (partition by pat.account, hsp.admit_date order by pat.account) row_num
                ,case when row_number() over (partition by pat.account,hsp.admit_date order by pat.account) =1 then 'New Admit' else null end new_admit
                ,convert(varchar,hsp.admit_date,101) adm_date
                ,convert(varchar,hsp.discharge_date,101) disch_date
                ,pat.account
                from hsp_account hsp 
                left join patient pat on hsp.pat_id=pat.pat_id
                where pat.account in ('AAA','BBB','CCC','DDD')
                ) cte
        where cte.new_admit = 'New Admit'
        ) cte2

实现思路 #

1. 先筛选出所有目标子账户的基础信息，包括所属账户、对应入院出院时间
2. 提取每个账户下所有去重后的admit_date，按时间升序排序
3. 用OUTER APPLY关联取每个目标子账户所属账户下，时间晚于当前入院时间的最小入院时间及对应的出院时间
4. 无匹配结果时统一返回No Readmit

实现代码（基于测试表random_table） #

WITH target_sub AS (
    -- 筛选目标子账户的基础信息
    SELECT DISTINCT account, subaccount, admit_date target_admit_date, discharge_date target_discharge_date
    FROM random_table
    WHERE subaccount IN ('121','214','315','414','416')
),
unique_admit AS (
    -- 每个账户下去重后的入院时间，按时间升序排序
    SELECT 
        account, 
        admit_date, 
        discharge_date
    FROM (
        SELECT account, admit_date, discharge_date,
               ROW_NUMBER() OVER(PARTITION BY account, admit_date ORDER BY subaccount) dup_rn
        FROM random_table
    ) t
    WHERE dup_rn = 1
)
SELECT 
    ts.account,
    ts.subaccount,
    CONVERT(VARCHAR, ts.target_admit_date, 101) target_admit_date,
    CONVERT(VARCHAR, ts.target_discharge_date, 101) target_discharge_date,
    COALESCE(CONVERT(VARCHAR, ua.admit_date, 101), 'No Readmit') next_admit_date,
    COALESCE(CONVERT(VARCHAR, ua.discharge_date, 101), 'No Readmit') next_discharge_date
FROM target_sub ts
OUTER APPLY (
    SELECT TOP 1 admit_date, discharge_date
    FROM unique_admit ua
    WHERE ua.account = ts.account AND ua.admit_date > ts.target_admit_date
    ORDER BY ua.admit_date ASC
) ua
ORDER BY ts.account, ts.subaccount;

输出结果说明 #

执行上述代码后可得到符合要求的结果：

• AAA的目标子账户121对应的下一个唯一入院日期为07/01/2021
• 目标子账户214无匹配账户记录，对应后续入院记录为空，返回No Readmit
• CCC的目标子账户315无后续入院记录，返回No Readmit
• DDD的目标子账户414、416分别对应后续的07/06/2021、08/10/2021入院记录

内容的提问来源于stack exchange，提问作者Gabe Verzino