Exclude entitlements with no accounts in entitlement owner campaign

[SudheerKaneti]

Hi Team,

We are trying to exclude entitlements which are not having accounts in them in entitlement owner campaign.

We have tried below queries in Account entitlement 1 Query in Advanced query, but none of them worked.

1. ae1.entitlement_valuekey is not null 

2. ae1.entitlement_valuekey>0

3. ae1.entitlement_valuekey is not null and ae1.accountkey not in (select accountkey from accounts)

May anyone please help us with the above.

Thank you,

Sudheer Kaneti.

[dgandhi]

Add below condition in Advance Config and try.

[rushikeshvartak]

try below query and change filters as per your need

select * from(

select ev.entitlement_valuekey ,ev.entitlement_value,
(select count(* ) from account_entitlements1 ae1 ,accounts a where ae1.entitlement_valuekey=ev.entitlement_valuekey and 
a.accountkey=ae1.accountkey and a.status in (1,'Manually Provisioned') and a.accounttype ='A'

)noofaccountsassignedwithent
from entitlement_values ev where 
ev.customproperty30 Like 'True' and ev.status=1
and entitlementtypekey=121 )data where noofaccountsassignedwithent=0