Hi @Rayden P
You can try this query.
;with T1 as(
select *,month(EnrollmentDate) as month,year(EnrollmentDate) as year from #table
),T2 as(
select A.IDNUMBER,A.EnrollmentDate,A.EndDate,A.Status,
case when B.IDNUMBER is null and C.IDNUMBER is not null then C.month
when B.IDNUMBER is not null and C.IDNUMBER is null then A.month
when B.IDNUMBER is null and C.IDNUMBER is null then A.month end as jud
from T1 as A left outer join T1 as B on A.month = B.month - 1 and A.year = B.year
left outer join T1 as C on A.month = C.month + 1 and A.year = C.year)
select IDNUMBER,min(EnrollmentDate) as EnrollmentDate,max(EndDate) as EndDate,Status
from T2 group by IDNUMBER,Status,jud;
Output:
Best regards,
Percy Tang
If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.