Hello All,
I have a requirement where we need to merge spans if the status is same for continues spans of a member. in the sample data below member 111 has continuedly having 3 status from 03/24/20 to 12/31/29 but in 2 lines in the raw data but it has to be merged because it has status 3 in both the lines but for member 222 continuedly there is no same status of a member. even though 5 and 4 status is repeating those are not for continues lines, so query should return 4 lines. for member 333 no change it should return one line. Thanks in advance for your help and let me know if my explanation is not clear.
IF OBJECT_ID('tempdb..#memberstatus_rawdata') IS NOT NULL BEGIN DROP TABLE #memberstatus_rawdata END
create table #memberstatus_rawdata
(
ID varchar(25),
Startdate date,
enddate date,
Status int)
insert into #memberstatus_Rawdata
values ('111', '2020-03-01', '2020-03-23', 4),
('111', '2020-03-24', '2020-03-26', 3),
('111', '2020-03-27', '2299-12-31', 3),
('222', '2020-01-01', '2020-01-08', 5),
('222', '2020-01-09', '2020-02-16', 4),
('222', '2020-02-17', '2020-02-19', 5),
('222', '2020-02-20', '2299-12-31', 4),
('333', '2020-01-01', '2299-12-31' , 4)
--input data
select * from #memberstatus_Rawdata
IF OBJECT_ID('tempdb..#memberstatus_Output') IS NOT NULL BEGIN DROP TABLE #memberstatus_Output END
create table #memberstatus_Output
(
ID varchar(25),
Startdate date,
enddate date,
Status int)
insert into #memberstatus_Output
values ('111', '2020-03-01', '2020-03-23', 4),
('111', '2020-03-24', '2299-12-31', 3),
('222', '2020-01-01', '2020-01-08', 5),
('222', '2020-01-09', '2020-02-16', 4),
('222', '2020-02-17', '2020-02-19', 5),
('222', '2020-02-20', '2299-12-31', 4),
('333', '2020-01-01', '2299-12-31' , 4)
--output data
select * from #memberstatus_Output
DROP TABLE #memberstatus_Rawdata
DROP TABLE #memberstatus_Output