question

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

Need HELP to build SQL statement

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. Thanks in advance.

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

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

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.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered Viorel-1 commented

Hi @ByomakeshPanda-8839,

Welcome to Microsoft Q&A!

Please refer below:

 ;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<15 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    Silver    2021-05-01    2021-12-31
 M4    GOLD    2021-02-10    2021-12-31
 M5    GOLD    2021-05-10    2021-12-31

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.

· 1
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.


Does not seem to work well in this case:

 INSERT INTO tempMemberUpdt values ('M6', 'Silver', '1/1/2021', '1/30/2021')
 INSERT INTO tempMemberUpdt values ('M6', 'Silver', '7/1/2021', '7/30/2021')
 INSERT INTO tempMemberUpdt values ('M6', 'GOLD', '10/1/2021', '12/31/2021')
0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered ByomakeshPanda-8839 commented

Try one of solutions:

 ;
 with Q0 as
 (
     select *,
         lag(TerminateDt) over (partition by MemberID order by EffectiveDt) as ptd,
         lead(EffectiveDt) over (partition by MemberID order by EffectiveDt) as ned
     from tempMemberUpdt
 ),
 Q1 as
 (
     select *,
         case when datediff(day, ptd, EffectiveDt) >= 15 then 1 else 0 end as g
     from Q0
 )
 select MemberID, max(ProgramName) as ProgramName, min(EffectiveDt) as EffectiveDt, max(TerminateDt) as TerminateDt
 from tempMemberUpdt t
 where not exists (select * from Q1 where MemberID = t.MemberID and EffectiveDt > t.EffectiveDt 
                     and ( ProgramName <> t.ProgramName or g = 1))
 group by MemberID
 order by MemberID
· 1
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.

HI,
I need one minor change to accommodate dual membership. I submitted another question . URL is below.

https://docs.microsoft.com/en-us/answers/questions/343808/need-help-to-design-sql-in-sqlserver.html

Thank you very much.

0 Votes 0 ·
RusselLoski-0634 avatar image
0 Votes"
RusselLoski-0634 answered
 with cte as *
 SELECT *
 , ROW_NUMBER() OVER (Partition BY MemberID Order By EffectiveDt desc, TerminateDt desc) as RN
 FROM tempMemberUpdt
    
 )
 SELECT * 
 from cte
 where RN = 1;
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.