SQLTeam.com | Weblogs | Forums

Island Group, Regroup

Sorry this has been resolved and closed

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.

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:

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.

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

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?