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
Leave a Comment