CODEWITHSUNDEEP
sqlamazon-redshift
user8834780
user8834780

Reputation: 1670

case when.. then.. where

I have the following piece of code:

case when status=status2 and rank=5 then datediff(day, rep_onboard_date, client_signup_date) end as time_to_status2

where:

rank= row_number() over(partition by rep_id order by client_signup_date)

and:

status2= case when count(client_signup_date) over (partition by rep_id) >=5.

This takes the time difference between rep_onboard_date and client_signup_date of his 5th client.

This works fine however only the 5th row per rep is populated, while the rest are (null).

What I would like is that if any row is populated for the rep with time_to_status then all rows should carry the same value.

Simplified query:

with cte as (
select rep_id, rep_onboard_date, user_id, client_signup_date, /* a bunch of other fields,*/
   count(client_signup_date) over (partition by rep_id) as total_applicants,
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,
    from table1 r
    left join table2 u on r.user_id=u.user_id
    left join table3 pi on u.user_id=pi.user_id
    )

    select *, 
     case when status='status1' and rank=10 then datediff(day, advisor_onboard_date, client_signup_date) end as time_to_status1,
     case when status='status2' and rank=5 then datediff(day, advisor_onboard_date, client_signup_date) end as time_to_status2,
     case when status='status3' and rank=1 then datediff(day, advisor_onboard_date, client_signup_date) end as time_to_status3
    from cte

Current output: 

rep_id user_id rep_onboard_date client_signup_date status   rank  time_to_status
1      1       1/1/2018         1/5/2018           status2  1     (null)
1      2       1/1/2018         1/5/2018           status2  2     (null)
1      3       1/1/2018         1/6/2018           status2  3     (null)
1      4       1/1/2018         1/7/2018           status2  4     (null)
1      5       1/1/2018         1/10/2018          status2  5     9
1      6       1/1/2018         1/15/2018          status2  6     (null)

Expected output:

rep_id user_id rep_onboard_date client_signup_date status   rank  time_to_status
1      1       1/1/2018          1/5/2018          status2  1     9
1      2       1/1/2018          1/5/2018          status2  2     9
1      3       1/1/2018          1/6/2018          status2  3     9
1      4       1/1/2018          1/7/2018          status2  4     9
1      5       1/1/2018          1/10/2018         status2  5     9
1      6       1/1/2018          1/15/2018         status2  6     9

Upvotes: 1

Views: 78

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I believe what you want are window functions:

 select cte.*, 
        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
from cte;

Upvotes: 1

Aaron Dietz
Aaron Dietz

Reputation: 10277

It seems you don't want a CASE, you want a subquery. Something like this:

SELECT col1,
       col2,
       (SELECT datediff(day, rep_onboard_date, client_signup_date)
        FROM yourTable t2
        WHERE t2.rep_id = t.rep_id
        AND ((t.rank = 10  AND t.status = 'status')
        OR (t.rank = 5 AND t.status = 'status2')
        OR (t.rank = 1 AND t.status = 'status3'))) as time_to_status
FROM (yourTable or derivedTable with rank function) t

Upvotes: 1

Related Questions