状态计算的条件时间

Question

我正在计算一个代表需要多长时间才能让x个客户申请服务:这意味着我需要在date_created - ie之间的时间。当代表到达某一“状态”的日期。当rep的客户端(= users)有一个非空的date_applied- ie时，就会达到状态。用户注册日期。

X是达到每个“状态”的最低标准，并与前面的一个问题联系起来：Aggregate case when inside non aggregate query，我目前正在计算“状态”，如下所示：

  case when count(date_applied) over (partition by rep_id) >=10 then 'status1'
    when count(date_applied) over (partition by rep_id) >=5 then 'status2'
    when count(date_applied) over (partition by rep_id) >=1 then 'status3'
    else 'no_status' end status

因此，需要10个客户到达status1，5个到达status2，1个到达status3。这些是每个“状态”的标准，因此，如果您有7个用户，那么仍然可以根据第五个用户申请的日期计算status2。

我认为计算time_to_status1/2/3 (我想得到的)应该如下所示：

case when count(date_applied) over (partition by rep_id) >=10 then
  datediff(day, date_created, date_applied for the 10th user that applied with that rep) end as time_to_status1,
case when count(date_applied) over (partition by rep_id) >=5 then
  datediff(day, date_created, date_applied for the 5th user that applied with that rep) end as time_to_status2,
case when count(date_applied) over (partition by rep_id) >=1 then
  datediff(day, date_created, date_applied for the 1st user that applied with that rep) end as time_to_status3

任何帮助都是非常感谢的！

-编辑--

样本当前数据：

rep_id user_id date_created          date_applied         status
1      1       1/1/2018 6:43:22 AM   1/5/2018 2:45:15 PM  status2 
1      2       1/1/2018 6:43:22 AM   1/5/2018 3:35:15 PM  status2 
1      3       1/1/2018 6:43:22 AM   1/6/2018 4:25:15 PM  status2 
1      4       1/1/2018 6:43:22 AM   1/7/2018 5:05:15 PM  status2 
1      5       1/1/2018 6:43:22 AM   1/10/2018 3:35:15 PM status2 
1      6       1/1/2018 6:43:22 AM   1/15/2018 12:55:23 PM status2 
2      7       1/12/2018 1:13:42 PM  1/15/2018 4:25:15 PM status3
2      8       1/12/2018 1:13:42 PM  1/16/2018 1:05:15 PM status3 
2      9       1/12/2018 1:13:42 PM  1/16/2018 3:35:15 PM status3 
3      10      1/20/2018 10:13:15 AM 1/26/2018 7:25:15 PM status3
4      11      1/21/2018 3:33:23 PM  (null)               no_status   

期望产出：

 rep_id user_id date_created          date_applied         status  time_to_status1  time_to_status2  time_to_status3
1      1       1/1/2018 6:43:22 AM   1/5/2018 2:45:15 PM  status2  (null)  9  (null)
1      2       1/1/2018 6:43:22 AM   1/5/2018 3:35:15 PM  status2  (null)  9  (null)
1      3       1/1/2018 6:43:22 AM   1/6/2018 4:25:15 PM  status2  (null)  9  (null)
1      4       1/1/2018 6:43:22 AM   1/7/2018 5:05:15 PM  status2  (null)  9  (null)
1      5       1/1/2018 6:43:22 AM   1/10/2018 3:35:15 PM status2  (null)  9  (null)
1      6       1/1/2018 6:43:22 AM   1/15/2018 12:55:23 PM status2  (null)  9 (null)
2      7       1/12/2018 1:13:42 PM  1/15/2018 4:25:15 PM status3  (null) (null) 3
2      8       1/12/2018 1:13:42 PM  1/16/2018 1:05:15 PM status3  (null) (null) 3
2      9       1/12/2018 1:13:42 PM  1/16/2018 3:35:15 PM status3  (null) (null) 3
3      10      1/20/2018 10:13:15 AM 1/26/2018 7:25:15 PM status3 (null) (null) 6
4      11      1/21/2018 3:33:23 PM  (null)               no_status (null) (null) (null)

rep_id=1有status2，因为他有6个具有非空date_applied的用户，所以在他的例子中，time_to_status2是基于第5位客户代表注册的date_applied：datediff(day, '1/1/2018 6:43:22 AM', '1/10/2018 3:35:15 PM') = 9 days

rep_id=2有status3，因为他有3个用户有一个非空date_applied，所以在他的例子中，time_to_status3是基于第一客户代表注册的date_applied：datediff(day, '1/12/2018 1:13:42 PM', '1/15/2018 4:25:15 PM') = 3 days

rep_id=3有status3，因为他有一个非空date_applied的1 (>=1)用户，所以在他的例子中，time_to_status3是datediff(day, '1/20/2018 10:13:15 AM', '1/26/2018 7:25:15 PM') = 6 days

Answer 1

基于@Parfait删除的提示，以及@Gordon对另一个问题的回答，我想出了一个答案：

with cte as 
(
initial query with:
 case when count(client_signup_date) over (partition by rep_id) >=10 then 'status1'
        when count(client_signup_date) over (partition by rep_id) >=5 then 'status2'
        when count(client_signup_date) over (partition by rep_id) >=1 then 'status3'
        else 'none' end status,
      row_number() over(partition by rep_id order by client_signup_date) as rank
)

select *, 
        max(case when status = 'status1' and rank = 10
                 then datediff(day, advisor_onboard_date, client_signup_date)
            end) over (partition by rep_id) as time_to_status1,
        max(case when status = 'status2' and rank = 5
                 then datediff(day, advisor_onboard_date, client_signup_date)
            end) over (partition by rep_id) as time_to_status2,
        max(case when status = 'status3' and rank = 1
                 then datediff(day, advisor_onboard_date, client_signup_date)
            end) over (partition by rep_id) as time_to_status3
into #t
from cte