question

ByomakeshPanda-8839 avatar image
0 Votes"
ByomakeshPanda-8839 asked ByomakeshPanda-8839 commented

Need help to design SQL in SQLServer

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.


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ByomakeshPanda-8839 commented

Hi @ByomakeshPanda-8839,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Will check and confirm. Thank you.

0 Votes 0 ·

Hi @ByomakeshPanda-8839,

Could you please provide any update?

Thank you.

Best regards
Melissa

0 Votes 0 ·