Help needed to design SQL Query

byomakesh · April 2, 2021, 8:58pm

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.
We can use multiple #temp tables or a procedure if needed.
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')

yosiasz · April 3, 2021, 12:34am

Please provide your data in a consumable format with proper DDL and DML something like this

create table #resulta(work_day varchar(50), open_time time, close_time time)

insert into #resulta
select' Monday', '07:00:00', '15:00:00'

byomakesh · April 3, 2021, 3:31am

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

We can use multiple TEMP tables to get needed result

harishgg1 · April 3, 2021, 5:37am

Hi

Row number
Partition by member
Order by termination date descending

From this
All the rows where row number = 1

RogierPronk · April 4, 2021, 8:32am

This should help you a lot, you should be able to go from here.

-- Determ MemberProgram
WITH MemberProgram AS
(
SELECT MemberID, ProgramName, EffectiveDt, TerminateDt
, LAG(ProgramName,1,'None') OVER (PARTITION BY MemberID ORDER BY EffectiveDT) AS PrevProgramName
, LAG(TerminateDt,1,DATEFROMPARTS(1900,1,1)) OVER (PARTITION BY MemberID ORDER BY TerminateDt) AS PrevTerminateDt
, DATEDIFF(DAY,EffectiveDt, LAG(TerminateDt,1,DATEFROMPARTS(1900,1,1)) OVER (PARTITION BY MemberID ORDER BY TerminateDt)) AS DaysBetweenContract
, CASE
WHEN DATEDIFF(DAY,EffectiveDt, LAG(TerminateDt,1,DATEFROMPARTS(1900,1,1)) OVER (PARTITION BY MemberID ORDER BY TerminateDt)) < -15 OR
ProgramName <> LAG(ProgramName,1,'None') OVER (PARTITION BY MemberID ORDER BY EffectiveDT)
THEN
1
ELSE
0
END AS NewContract
FROM tempMemberUpdt
)
SELECT * FROM MemberProgram;

-- Determ MemberProgram
WITH MemberProgram AS
(
SELECT MemberID, ProgramName, EffectiveDt, TerminateDt
, LAG(ProgramName,1,'None') OVER (PARTITION BY MemberID ORDER BY EffectiveDT) AS PrevProgramName
, LAG(TerminateDt,1,DATEFROMPARTS(1900,1,1)) OVER (PARTITION BY MemberID ORDER BY TerminateDt) AS PrevTerminateDt
, DATEDIFF(DAY,EffectiveDt, LAG(TerminateDt,1,DATEFROMPARTS(1900,1,1)) OVER (PARTITION BY MemberID ORDER BY TerminateDt)) AS DaysBetweenContract
, CASE
WHEN DATEDIFF(DAY,EffectiveDt, LAG(TerminateDt,1,DATEFROMPARTS(1900,1,1)) OVER (PARTITION BY MemberID ORDER BY TerminateDt)) < -15 OR
ProgramName <> LAG(ProgramName,1,'None') OVER (PARTITION BY MemberID ORDER BY EffectiveDT)
THEN
1
ELSE
0
END AS NewContract
FROM tempMemberUpdt
),
LastContract AS
(
SELECT
MemberID,
ProgramName,
EffectiveDt
FROM MemberProgram
WHERE
EffectiveDt=(SELECT MAX(EffectiveDt) FROM MemberProgram MemberProgram_ WHERE MemberProgram_.MemberID=MemberProgram.MemberID AND NewContract=1)
)
SELECT * FROM LastContract

byomakesh · April 5, 2021, 4:51am

Will implement this script and confirm. Thank you