Combining multiple rows data into single start and end date

Sabs · November 21, 2019, 1:30pm

Hello All,
I have a requirement to display member Active Start Date and member Active end date looking at startDate and endDate column in below requirement table. In requirement table a member can have multiple rows and my task is to look for the all the rows for a member and determine if there are any gaps. If there are gaps then output will have multiple rows depending on end date and start date. The output that I am looking for is displayed in the format as shown on expected answer table. Your help is greatly appreciated.
Requirement table::
memberId startDate EndDate
111 1/1/2019 1/31/2019
111 2/1/2019 2/28/2019
111 4/1/2019 4/30/2019
111 5/1/2019 5/31/2019
111 6/1/2019 6/30/2019
111 7/1/2019 7/31/2019
111 9/1/2019 9/30/2019
112 1/1/2019 9/30/2019

Expected Answer table::
memberId ActivestartDate ActiveEndDate
111 1/1/2019 2/28/2019
111 4/1/2019 7/31/2019
111 9/1/2019 9/30/2019
112 1/1/2019 9/30/2019

Ifor · November 20, 2019, 9:30pm

-- *** Test data ***
CREATE TABLE #t
(
	memberId int NOT NULL
	,startDate date NOT NULL
	,endDate date NOT NULL
	,PRIMARY KEY (memberId, startDate)
);
INSERT INTO #t
VALUES (111, '20190101', '20190131')
,(111, '20190201', '20190228')
,(111, '20190401', '20190430')
,(111, '20190501', '20190531')
,(111, '20190601', '20190630')
,(111, '20190701', '20190731')
,(111, '20190901', '20190930')
,(112, '20190101', '20190930');
-- *** End Test data ***

WITH Grps
AS
(
	SELECT memberId, startDate, endDate
			,DATEDIFF(day, 0, startDate)
				- COALESCE
				(
					SUM(DATEDIFF(day, startDate, endDate) + 1)
						OVER
						(
							PARTITION BY memberId
							ORDER BY startDate
							ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
						)
					,0
				) AS grp
	FROM #t
)
,Ranges
AS
(
	SELECT memberId, grp
		,MIN(startDate) AS startDate
		,Max(endDate) AS endDate
	FROM Grps
	GROUP BY memberId, grp
)
SELECT memberId, startDate, endDate
FROM Ranges
ORDER BY memberId, startDate;

jeffw8713 · November 20, 2019, 9:46pm

Here is one way:

Declare @testTable Table (
        MemberId int Not Null
      , StartDate date Not Null
      , EndDate date Not Null);

 Insert Into @testTable
 Values (111, '20190101', '20190131')
      , (111, '20190201', '20190228')
      , (111, '20190401', '20190430')
      , (111, '20190501', '20190531')
      , (111, '20190601', '20190630')
      , (111, '20190701', '20190731')
      , (111, '20190901', '20190930')
      , (112, '20190101', '20190930');

   With memberGroups
     As (
 Select tt.MemberId
      , tt.StartDate
      , tt.EndDate
      , GroupId = dateadd(month, -dense_rank() over(Partition By tt.MemberId Order By tt.StartDate), tt.StartDate)
   From @testTable      tt
        )
 Select m.MemberId
      , MemberStartDate = min(m.StartDate)
      , MemberEndDate = max(m.EndDate)
   From memberGroups     m
  Group By
        m.MemberId
      , m.GroupId;

Sabs · November 21, 2019, 1:36pm

Hello Harishgg1,
In requirement table, I have startdate and enddate for each member on monthly basis so each member will have multiple line. Output format I am looking for any member if they are enrolled for all months without skipping any month is one row per member and startdate is firstrow startdate and enddate is last row enddate. IF any member has not enroll in any of the month like in the requirement table example MemberId 111 then output will have multiple row. first row with startdate of first row and enddate of the month before skip. Example in requirement table is. for memberId 111 startdate January 1, 2019 and enddate of Feburary 28, 2019. March month is skipped so next row startdate is April 1, 2019 end date is July 2019. August is skipped Next row startdate is 9/1/2019, enddate is 9/30/2019. Thank you for your help.

Sabs · November 21, 2019, 1:42pm

Thank You very much.

Sabs · November 21, 2019, 2:10pm

thank you

harishgg1 · November 24, 2019, 7:42am

hi

i did this in a different way !!! using recursive CTE
as Jeff Moden says recursive CTE should not be used at all ..
very bad for performance !!!!
just proof of concept ( something different )

personally i feel Jeff Williams way Solution .. is Awesome
, GroupId = dateadd(month, -dense_rank() over(Partition By tt.MemberId Order By tt.StartDate), tt.StartDate)

please click arrow to the left for drop create data .. Script

drop table #t 
go 

-- *** Test data ***
CREATE TABLE #t
(
    memberId int NOT NULL
	,startDate date NOT NULL
	,endDate date NOT NULL
	,PRIMARY KEY (memberId, startDate)
);
INSERT INTO #t
VALUES (111, '20190101', '20190131')
,(111, '20190201', '20190228')
,(111, '20190401', '20190430')
,(111, '20190501', '20190531')
,(111, '20190601', '20190630')
,(111, '20190701', '20190731')
,(111, '20190901', '20190930')
,(112, '20190101', '20190930');
-- *** End Test data ***

select * from #t 
go

please click arrow to the left for SQL

;WITH rownumber_adding_cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) AS rn, 
               *
         FROM   #t), 
     recursive_cte 
     AS (SELECT * ,   1 as grp          
         FROM   rownumber_adding_cte 
         WHERE  rn = 1 
         UNION ALL 
         SELECT a.* , case when DATEDIFF(MM,b.startDate,a.startDate) > 1 then grp+1 else grp end 
         FROM   rownumber_adding_cte a 
                JOIN recursive_cte b 
                  ON a.rn = b.rn + 1) 
SELECT memberId,MIN(startdate),MAX(endDate) , grp
FROM   recursive_cte 
group by memberId,grp