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_sucess_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
-- Any of the parameters in this comment group can be used
-- where lower (a.partnername) like '%docomo%' --'Vodafone I%' -- 'Jio IN'
-- and a.partnername not like 'Vodafone IT%'
where a.entitlement_id in ('663025f8f84aa735cd67c225',
'66305b56f84aa735cd67c78f',
'66309e3df84aa735cd67d072',
'66317673f84aa735cd67dff0',
'6630c859f84aa735cd67d736',
'663047ddf84aa735cd67c53e',
'663060d6f84aa735cd67c823',
'65a25a83f67b9b0498dcb1a2',
'66313ad7f84aa735cd67ddce',
'662fe721f67b9b5381fd4771',
'6630cf99f67b9b5381fd5f9c')
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