Untitled

select
a.entitlement_id
,a.fuse_sub_id
,b.plan_id
,a.partnername
,case
    when a.fuse_termination_sucess_timestamp is not null then 'TERMINATED'
    when b.service_reason is not null then b.service_reason
    when a.fuse_activate_success_timestamp is null and a.fuse_activation_creation_timestamp is not null then 'PENDING'
    when b.service_reason is null and a.fuse_activation_creation_timestamp is null then 'NOT FOUND'
    end as service_reason
-- ,b.start_date
-- ,max(a.fuse_activation_creation_timestamp ) fuse_activation_creation_timestamp
,max(a.fuse_activate_success_timestamp ) fuse_activate_success_timestamp
-- ,max(a.fuse_termination_sucess_timestamp) fuse_termination_success_timestamp
from mbd_reporting.d_fuse_customers a
left join (select *, row_number() over (partition by subscription_id order by start_date desc) as rn_ from fender.service_reason
) b
on a.fuse_sub_id = b.subscription_id
where a.partnername like 'Vodafone I%' -- = 'Jio IN'
and a.partnername not like 'Vodafone IT%'
-- a.entitlement_id = '5e5f9fab-bf97-40ba-b0e5-6e767649b46b'
-- b.plan_id = '4d33d862-4491-45dc-95e4-1bb4c946a529'  --- add in other Vodafone PT plans
-- and b.service_reason = 'ACTIVATED'
and b.service_reason NOT IN ('TERMINATED','SUSPEND','CANCELLED')
and b.rn_ = 1
group by
a.entitlement_id
,a.fuse_sub_id
,b.plan_id
,a.partnername
,case
  when a.fuse_termination_sucess_timestamp is not null then 'TERMINATED'
    when b.service_reason is not null then b.service_reason
    when a.fuse_activate_success_timestamp is null and a.fuse_activation_creation_timestamp is not null then 'PENDING'
    when b.service_reason is null and a.fuse_activation_creation_timestamp is null then 'NOT FOUND'
    end
,b.start_date