SQLTeam.com | Weblogs | Forums

Probability in SQL

sql2012

#1

Hi

I am not sure how you would pull this of in SQL but I was hoping someone could help me out.
I have the below data and would like to calculate:

When the Status is Re-Activation what the probability is of it going past 0 Completion Days by looking at the number of times it got 'REJE'(Rejected) and 'N-CO' (Not Complete) but also the time it took for the 'REJE' and 'N-CO' to occur.

Not really my area but it would be nice to learn something new.
I would like to create an extra column for each identifier telling me the probability of the it going past 0 days.
Thanks,
Danii

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 ('318403853','61030203647','20170512','20170518','De-Activated', 'COMP')
INSERT INTO #temp
VALUES('318616723','61030203647','20170613','20170613','Re-Activattion', 'N-CO')
INSERT INTO #temp
VALUES('318637013','61030203647','20170615','20170615','Re-Activattion', 'REJE')
INSERT INTO #temp
VALUES('318639191','61030203647','20170615','20170616','Re-Activattion', 'COMP')
INSERT INTO #temp
VALUES('318637791','61030203666','20170615','20170616','Re-Activattion', 'Rejected')
INSERT INTO #temp
VALUES('318637792','61030203666','20170614','20170615','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('000318634115','64074558782','20170615','20170615','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318636669','64074558782','20170615','20170615','Re-Activattion','COMP')
INSERT INTO #temp
VALUES('000318636873','64074558782','20170615','20170614','Re-Activattion','REJE')
INSERT INTO #temp
VALUES('000318623572','64074558782','20170614','20170614','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318627678','64074558782','20170614','20170614','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318614132','64074558782','20170613','20170613','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('00031861999','64074558799','20170613','20170613','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('00031861991','64074558791','20170613','20170613','De-Activated','N-CO')
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('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
                               )
                           ,max(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
;