Island Group, Regroup

Transact-SQL
,
1

Sorry this has been resolved and closed

2

Not a friendly thing to do. It would be helpful to us all if you reposted your original question and, since it has been resolved, at least post a link to what you used to resolve it. Remember... we're all in this together. :wink:

Also, closing something that way means that you have to live with whatever the resolution is and, from what I've seen on the internet, there are a whole lot of solutions that need some help. Without knowing what you settled on, you're destroying any chance of possibly getting something much better.

3

Hi Jeff
This was not resolved thanks to somone.
It was my mistake I picked up in the code when I posted it.
It was an embarassing mistake and question and thus i removed it.
Hope that clears it up some more.
Else I would not remove it, as you said we are in it together.
But in this situation it was a miss post and I would like to save some face :wink:

4

Ah... got it. Thank you for the feedback. Many people do such a thing like what I posted. Glad to see you didn't.

Still, "Island Group, Regroup" sounds like an interesting topic. I can't speak for anyone else but I'd like to see your code on the subject, if that's possible.

5

No problem

Here you go

CREATE TABLE #temp
(
Identifier varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,VARIATION varchar(20) NOT NULL
,FLAG varchar(20) NOT NULL
)
;
INSERT INTO #temp
VALUES('64074558792','20160729','20160805','Re-Activattion','DONT DRIVE');
INSERT INTO #temp
VALUES('64074558792','20160812','20160812','Re-Activattion','DONT DRIVE');
INSERT INTO #temp
VALUES('64074558792','20160811','20160811','Re-Activattion','DRIVE');
INSERT INTO #temp
VALUES('64074558792','20160811','20160813','Re-Activattion','DONT DRIVE');
;

WITH Src AS (
SELECT Identifier, CreatedDate, CompletedDate, VARIATION, FLAG,
 ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY CreatedDate, CASE WHEN FLAG = 'DRIVE' THEN 1 ELSE 0 END, CompletedDate) AS rn,
 ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY CreatedDate, CASE WHEN FLAG = 'DRIVE' THEN 1 ELSE 0 END, CompletedDate) -
 ROW_NUMBER() OVER(PARTITION BY Identifier,CASE WHEN FLAG = 'DRIVE' THEN 1 ELSE 0 END ORDER BY CreatedDate, CompletedDate) AS grp
FROM #temp
),
Grouped AS (
SELECT Identifier, CASE WHEN FLAG = 'DRIVE' THEN 1 ELSE 0 END AS IsCOMP,
 MIN(CreatedDate) AS StartDate,
 COUNT(*) AS [RE-AN NCO #],
 MAX(rn) AS LastRn 
FROM Src
GROUP BY Identifier, CASE WHEN FLAG = 'DRIVE' THEN 1 ELSE 0 END, grp
),
grouped2 AS (SELECT Identifier, MAX(rn) AS maxRN
FROM [Src]
           
GROUP BY [Src].[Identifier])
    
Select *
From grouped2
6

Hmmmm... correct me if I'm wrong, please. Is the purpose of the code to determine which row has the (temporarily speaking) first "Don't Drive" row that appears after a "Drive" row?