August 26, 2017 at 7:38 am
Hi
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)
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 14: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','20170101 12:15:00','20170102','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
;
RESULT I AM AFTER
August 28, 2017 at 2:08 pm
This is a variation on packing intervals. You can read more about that at New Solution to the Packing Intervals Problem. You just need to use the desired tolerance as the "end" of the interval.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply