Need help to design SQL Query on below requirement

Byomakesh Panda 21 Reputation points
2021-04-02T21:20:47.197+00:00

Scenario 1
MemberID Program Effective Dt Terminate Date
M1 Silver 1/1/2021 1/31/2021
GOLD 2/1/2021 12/31/2021

Result - MerberID - M1, Effective Date: 2/1/2021 and Terminate Date: 12/31/2021
Program changed so take recent effective date as 2/1/2021 and terminate date as 12/31/2021

Scenario 2
MemberID Program Effective Dt Terminate Date
M2 GOLD 1/1/2021 1/31/2021
GOLD 2/15/2021 12/31/2021

Result - MemberID: M2, Effective Date: 1/1/2021 and Terminate Date: 12/31/2021
Max 28 days gap allowed between previous termination date and effective date. So Effective date should be 1/1/2021 and terminate date as 12/31/2021

Scenario 3
MemberID Program Effective Dt Terminate Date
M3 GOLD 1/1/2021 1/31/2021
GOLD 2/10/2021 3/31/2021
GOLD 4/1/2021 12/31/2021

Result - Effective Date: 1/1/2021 and Terminate Date: 12/31/2021
Max 28 days gap allowed between previous termination date and effective date. So Effective date should be 1/1/2021 and terminate date as 12/31/2021

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Byomakesh Panda 21 Reputation points
    2021-04-03T03:34:01.933+00:00

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

    0 comments
  2. MelissaMa-MSFT 24,176 Reputation points
    2021-04-05T07:01:33.633+00:00

    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.

    0 comments