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
-- left join (select max(deferral_product_subtype) from mbd_presentation.fuse_fbts_open_subs_activation)c
-- on a.fuse_sub_id = c.fuse_subscription_id
-- where a.fuse_sub_id = ('60a9a2b4-e08d-4035-ac9c-06c5e2a7a87c')  -- '17fabd2c-6bf6-30b6-8555-1ff83d01a757'
-- where lower (a.partnername) like '%tarhub%' --'%ingte%' -- '%rue%' --%docomo%' -- --%ata%' --'MTN%' --Best%' -- 'MTN%' --'Airtel NG' -- 'Vodafone T%' -- '''Visa%' --'Optus%' --'Vodafone I%' -- = 'Jio IN'
where a.partnername like 'Tata%' -- not like 'Vodafone IT%'
and a.entitlement_id not like 'Fender%'
and a.entitlement_id not like 'FDSETest%'
-- where a.fuse_sub_id in ('943b991d-e99c-3fbb-afe9-380714cf96bc')
-- where b.plan_id in
-- and b.service_reason = 'ACTIVATED'
and b.service_reason NOT IN ('TERMINATED','CANCELLED')
-- 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