question

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

Need help to design SQL Query on below requirement

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

sql-server-transact-sql
· 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.

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem.

This permits is to copy and paste into a query window to develop a tested solution. It also helps to clarify your verbal description.

Without that you will only get guesses, and when the guesses are wrong it is only frustrating for everyone.

It also helps to give a brief description of the business rules. It seems that to do the latter, but only on detail level. What is the overall goal here, I am not sure that I can read out.

0 Votes 0 ·

In addition, give samples for gaps longer than 28 days.

0 Votes 0 ·
ByomakeshPanda-8839 avatar image
0 Votes"
ByomakeshPanda-8839 answered ByomakeshPanda-8839 published
  • 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')

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.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ByomakeshPanda-8839,

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.

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.