Help with code (small change)

Transact-SQL
1

Hi

I have the below code, which mostly works correctly with some other scenarios I tested.

However, for the example below I am trying to count the number for N-CO (non completions) until completion but it return 5 instead of 3.

And i am trying to create another column to count the PARTIALLY which is 2.

Any once have some input as to why?
What do I need to change?

CREATE TABLE #temp
(
Identifier varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,SN_Type varchar(20) NOT NULL
,SN_Status varchar(20) NOT NULL
)
;
INSERT INTO #temp
VALUES('64074558792','20160729','20160805','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160729','20160805','Re-Activattion','PARTIALLY');
INSERT INTO #temp
VALUES('64074558792','20160809','20160809','Re-Activattion','PARTIALLY');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160812','20160812','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160811','20160811','Re-Activattion','COMP');
INSERT INTO #temp
VALUES('64074558792','20160811','20160813','Re-Activattion','N-CO');
;

WITH Src AS (
SELECT Identifier, CreatedDate, CompletedDate, SN_Type, SN_Status,
 ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY CreatedDate, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END, CompletedDate) AS rn,
 ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY CreatedDate, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END, CompletedDate) -
 ROW_NUMBER() OVER(PARTITION BY Identifier,CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END ORDER BY CreatedDate, CompletedDate) AS grp
FROM #temp
),
Grouped AS (
SELECT Identifier, CASE WHEN SN_Status = 'COMP' 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 SN_Status = 'COMP' THEN 1 ELSE 0 END, grp
),
grouped2 AS (SELECT Identifier, MAX(rn) AS maxRN
FROM [Src]
           
GROUP BY [Src].[Identifier])
    
SELECT s.Identifier,
    CASE WHEN isComp = 0
    THEN
        CAST(DATEDIFF(day,g.StartDate,s.CreatedDate) AS VARCHAR(25))
    ELSE
        'NOT COMPLETED'
    END AS RE_ACT_COMPLETION_TIME,
 g.[RE-AN NCO #]
FROM Src s
INNER JOIN Grouped g ON g.Identifier = s.Identifier
  AND g.LastRn + 1 = s.rn
JOIN grouped2 g2 ON [g2].[Identifier] = [s].[Identifier]
WHERE s.SN_Status = 'COMP'
    OR (SN_Status <> 'COMP' AND maxRN = [s].[rn])
  
ORDER BY rn;
DROP TABLE #temp