Create a lapsed concept based on logic across ever

问题:

I am trying to get to a lapsed_date which is when there are >12 weeks (ie. 84 days) for a given ID between:

1) onboarded_at and current_date (if no applied_at exists) - this means lapsed_now if >84 days

2) onboarded_at and min(applied_at) (if one exists)

3) each consecutive applied_at

4) max(applied_at) and current_date - this means lapsed_now if >84 days

If there are multiple instances where he lapsed, then we only show the latest lapsed date.

The attempt I have works for most but not all cases. Can you assists make it work universally?

Sample set:

CREATE TABLE #t
(
  id VARCHAR(10),
  rank INTEGER,
  onboarded_at DATE,
  applied_at DATE
  );

INSERT INTO #t VALUES
('A',1,'20180101','20180402'),
('A',2,'20180101','20180403'),
('A',3,'20180101','20180504'),
('B',1,'20180201','20180801'),
('C',1,'20180301','20180401'),
('C',2,'20180301','20180501'),
('C',3,'20180301','20180901'),
('D',1,'20180401',null)

Best attempt:

SELECT onb.id,
onb.rank,
onb.onboarded_at,
onb.applied_at,
onb.lapsed_now,
CASE WHEN lapsed_now = 1 OR lapsed_previous = 1
    THEN 1
    ELSE 0
END lapsed_ever,
CASE WHEN lapsed_now = 1
    THEN DATEADD(DAY, 84, lapsed_now_date)
    ELSE min_applied_at_add_84
END lapsed_date
FROM
(SELECT *,
CASE
    WHEN DATEDIFF(DAY, onboarded_at, MIN(ISNULL(applied_at, onboarded_at)) over (PARTITION BY id)) >= 84
        THEN 1
    WHEN DATEDIFF(DAY, MAX(applied_at) OVER (PARTITION BY id), GETDATE()) >= 84
        THEN 1
    ELSE 0
END lapsed_now,
CASE
    WHEN MAX(DATEDIFF(DAY, onboarded_at, ISNULL(applied_at, GETDATE()))) OVER (PARTITION BY id) >= 84
        THEN 1
    ELSE 0
END lapsed_previous,
MAX(applied_at) OVER (PARTITION BY id) lapsed_now_date,
DATEADD(DAY, 84, MIN(CASE WHEN applied_at IS NULL THEN onboarded_at ELSE applied_at END) OVER (PARTITION BY id)) min_applied_at_add_84
FROM #t
) onb

Current solution:

id  rank    onboarded_at    applied_at  lapsed_now  lapsed_ever lapsed_date
A   1       2018-01-01      2018-04-02  1           1           2018-07-27
A   2       2018-01-01      2018-04-03  1           1           2018-07-27
A   3       2018-01-01      2018-05-04  1           1           2018-07-27
B   2       2018-02-01      2018-08-01  1           1           2018-10-24
C   1       2018-03-01      2018-04-01  0           1           2018-06-24
C   2       2018-03-01      2018-05-01  0           1           2018-06-24
C   3       2018-03-01      2018-09-01  0           1           2018-06-24
D   1       2018-04-01      null        1           1           2018-06-24

Expected solution:

id  rank    onboarded_at    applied_at  lapsed_now  lapsed_ever lapsed_date
A   1       2018-01-01      2018-04-02   1           1         2018-07-27 (not max lapsed date)
A   2       2018-01-01      2018-04-03   1           1         2018-07-27
A   3       2018-01-01      2018-05-04   1           1         2018-07-27 (May 4 + 84)
B   1       2018-02-01      2018-08-01   0           1         2018-04-26 (Feb 1 + 84)
C   1       2018-03-01      2018-04-01   0           1         2018-07-24 
C   2       2018-03-01      2018-05-01   0           1         2018-07-24 (May 1 + 84)
C   3       2018-03-01      2018-09-01   0           1         2018-07-24 
D   1       2018-04-01      null         1           1         2018-06-24

回答1:

Bit of guesswork here, but hopefully this does the trick:

SELECT res.id,
res.rank,
res.onboarded_at,
res.applied_at,
res.lapsed_now,
CASE WHEN lapsed_now = 1 OR lapsed_previous = 1
    THEN 1
    ELSE 0
