Untitled
unknown
plain_text
a month ago
1.9 kB
0
Indexable
Never
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
Leave a Comment