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
-- *** 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;
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.
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
;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
