Create A Lapsed Concept Based On Logic Across Every Row Per Id

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 me

Solution 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,
CASEWHEN lapsed_now =1OR lapsed_previous =1THEN1ELSE0END lapsed_ever,
CASEWHEN lapsed_now =1THEN DATEADD(DAY, 84, lapsed_now_date)
  WHEN applied_difference_gt84 ISNOTNULLTHEN DATEADD(DAY, 84, applied_difference_gt84)
  WHEN DATEDIFF(DAY, min_applied_at_add_84, GETDATE()) <84THEN DATEADD(DAY, 84, onboarded_at)
    ELSE min_applied_at_add_84
END lapsed_date
FROM (
SELECT*, MAX(applied_difference) OVER (PARTITIONBY id ORDERBY rank ROWSBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) applied_difference_gt84
FROM
(
SELECT*,
CASEWHEN DATEDIFF(DAY, onboarded_at, MIN(ISNULL(applied_at, onboarded_at)) over (PARTITIONBY id)) >=84AND DATEDIFF(DAY, MAX(applied_at) OVER (PARTITIONBY id), GETDATE()) >=84THEN1WHEN DATEDIFF(DAY, ISNULL(MAX(applied_at) OVER (PARTITIONBY id), onboarded_at), GETDATE()) >=84THEN1ELSE0END lapsed_now,
CASEWHENMAX(DATEDIFF(DAY, onboarded_at, ISNULL(applied_at, GETDATE()))) OVER (PARTITIONBY id) >=84THEN1ELSE0END lapsed_previous,
 CASEWHEN DATEDIFF(MONTH, applied_at, LEAD(applied_at, 1) OVER (PARTITIONBY id ORDERBY rank)) >=2THEN applied_at
 ELSENULLEND applied_difference,
ISNULL(MAX(applied_at) OVER (PARTITIONBY id), onboarded_at) lapsed_now_date,
DATEADD(DAY, 84, MIN(CASEWHEN applied_at ISNULLTHEN onboarded_at ELSE applied_at END) OVER (PARTITIONBY id)) min_applied_at_add_84
FROM #t
) onb
  ) res

Results:

idrankonboarded_atapplied_atlapsed_nowlapsed_everlapsed_dateA12018-01-01      2018-04-02  112018-07-27A22018-01-01      2018-04-03  112018-07-27A32018-01-01      2018-05-04  112018-07-27B12018-02-01      2018-08-01  012018-04-26C12018-03-01      2018-04-01  012018-07-24C22018-03-01      2018-05-01  012018-07-24C32018-03-01      2018-09-01  012018-07-24D12018-04-01(null)112018-06-24

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

Solution 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(casewhen (zero_applicants isnotnullandcurrent_date- onboarded_at >84) or (last_applicant isnotnullandcurrent_date- last_applicant >84) then1else0end) over (partitionby id) lapsed_now,
max(casewhen (zero_applicants isnotnullandcurrent_date- onboarded_at >84) or (one_applicant isnotnulland applied_at - onboarded_at >84)
     or (one_applicant isnotnullandcurrent_date- applied_at >84) or (next_applicant isnotnulland next_applicant- applied_at >84)
     or (last_applicant isnotnullandcurrent_date- last_applicant >84) then1else0end) over(partitionby id) lapsed_ever,
max(casewhen zero_applicants isnotnullandcurrent_date- onboarded_at >84then onboarded_at +84when one_applicant isnotnulland applied_at - onboarded_at >84then onboarded_at +84when one_applicant isnotnullandcurrent_date- applied_at >84then applied_at +84when next_applicant isnotnulland next_applicant - applied_at >84then applied_at +84when last_applicant isnotnullandcurrent_date- last_applicant >84then last_applicant +84end) over (partitionby id) lapsed_date
from (
select*, 
casewhenMAX(applied_at) OVER (PARTITIONBY id) isnullthen onboarded_at endas zero_applicants,
casewhencount(applied_at) over(partitionby id)=1then onboarded_at endas one_applicant,
casewhencount(applied_at) over(partitionby id)>1thenLEAD(applied_at, 1) OVER (PARTITIONBY id ORDERBY applied_at) endas next_applicant,
casewhenLEAD(applied_at, 1) OVER (PARTITIONBY id ORDERBY applied_at) isnullthenMAX(applied_at) over(partitionby id) endas last_applicant
from #t
) res
orderby id, applied_at