Before I begin, credit to bitsmed for helping with this.
The below code looks at the first time an identifier (customer) has requested re activation of their account to completion and the messages which occurred in-between.
However I am trying to add a tolerance piece which says. If there were further messages within 2 weeks after the completion date (status is complete) then count it as part of the same request.
In the data set, one request happened in 2016 and one in 2017 (2 weeks past the completion date of the one which occurred in 2016 so its considered a 2nd request, thus the 2nd line)
So result I am after is:
Any Help is Appreciated
CREATE TABLE #temp ( Identifier varchar(40)NOT NULL ,Created_Date DATETIME NULL ,Completed_Date DATETIME NULL ,SN_TYPE varchar(20) NOT NULL ,SN_STATUS varchar(20) NOT NULL ); INSERT INTO #temp VALUES ('200895691','20160127 15:15:00','20160127','Re-Activattion', 'COMP'); INSERT INTO #temp VALUES ('200895691','20160127 16:25:00','20670131','Re-Activattion', 'N-CO'); INSERT INTO #temp VALUES ('200895691','20160128 01:22:00','20160130','Re-Activattion', 'N-CO'); INSERT INTO #temp VALUES ('200895691','20170127 12:15:00','20170128','Re-Activattion', 'COMP'); select identifier ,case when sum(case when SN_STATUS='COMP' and SN_TYPE = 'Re-Activattion' then 1 else 0 end)>0 then str(datediff(day ,MIN(case when SN_TYPE = 'Re-Activattion' then Created_Date else null end ) ,min(case when (SN_TYPE = 'Re-Activattion' and SN_STATUS='COMP' ) then Completed_Date else null end ) ) ) when sum(case when SN_TYPE='Re-Activattion' then 1 else 0 end)>0 then 'NOT COMP' else 'NO RE-ACT' end as RE_ACT_COMPLETION_TIME ,Sum(CASE WHEN SN_STATUS = 'N-CO' THEN 1 ELSE 0 END) as [RE-AN NCO #] from #temp group by identifier ;