Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- when cancelled_client_id is null and year(coalesce(RUM.first_date,RUR.first_date)) = year(date)
- and coalesce(RUM.first_date,RUR.first_date) <= RUL.date then user_id end) as
- from RUL
- left join
- (
- select enrolled_client_id, min(date) as first_date
- from RUL
- where enrolled_client_id is not null
- group by enrolled_client_id
- ) RUR on RUR.enrolled_client_id=RUL.enrolled_client_id
- left join
- (
- select managed_client_id, min(date) as first_date
- from RUL
- where managed_client_id is not null
- group by managed_client_id
- ) RUM on RUM.managed_client_id=RUL.managed_client_id
- count(distinct case when cancelled_client_id is null
- and year(min(case when enrolled_client_id is not null then date end) over(partition by enrolled_client_id)) = year(date)
- and min(case when enrolled_client_id is not null then date end) over(partition by enrolled_client_id) <= date
- then user_id
- when cancelled_client_id_rev is null
- and year(coalesce(
- min(case when enrolled_client_id is not null then date end) over(partition by enrolled_client_id),
- min(case when managed_client_id is not null then date end) over(partition by managed_client_id))) = year(date)
- and coalesce(
- min(case when enrolled_client_id is not null then date end) over(partition by enrolled_client_id),
- min(case when managed_client_id is not null then date end) over(partition by managed_client_id)) <= date
- then user_id end)
- from RUL
Add Comment
Please, Sign In to add comment