This should get you started. It's not completely as you asked, but I ran out of time. If you've never used windowing functions then you'll need to read about them and understand how they work.
WITH NumberedforCompletion AS (
-- Get the no of completions so far per Identifier
SELECT *
, SUM(CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END) OVER (PARTITION BY Identifier ORDER BY CompletedDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS CompsSoFar
FROM #temp
where SN_Type = 'Re-Activattion'
) --select * from NumberedforCompletion
, ReverseNumbered AS (
-- Reverse the numbering so that the most recent is numbered 1
SELECT *, DENSE_RANK() OVER (PARTITION BY Identifier ORDER BY COALESCE(CompssoFar,0) DESC) AS ReverseNo
FROM NumberedforCompletion
)
, DateExtremes AS (
select distinct Identifier,
MAX(CompletedDate) OVER (PARTITION BY Identifier, ReverseNo) AS FinishDate,
MIN(CreatedDate) OVER (PARTITION BY Identifier, ReverseNo) AS StartDate,
SUM(CASE WHEN SN_Status = 'SENT' THEN 1 ELSE 0 END) OVER (PARTITION BY Identifier, ReverseNo) AS SentCount,
SUM(CASE WHEN SN_Status = 'N-CO' THEN 1 ELSE 0 END) OVER (PARTITION BY Identifier, ReverseNo) AS NCoCount
from ReverseNumbered
)
SELECT
Identifier
, StartDate
, CASE WHEN StartDate > FinishDate
THEN NULL
ELSE DATEDIFF(day,StartDate,FinishDate)
END AS RE_ACT_COMP_TIME
, NCoCount
, SentCount
FROM DateExtremes
John