daily pastebin goal
37%
SHARE
TWEET

Untitled

a guest Apr 16th, 2018 47 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select count(distinct case when cancelled_client_id is null and year(RUM.first_date) = year(date) and RUM.first_date <= .date then user_id
  2.     when cancelled_client_id is null and year(coalesce(RUM.first_date,RUR.first_date)) = year(date)
  3.     and coalesce(RUM.first_date,RUR.first_date) <= RUL.date then user_id end) as
  4. from RUL
  5. left join
  6. (
  7. select enrolled_client_id, min(date) as first_date
  8. from RUL
  9. where enrolled_client_id is not null
  10. group by enrolled_client_id
  11. ) RUR on RUR.enrolled_client_id=RUL.enrolled_client_id
  12. left join
  13. (
  14. select managed_client_id, min(date) as first_date
  15. from RUL
  16. where managed_client_id is not null
  17. group by managed_client_id
  18. ) RUM on RUM.managed_client_id=RUL.managed_client_id
  19.    
  20. count(distinct case when cancelled_client_id is null
  21.     and year(min(case when enrolled_client_id is not null then date end) over(partition by enrolled_client_id)) = year(date)
  22.     and min(case when enrolled_client_id is not null then date end) over(partition by enrolled_client_id) <= date
  23.     then user_id
  24. when cancelled_client_id_rev is null
  25.     and year(coalesce(
  26.     min(case when enrolled_client_id is not null then date end) over(partition by enrolled_client_id),
  27.     min(case when managed_client_id is not null then date end) over(partition by managed_client_id))) = year(date)
  28.     and coalesce(
  29.     min(case when enrolled_client_id is not null then date end) over(partition by enrolled_client_id),
  30.     min(case when managed_client_id is not null then date end) over(partition by managed_client_id)) <= date
  31.     then user_id end)
  32. from RUL
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top