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.