SQL find next unique date and subaccount per account

Question

This query has a few requirements. The basic idea is that for each account, pull the next admit_date and corresponding discharge_date after the subaccount of interest. If there is no next admit_date that is unique, indicate "No Readmit."

I realize pictures are not encouraged on StackOverflow, but I feel a visual aid is helpful. The accounts of interest are AAA, BBB, CCC and DDD and the subaccounts of interest are 121, 214, 315, 414 and 416. Note that CCC has no next unique admit_date (would be "No Readmit"), DDD has two subaccounts of interest with a next unique admit_dates, and that the subaccounts are not necessarily in numerical order (i.e. BBB begins at 221 and ends at 216). So transforming this:

To this:

Here is the setup code:

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)

To solve for this, I've been trying to use a combination of row_numbers() to mark the first new instance of each admit_date (partitioned by the account), as well as CTEs to select those relevant rows. But obviously not there yet. Any suggestions? Here's what I have:

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

Answer 1

Hope this is what you're looking for:

with AI as
(
  select * from 
    (values ('AAA'), ('BBB'), ('CCC'), ('DDD')) A(account)
),
SI as
(
  select * from 
    (values (121), (214), (221), (315), (414), (416)) A(subaccount)
),
T as
(
  select * from random_table 
    where account in (select * from AI)
),
N as
(
  select
    T1.account,
    T1.subaccount,
    T1.admit_date,
    T1.discharge_date,
    T2.subaccount next_subaccount,
    T2.admit_date next_admit_date,
    T2.discharge_date next_discharge_date,
    row_number() 
      over(
        partition by T1.account, T1.subaccount 
        order by T2.admit_date) group_id
  from
    T T1
    left join
    T T2
    on
      T1.account = T2.account and
      T1.admit_date < T2.admit_date
  where
    T1.subaccount in (select * from SI)
)
select 
  account, subaccount, admit_date, 
  next_subaccount, next_admit_date, next_discharge_date
from N 
where
  N.group_id = 1

Please note that I print NULL instead of 'No Readmit'.