Need HELP to build SQL statement

Question

Hi,
Need help to build needed SQL Query. For one member we have multiple rows. Based on condition, would like to select 1 row per member with Effective date and Termination Date. Calculation to Effective date needed. I can select MAX termination date Group by member will work for me. Thanks in advance.

CREATE TABLE tempMemberUpdt
(
MemberID VARCHAR(10),
ProgramName VARCHAR(10),
EffectiveDt Date,
TerminateDt Date)

-- Result Expected MemberID: M1, GOLD, EffectiveDt: 2/1/2021 and TerminateDt: 12/31/2021 as GOLD membership started from 2/1/2021 and renewed within 15 days gap
INSERT INTO tempMemberUpdt values ('M1','Silver','1/1/2021','1/31/2021')
INSERT INTO tempMemberUpdt values ('M1','GOLD','2/1/2021','3/31/2021')
INSERT INTO tempMemberUpdt values ('M1','GOLD','4/1/2021','4/30/2021')
INSERT INTO tempMemberUpdt values ('M1','GOLD','5/1/2021','12/31/2021')

-- Result Expected MemberID: M2, GOLD, EffectiveDt: 1/1/2021 and TerminateDt: 12/31/2021 as all membership are GOLD and renewed within 15 days gap
INSERT INTO tempMemberUpdt values ('M2','GOLD','1/1/2021','1/31/2021')
INSERT INTO tempMemberUpdt values ('M2','GOLD','2/1/2021','3/31/2021')
INSERT INTO tempMemberUpdt values ('M2','GOLD','4/1/2021','4/30/2021')
INSERT INTO tempMemberUpdt values ('M2','GOLD','5/1/2021','12/31/2021')

-- Result Expected MemberID: M3, Silver, EffectiveDt: 5/1/2021 and TerminateDt: 12/31/2021 as member Switch to Silver membership from 5/1/2021
INSERT INTO tempMemberUpdt values ('M3','Silver','1/1/2021','1/31/2021')
INSERT INTO tempMemberUpdt values ('M3','Silver','2/1/2021','3/31/2021')
INSERT INTO tempMemberUpdt values ('M3','GOLD','4/15/2021','4/30/2021')
INSERT INTO tempMemberUpdt values ('M3','Silver','5/1/2021','12/31/2021')

-- Result Expected MemberID: M4, GOLD, EffectiveDt: 2/10/2021 and TerminateDt: 12/31/2021 as GOLD membership started from 2/10/2021 (membership renewal gap 15 days allowed)
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')

-- Result Expected MemberID: M5, GOLD, EffectiveDt: 5/10/2021 and TerminateDt: 12/31/2021 as GOLD membership started from 5/10/2021 (renewal gap is more than allowed 15 days)
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')

Answer 1

Hi @Byomakesh Panda ,

Welcome to Microsoft Q&A!

Please refer below:

;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<15 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	Silver	2021-05-01	2021-12-31  
M4	GOLD	2021-02-10	2021-12-31  
M5	GOLD	2021-05-10	2021-12-31  

If above is not working, please provide more sample data and expected output.

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.

Answer 2

with cte as *
SELECT *
, ROW_NUMBER() OVER (Partition BY MemberID Order By EffectiveDt desc, TerminateDt desc) as RN
FROM tempMemberUpdt

)
SELECT * 
from cte
where RN = 1;

Answer 3

Try one of solutions:

;
with Q0 as
(
    select *,
        lag(TerminateDt) over (partition by MemberID order by EffectiveDt) as ptd,
        lead(EffectiveDt) over (partition by MemberID order by EffectiveDt) as ned
    from tempMemberUpdt
),
Q1 as
(
    select *,
        case when datediff(day, ptd, EffectiveDt) >= 15 then 1 else 0 end as g
    from Q0
)
select MemberID, max(ProgramName) as ProgramName, min(EffectiveDt) as EffectiveDt, max(TerminateDt) as TerminateDt
from tempMemberUpdt t
where not exists (select * from Q1 where MemberID = t.MemberID and EffectiveDt > t.EffectiveDt 
                    and ( ProgramName <> t.ProgramName or g = 1))
group by MemberID
order by MemberID