Need help to design SQL in SQLServer

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.

I need solution specially for Dual Membership. createdate/ updatedate column is available in my database table. Please let me know if this needs to be added in my table script else please add that column and build the script

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')

--------------------- DUAL Member -------------

INSERT INTO tempMemberUpdt values ('M8','GOLD','1/1/2021','1/31/2021')
INSERT INTO tempMemberUpdt values ('M8','GOLD','2/1/2021','3/31/2021')
INSERT INTO tempMemberUpdt values ('M8','Silver','4/15/2021','6/30/2021')
INSERT INTO tempMemberUpdt values ('M8','GOLD','4/15/2021','6/30/2021') -- Most Recent record (createdt)

-- Result of MemberID: M8 Previous GOLD start date EffectiveDt: 01/01/2021 TerminateDt: '6/30/2021'

INSERT INTO tempMemberUpdt values ('M9','Silver','1/1/2021','1/31/2021')
INSERT INTO tempMemberUpdt values ('M9','GOLD','2/1/2021','3/31/2021')
INSERT INTO tempMemberUpdt values ('M9','Silver','4/15/2021','6/30/2021')
INSERT INTO tempMemberUpdt values ('M9','GOLD','4/15/2021','6/30/2021') -- Most Recent record (createdt)

-- Result of MemberID: M9 Previous GOLD start EffectiveDt: 02/01/2021 TerminateDt: '6/30/2021'

INSERT INTO tempMemberUpdt values ('M10','Silver','1/1/2021','1/31/2021')
INSERT INTO tempMemberUpdt values ('M10','GOLD','2/1/2021','3/31/2021')
INSERT INTO tempMemberUpdt values ('M10','Bronze','4/15/2021','6/30/2021')
INSERT INTO tempMemberUpdt values ('M10','Silver','4/15/2021','6/30/2021') -- Most Recent record (createdt)

-- Result of MemberID: M10 EffectiveDt: 04/15/2021 TerminateDt: '6/30/2021'

Thank you in advance.

Answer 1

Hi @Byomakesh Panda ,

Why should we ignore the first row of 'M10' in the expected output?

Please refer below with only data of DUAL Member:

drop table if exists #temp  

select identity(int,1,1) ID,*   
into #temp   
from  tempMemberUpdt  
  
;with cte as (  
select *, ROW_NUMBER() Over (partition by MemberID order By ID desc) rn  
 from #temp  
),cte1 as (  
select MemberID,ProgramName from cte   
where rn=1)  
,cte2 as (  
select *,lag(rn) over (partition by MemberID order by rn) lag  
from cte a  
where exists (select 1 from cte1  where MemberID=a.MemberID and ProgramName=a.ProgramName))  
select MemberID,ProgramName,min(EffectiveDt) EffectiveDt,max(TerminateDt) TerminateDt  
from cte2   
where rn-lag<=2 or lag is null  
group by MemberID,ProgramName  

Output:

MemberID ProgramName EffectiveDt TerminateDt  
M8 GOLD 2021-01-01 2021-06-30  
M9 GOLD 2021-02-01 2021-06-30  
M10 Silver 2021-04-15 2021-06-30  

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.