Hi @Byomakesh Panda ,
This thread is similar with Need HELP to build SQL statement. You could consider to close this thread if necessary.
Please refer below similar method:
drop table if exists tempMemberUpdt
CREATE TABLE tempMemberUpdt
(
MemberID VARCHAR(10),
ProgramName VARCHAR(10),
EffectiveDt Date,
TerminateDt Date)
INSERT INTO tempMemberUpdt values ('M1', 'Silver' ,'1/1/2021' ,'1/31/2021')
INSERT INTO tempMemberUpdt values ('M1','GOLD', '2/1/2021' ,'12/31/2021')
INSERT INTO tempMemberUpdt values ('M2', 'GOLD' ,'1/1/2021' ,'1/31/2021')
INSERT INTO tempMemberUpdt values ('M2','GOLD', '2/15/2021' ,'12/31/2021')
INSERT INTO tempMemberUpdt values ('M3', 'GOLD' ,'1/1/2021' ,'1/31/2021')
INSERT INTO tempMemberUpdt values ('M3','GOLD', '2/10/2021' ,'3/31/2021')
INSERT INTO tempMemberUpdt values ('M3','GOLD', '4/1/2021' ,'12/31/2021')
INSERT INTO tempMemberUpdt values ('M4','Silver','1/1/2021','1/31/2021')
INSERT INTO tempMemberUpdt values ('M4','GOLD','2/10/2021','3/31/2021')
INSERT INTO tempMemberUpdt values ('M4','GOLD','4/12/2021','4/30/2021')
INSERT INTO tempMemberUpdt values ('M4','GOLD','5/10/2021','12/31/2021')
INSERT INTO tempMemberUpdt values ('M5','Silver','1/1/2021','1/31/2021')
INSERT INTO tempMemberUpdt values ('M5','GOLD','3/10/2021','3/31/2021')
INSERT INTO tempMemberUpdt values ('M5','GOLD','5/10/2021','12/31/2021')
;with cte as (
select *,lead(ProgramName) over (partition by MemberID order by EffectiveDt) as ned
from tempMemberUpdt
),cte1 as (
select *,lead(EffectiveDt) over (partition by MemberID,ProgramName order by EffectiveDt) as eff
from cte
where ProgramName=ned or ned is null)
,cte2 as (
select * ,DATEDIFF(DAY,TerminateDt,eff) gap
from cte1)
select MemberID,max(ProgramName) ProgramName,
min(EffectiveDt) EffectiveDt,max(TerminateDt) TerminateDt
from cte2
where gap<=28 or gap is null
group by MemberID
Output:
MemberID ProgramName EffectiveDt TerminateDt
M1 GOLD 2021-02-01 2021-12-31
M2 GOLD 2021-01-01 2021-12-31
M3 GOLD 2021-01-01 2021-12-31
M4 GOLD 2021-02-10 2021-12-31
M5 GOLD 2021-05-10 2021-12-31
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
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.