Untitled
unknown
plain_text
5 months ago
2.3 kB
5
Indexable
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
Editor is loading...
Leave a Comment