daily pastebin goal
95%
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
Top