TSQL script based on the below input

Question

TSQL script based on the below input

Vivaan Shresth 101

Hi Team,

I have the below input:

Output should be base on the above table:

Guoxiong 8,206 Reputation points

2022-03-28T19:35:43.33+00:00

Do you only want to update this specific record? If no, you need to tell the logic to get your output..

Accepted answer

2 additional answers

Your answer

Guoxiong 8,206 Reputation points

2022-03-28T19:35:43.33+00:00

Do you only want to update this specific record? If no, you need to tell the logic to get your output..

Answer 1

Naomi Nosonovsky 8,431

Try:

;WITH Groups AS (SELECT
     *,
     ROW_NUMBER() OVER (PARTITION BY BI, t.MEMBER_ID,ID ORDER BY StartDate,EndDate) AS RN,

    LAG(EndDate,1) OVER (PARTITION BY BI, t.MEMBER_ID,ID ORDER BY StartDate, EndDate) AS PreviousEndDate
FROM @test t)

SELECT BI, x.MEMBER_ID, ID, MIN(StartDate) AS StartDate, MAX(EndDate) AS EndDate

FROM (SELECT
    *,
    CASE WHEN Groups.PreviousEndDate = DATEADD(DAY,-1,StartDate) THEN 0 ELSE 1 END AS IslandStartInd,
    SUM(CASE WHEN Groups.PreviousEndDate =DATEADD(DAY,-1,StartDate) THEN 0 ELSE 1 END) OVER (ORDER BY Groups.RN) AS IslandId
FROM groups) x
GROUP BY BI, X.MEMBER_ID, ID, x.IslandId

Vivaan Shresth 101 Reputation points

2022-03-28T20:15:50.17+00:00

Thank you :) It helped me.

Answer 2

Vivaan Shresth 101

Please help me on tsql code and the results should be like below:

Answer 3

Naomi Nosonovsky 8,431

This should help you to solve the problem - if you want tested solution, please post DDL (using table variable) and script to create data:

https://www.mssqltips.com/sqlservertutorial/9130/sql-server-window-functions-gaps-and-islands-problem/

Vivaan Shresth 101 Reputation points

2022-03-28T19:14:50.533+00:00

IF EXISTS(SELECT * FROM sys.tables WHERE name='Test')
BEGIN
DROP TABLE Test
END

CREATE TABLE Test
(
BI varchar(20) not null,
MEMBER_ID varchar(15) not null,
ID int not null,
StartDate Date null,
EndDate date null,
PID varchar(50) null
)

insert into Test (BI,MEMBER_ID,ID,StartDate,EndDate,PID)
values ('A123', '122', '1', '1/1/2019', '12/31/2019', 'A924'),
('A123', '122', '3', '1/1/2020', '12/31/2020', 'A924'),
('A123', '122', '1', '1/1/2021', '12/31/2021', 'A924'),
('A123', '122', '1', '1/1/2022', '12/31/9999', 'A924')
Ronen Ariely 15,206 Reputation points

2022-03-28T19:32:20.617+00:00

Hi @Anonymous

Check that you saw the message on Facebook in the Community Ninjas group :-)
Naomi Nosonovsky 8,431 Reputation points

2022-03-28T19:47:42.14+00:00

I haven't.

Share via

TSQL script based on the below input

2 additional answers

Your answer