问题描述
我正在尝试到达lapsed_date，这是给定的ID在> 12周之间(即84天):
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和current_date(如果不存在applied_at)-如果超过84天，则表示lapsed_now
1) onboarded_at and current_date (if no applied_at exists) - this means lapsed_now if >84 days
2)onboarded_at和min(applied_at)(如果存在)
2) onboarded_at and min(applied_at) (if one exists)
3)每个连续的applied_at
4)max(applied_at)和current_date-这意味着lapsed_now，如果> 84天
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?
样本集:
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)
最佳尝试:
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
当前解决方案:
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
期望的解决方案:
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
推荐答案
@Jim，根据您的回答，我创建了以下解决方案.我认为这很容易理解，而且很直观，只要知道失效的标准即可:
@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
这篇关于根据每个ID每行的逻辑创建失效概念的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！