Guest User

Untitled

a guest
Apr 16th, 2018
79
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.54 KB | None | 0 0
  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
Add Comment
Please, Sign In to add comment