MAX Dates that are consecutive. i.e., EndDate to Next EnrollmentDate

Question

How to MAX() Consecutive dates i.e. 05/01/2023, 06/01/2023, 07/01/2023 .... 09/01/2023

Consecutive dates End Date, Enrollement

I want to be able to take the MIN(EnrollmentDate) and The Max(EndDate) Only if they are consecutive dates (months, year). To where it looks like the following:

Any assistance you can give is appreciated.

Ray

Answer 1

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.