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

Question

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

Please check this query:

create table #table(IDNUMBER INT, EnrollmentDate DATE, EndDate DATE,Status VARCHAR(20))
insert into #table values
(1397767672, '5/1/2023', '5/31/2023','Active')
,(1397767672, '7/1/2023', '7/30/2026','Active')
,(1397767672, '8/1/2023', '8/31/2023','Active')
,(1397767672, '10/1/2023', '10/30/2023','Active')
,(1397767672, '11/1/2023', '11/30/2040','Active')

;WITH CTE1 AS
(
 SELECT *,LAG(EnrollmentDate)OVER(PARTITION BY IDNUMBER ORDER BY EnrollmentDate)AS Pre_EnrollmentDate
 FROM #table
),CTE2 AS
(
 SELECT IDNUMBER,EnrollmentDate,EndDate,Status
       ,SUM(CASE WHEN DATEDIFF(MONTH,Pre_EnrollmentDate,EnrollmentDate)=1 THEN 0 ELSE 1 END)
	   OVER(PARTITION BY IDNUMBER ORDER BY EnrollmentDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Part_Num
 FROM CTE1
)
SELECT IDNUMBER,MIN(EnrollmentDate) AS EnrollmentDate,MAX(EndDate) AS EndDate,Status
FROM CTE2
GROUP BY IDNUMBER,Status,Part_Num

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. 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.

Answer 2

Given the above comment, the following may be a starting point. Gets all records where the next record is consecutive.

 SELECT * FROM Data WHERE EXISTS (     SELECT 1     FROM Data AS t2       WHERE (         (MONTH(Data.EnrollmentDate) = 12 AND MONTH(t2.EnrollmentDate) = 1 AND YEAR(t2.EnrollmentDate) = YEAR(Data.EnrollmentDate) + 1)         OR (MONTH(t2.EnrollmentDate) = MONTH(Data.EnrollmentDate) + 1 AND YEAR(t2.EnrollmentDate) = YEAR(Data.EnrollmentDate))     ) );