Hi
I have the below query (which bitsmed and Ifor helped me out with).
I am trying to make these sums:
, sum(case when (SN_STATUS = 'SENT' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN SENT #'
, sum(case when (SN_STATUS = 'ACCE' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN ACCE #'
, sum(case when (SN_STATUS = 'N-CO' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN N-CO #'
, sum(case when (SN_STATUS = 'REJE' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN REJE #'
Only sum when when they appear between:
str(datediff(day
,min(case
when SN_TYPE = 'Re-Activattion'
then CreatedDate
else null
end
)
,min(case
when (SN_TYPE = 'Re-Activattion'
and SN_STATUS='COMP'
)
then CompletedDate
else null
end
)
)
The full code:
CREATE TABLE #temp
(
ServiceNumber varchar(20) NOT NULL
,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('01031861999','64074558792','20160729','20160805','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('02031861999','64074558792','20160810','20160810','Re-Activattion','REJE')
INSERT INTO #temp
VALUES('02031861999','64074558792','20160812','20160812','Re-Activattion','SENT')
INSERT INTO #temp
VALUES('03031861999','64074558792','20160811','20160811','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 CreatedDate
else null
end
)
,min(case
when (SN_TYPE = 'Re-Activattion'
and SN_STATUS='COMP'
)
then CompletedDate
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 = 'SENT' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN SENT #'
, sum(case when (SN_STATUS = 'ACCE' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN ACCE #'
, sum(case when (SN_STATUS = 'N-CO' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN N-CO #'
, sum(case when (SN_STATUS = 'REJE' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN REJE #'
from #temp
WHERE Identifier = '64074558792'
group by identifier
;
SENT is 0 as it did not happen between the specified date of when the Re-Activation was first created to when it was completed.
Any help would be appreciated.
Thanks,
Danii