Adding Date Tolerance to code - Part 2

Danii_PINK · August 26, 2017, 1:27pm

Hi

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:

result

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
;

bitsmed · August 27, 2017, 7:26am

Thanks for the credit

This might be something you can work with:

with cte1
  as (select *
            ,row_number() over(partition by identifier order by created_date) as rn
        from #temp
     )
    ,cte2
  as (select a.identifier
            ,a.rn as rn1
            ,b.rn as rn2
        from cte1 as a
             left outer join cte1 as b
                          on b.identifier=a.identifier
                         and b.sn_type='Re-Activattion'
                         and b.sn_status='COMP'
                         and b.rn<a.rn
                         and b.completed_date>=a.created_date-14
       where a.sn_type='Re-Activattion'
         and a.sn_status='COMP'
         and b.identifier is null
     )
    ,cte3
  as (select a.identifier
            ,a.rn1
            ,min(isnull(b.rn1,9999999)) as rn2
        from cte2 as a
             left outer join cte2 as b
                          on b.identifier=a.identifier
                         and b.rn1>a.rn1
       group by a.identifier
               ,a.rn1
     )
select b.identifier
      ,case
          when sum(case when b.sn_status='COMP' and b.sn_type='Re-Activattion' then 1 else 0 end)>0
          then str(datediff(day
                           ,min(case
                                   when b.sn_type='Re-Activattion'
                                   then b.created_date
                                   else null
                                end
                               )
                           ,min(case
                                   when (b.sn_type='Re-Activattion'
                                    and  b.sn_status='COMP'
                                        )
                                   then b.completed_date
                                   else null
                                end
                               )
                           )
                  )
          when sum(case when b.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 b.sn_status='N-CO' then 1 else 0 end) as [RE-AN NCO #]
  from cte3 as a
       inner join cte1 as b
               on b.identifier=a.identifier
              and b.rn>=a.rn1
              and b.rn<a.rn2
 group by b.identifier
         ,a.rn1
;

Danii_PINK · August 27, 2017, 8:12am

Bitsmed, thanks again for stepping in and saving the day.

I just gave it a shot with the below sample data and while it works with the example I posted with 100% the below does sample set does something funny.

INSERT INTO #temp
VALUES ('200895691','20160127 15:15:00','20160127','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES ('200895691','20160127 14:25:00','20160131','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','20170101','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('200895691','20170102 12:15:00','20170102','Re-Activattion', 'COMP');

Gives:

gives

In 2017 one was created and did not completed and then was raised the very next day again and completed so it should be 1 day and 1 NCO
Should

bitsmed · August 27, 2017, 9:26am

with cte1
  as (select *
            ,row_number() over(partition by identifier order by created_date) as rn
        from #temp
     )
    ,cte2
  as (select a.identifier
            ,a.rn as rn1
            ,b.rn as rn2
        from cte1 as a
             left outer join cte1 as b
                          on b.identifier=a.identifier
                         and b.sn_type='Re-Activattion'
/*                         and b.sn_status='COMP'*/
                         and b.rn<a.rn
                         and b.completed_date>=a.created_date-14
       where a.sn_type='Re-Activattion'
/*         and a.sn_status='COMP'*/
         and b.identifier is null
     )
    ,cte3
  as (select a.identifier
            ,a.rn1
            ,min(isnull(b.rn1,9999999)) as rn2
        from cte2 as a
             left outer join cte2 as b
                          on b.identifier=a.identifier
                         and b.rn1>a.rn1
       group by a.identifier
               ,a.rn1
     )
select b.identifier
      ,case
          when sum(case when b.sn_status='COMP' and b.sn_type='Re-Activattion' then 1 else 0 end)>0
          then str(datediff(day
                           ,min(case
                                   when b.sn_type='Re-Activattion'
                                   then b.created_date
                                   else null
                                end
                               )
                           ,min(case
                                   when (b.sn_type='Re-Activattion'
                                    and  b.sn_status='COMP'
                                        )
                                   then b.completed_date
                                   else null
                                end
                               )
                           )
                  )
          when sum(case when b.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 b.sn_status='N-CO' then 1 else 0 end) as [RE-AN NCO #]
  from cte3 as a
       inner join cte1 as b
               on b.identifier=a.identifier
              and b.rn>=a.rn1
              and b.rn<a.rn2
 group by b.identifier
         ,a.rn1
;

Danii_PINK · August 28, 2017, 1:45am

Hi bitsmed
Sorry for the delayed response and being a bother again, but was working out your query.
Found that removing the comp status doesn't group quiet the right way.
Have a look at the below if you can.
Thanks again!

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','2017-01-11 00:00:00.000',NULL,'Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('200895691','2017-01-23 00:00:00.000',NULL,'Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('200895691','2017-01-23 00:00:00.000',NULL,'Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('200895691','2017-03-10 00:00:00.000',NULL,'Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('200895691','2017-03-13 00:00:00.000','2017-03-13 00:00:00.000','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES ('200895691','2017-04-05 00:00:00.000',NULL,'Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('200895691','2017-04-06 00:00:00.000','2017-04-06 00:00:00.000','Re-Activattion', 'COMP');

SELECT *
FROM #temp
order by created_date

;
with cte1
  as (select *
            ,row_number() over(partition by identifier order by created_date) as rn
        from #temp
     )
    ,cte2
  as (select a.identifier
            ,a.rn as rn1
            ,b.rn as rn2
        from cte1 as a
             left outer join cte1 as b
                          on b.identifier=a.identifier
                         and b.sn_type='Re-Activattion'
/*                         and b.sn_status='COMP'*/
                         and b.rn<a.rn
                         and b.completed_date>=a.created_date-14
       where a.sn_type='Re-Activattion'
/*         and a.sn_status='COMP'*/
         and b.identifier is null
     )
    ,cte3
  as (select a.identifier
            ,a.rn1
            ,min(isnull(b.rn1,9999999)) as rn2
        from cte2 as a
             left outer join cte2 as b
                          on b.identifier=a.identifier
                         and b.rn1>a.rn1
       group by a.identifier
               ,a.rn1
     )
select b.identifier
      ,case
          when sum(case when b.sn_status='COMP' and b.sn_type='Re-Activattion' then 1 else 0 end)>0
          then str(datediff(day
                           ,min(case
                                   when b.sn_type='Re-Activattion'
                                   then b.created_date
                                   else null
                                end
                               )
                           ,min(case
                                   when (b.sn_type='Re-Activattion'
                                    and  b.sn_status='COMP'
                                        )
                                   then b.completed_date
                                   else null
                                end
                               )
                           )
                  )
          when sum(case when b.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 b.sn_status='N-CO' then 1 else 0 end) as [RE-AN NCO #]
  from cte3 as a
       inner join cte1 as b
               on b.identifier=a.identifier
              and b.rn>=a.rn1
              and b.rn<a.rn2
 group by b.identifier
         ,a.rn1
;

Gives:
UPDATE

Expected:
SHOULD BE

bitsmed · August 28, 2017, 6:07pm

Came up with this. Not sure if it covers all your requirements, but it gives correct result on the sample data you provided in this thread.

with cte1
  as (select *
            ,row_number() over(partition by identifier order by created_date) as rn
        from #temp
       where sn_type='Re-Activattion'
     )
    ,cte2
  as (select identifier
            ,1 as rn
            ,1 as lev
        from cte1
       group by identifier
      union all
      select a.identifier
            ,a.rn as rn
            ,row_number() over(partition by a.identifier order by a.created_date)+1 as lev
        from cte1 as a
             inner join cte1 as b
                     on b.identifier=a.identifier
                    and b.rn=a.rn-1
                    and b.completed_date<=a.created_date-14
     )
    ,cte3
  as (select a.identifier
            ,a.rn as rn1
            ,max(isnull(b.rn-1,99999999)) as rn2
        from cte2 as a
             left outer join cte2 as b
                          on b.identifier=a.identifier
                         and b.lev=a.lev+1
       group by a.identifier
               ,a.rn
     )
select b.identifier
      ,case
          when sum(case when b.sn_status='COMP' and b.sn_type='Re-Activattion' then 1 else 0 end)>0
          then str(datediff(day
                           ,min(case
                                   when b.sn_type='Re-Activattion'
                                   then b.created_date
                                   else null
                                end
                               )
                           ,min(case
                                   when (b.sn_type='Re-Activattion'
                                    and  b.sn_status='COMP'
                                        )
                                   then b.completed_date
                                   else null
                                end
                               )
                           )
                  )
          when sum(case when b.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 b.sn_status='N-CO' then 1 else 0 end) as [RE-AN NCO #]
  from cte3 as a
       inner join cte1 as b
               on b.identifier=a.identifier
              and b.rn>=a.rn1
              and b.rn<=a.rn2
 group by b.identifier
         ,a.rn1
;

Danii_PINK · August 28, 2017, 11:45pm

Hi bitsmed
Thanks again for contributing.
SO SO SO SO close.
Worked on the example you provided but why did it reverse the counts on this sample?

INSERT INTO #temp
VALUES ('200895691','2017-04-05 12:10:46.000','2017-04-28 12:16:00.000','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('200895691','2017-05-24 15:05:33.000','2017-07-04 08:16:00.000','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES ('200895691','2017-06-14 11:21:11.000',NULL,'Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('200895691','2017-07-03 14:04:25.000',NULL,'Re-Activattion', 'N-CO');

The results the code produces for time is spot on but the NCO count should be revered. For when its Not Completed then 2 and for when its 41 days it should be 1 NCO.
Gives

bitsmed · September 3, 2017, 6:08am

Sorry for this late reply - been swamped with work.

I thought I'd understood your logic, but obviously I don't.

Please explain you logic so that it will cover all your scenarios.

Danii_PINK · October 5, 2017, 1:41am

Withdrawn and deleted