Insert Into @YourTable Values

('200-001','S1','1/15/2020',3)

,('200-001','Y23','3/4/2020',15)

,('200-001','M56','5/6/2020',17)

,('200-001','UN','8/31/2020',38)

,('200-001','T1','10/12/2020',78)

,('200-001','C T28','11/23/2020',91)

,('200-001','C M3','1/29/2021',93)

,('200-001','P1','4/22/2021',94)

,('200-001','CM9','8/4/2021',95)

,('200-001','CM10','9/4/2021',97)

;with cte1 as (

Select *

,Grp = sum( case when CHECK_IN like '_1' then 1 else 0 end ) over ( partition by [GRPNBR] order by date_of_check_in)

from @YourTable

)

, cte2 as (

Select *

,RN = row_number() over ( partition by Grp order by date_of_check_in)

,_Check_In = max( case when CHECK_IN like '_1' then CHECK_IN end ) over ( partition by Grp )

From cte1

)

Select GRPNBR

,Check_In = _Check_In

,Post_Check_In = max( Check_In )

,Date_of_check_in = max(Date_of_check_in)

,Check_In_Order = max(Check_In_Order)

From cte2

Where RN>1

Group By GRPNBR,_Check_In,Grp,_Check_In,RN

Order By Grp,RN