END lapsed_ever,
CASE
  WHEN lapsed_now = 1
    THEN DATEADD(DAY, 84, lapsed_now_date)
  WHEN applied_difference_gt84 IS NOT NULL
    THEN DATEADD(DAY, 84, applied_difference_gt84)
  WHEN DATEDIFF(DAY, min_applied_at_add_84, GETDATE()) < 84
    THEN DATEADD(DAY, 84, onboarded_at)
    ELSE min_applied_at_add_84
END lapsed_date
FROM (
SELECT *, MAX(applied_difference) OVER (PARTITION BY id ORDER BY rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) applied_difference_gt84
FROM
(
SELECT *,
CASE
    WHEN DATEDIFF(DAY, onboarded_at, MIN(ISNULL(applied_at, onboarded_at)) over (PARTITION BY id)) >= 84
          AND DATEDIFF(DAY, MAX(applied_at) OVER (PARTITION BY id), GETDATE()) >= 84
        THEN 1
    WHEN DATEDIFF(DAY, ISNULL(MAX(applied_at) OVER (PARTITION BY id), onboarded_at), GETDATE()) >= 84
        THEN 1
    ELSE 0
END lapsed_now,
CASE
    WHEN MAX(DATEDIFF(DAY, onboarded_at, ISNULL(applied_at, GETDATE()))) OVER (PARTITION BY id) >= 84
        THEN 1
    ELSE 0
END lapsed_previous,
 CASE
  WHEN DATEDIFF(MONTH, applied_at, LEAD(applied_at, 1) OVER (PARTITION BY id ORDER BY rank)) >= 2
   THEN applied_at
 ELSE NULL
 END applied_difference,
ISNULL(MAX(applied_at) OVER (PARTITION BY id), onboarded_at) lapsed_now_date,
DATEADD(DAY, 84, MIN(CASE WHEN applied_at IS NULL THEN onboarded_at ELSE applied_at END) OVER (PARTITION BY id)) min_applied_at_add_84
FROM #t
) onb
  ) res

Results:

id  rank    onboarded_at    applied_at  lapsed_now  lapsed_ever lapsed_date
A   1       2018-01-01      2018-04-02  1           1           2018-07-27
A   2       2018-01-01      2018-04-03  1           1           2018-07-27
A   3       2018-01-01      2018-05-04  1           1           2018-07-27
B   1       2018-02-01      2018-08-01  0           1           2018-04-26
C   1       2018-03-01      2018-04-01  0           1           2018-07-24
C   2       2018-03-01      2018-05-01  0           1           2018-07-24
C   3       2018-03-01      2018-09-01  0           1           2018-07-24
D   1       2018-04-01      (null)      1           1           2018-06-24

It's a bit messy because of the need to calculate the difference between the applied_at dates.

回答2:

@Jim, inspired by your answer, I created the following solution. I think it is easily understandable and intuitive, knowing the lapsed criteria:

SELECT id, onboarded_at, applied_at, 
max(case when (zero_applicants is not null and current_date - onboarded_at > 84) or (last_applicant is not null and current_date - last_applicant > 84) then 1 else 0 end) over (partition by id) lapsed_now,
max(case when (zero_applicants is not null and current_date - onboarded_at > 84) or (one_applicant is not null and applied_at - onboarded_at > 84)
     or (one_applicant is not null and current_date - applied_at > 84) or (next_applicant is not null and next_applicant- applied_at > 84)
     or (last_applicant is not null and current_date - last_applicant > 84) then 1 else 0 end) over(partition by id) lapsed_ever,
max(case when zero_applicants is not null and current_date - onboarded_at > 84 then onboarded_at + 84 
     when one_applicant is not null and applied_at - onboarded_at > 84 then onboarded_at + 84 
     when one_applicant is not null and current_date - applied_at > 84 then applied_at + 84 
     when next_applicant is not null and next_applicant - applied_at > 84 then applied_at + 84 
     when last_applicant is not null and current_date - last_applicant > 84 then last_applicant + 84 
     end) over (partition by id) lapsed_date
from (
select *, 
case when MAX(applied_at) OVER (PARTITION BY id) is null then onboarded_at end as zero_applicants,
case when count(applied_at) over(partition by id)=1 then onboarded_at end as one_applicant,
case when count(applied_at) over(partition by id)>1 then LEAD(applied_at, 1) OVER (PARTITION BY id ORDER BY applied_at) end as next_applicant,
case when LEAD(applied_at, 1) OVER (PARTITION BY id ORDER BY applied_at) is null then MAX(applied_at) over(partition by id) end as last_applicant
from #t
) res
order by id, applied_at