Error: Query execution time analytics not created

[Roua]

Hello,
regarding access provisioning / deprovisioning  
i had an issue with the long excusion time that i meantioned here and here,
i tried many approaches, filtering endpoints and status for both entitlement and accounts as well as creating a dataset to match them instead of listing 100 manually in the query,
i also separated provisioning and deprovisioning
yet still i can't create an analytics it shows it is proccessing but it doesn't get created 
what would be a good approach regarding this matter of assigning the right entitlements? 

SELECT 
    u.userKey, 
    u.employeeclass, 
    e.endpointkey,
    ev.entitlement_valuekey AS entvaluekey, 
    a.accountkey AS acctKey, 
    a.name AS accName, 
    ev.entitlement_value AS entitlementValue, 
    'Provision Access' AS Default_Action_For_Analytics 
FROM 
    users u 
JOIN 
    user_accounts ua ON u.userkey = ua.userkey 
JOIN 
    accounts a ON ua.accountkey = a.accountkey 
LEFT JOIN 
    dataset_values ds ON ds.ATTRIBUTE1 = u.employeeclass 
JOIN 
    entitlement_values ev ON ( 
        (ds.ATTRIBUTE3 IS NOT NULL AND ev.entitlement_value = ds.ATTRIBUTE3) 
        OR (ds.ATTRIBUTE3 IS NULL AND ev.customProperty8 = u.CUSTOMPROPERTY4 AND ev.customProperty9 = u.DEPARTMENTNUMBER)
    )
JOIN 
    entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey 
JOIN 
    endpoints e ON a.endpointkey = e.endpointkey  
WHERE 
    e.endpointkey = 6  
    AND a.endpointkey = 6  
    AND ev.status = 1  
    AND a.status = '1';

[rushikeshvartak · Accepted solution]

SELECT 
    u.userKey, 
    u.employeeclass, 
    e.endpointkey,
    ev.entitlement_valuekey AS entvaluekey, 
    a.accountkey AS acctKey, 
    a.name AS accName, 
    ev.entitlement_value AS entitlementValue, 
    'Provision Access' AS Default_Action_For_Analytics 
FROM 
    users u 
JOIN 
    user_accounts ua ON u.userkey = ua.userkey 
JOIN 
    accounts a ON ua.accountkey = a.accountkey 
LEFT JOIN 
    dataset_values ds ON ds.ATTRIBUTE1 = u.employeeclass 
JOIN 
    entitlement_values ev ON ev.entitlement_value = ds.ATTRIBUTE3 
JOIN 
    entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey 
JOIN 
    endpoints e ON a.endpointkey = e.endpointkey  
WHERE 
    e.endpointkey = 6  
    AND a.endpointkey = 6  
    AND ev.status = 1  
    AND a.status = '1'
    AND ds.ATTRIBUTE3 IS NOT NULL

UNION ALL

SELECT 
    u.userKey, 
    u.employeeclass, 
    e.endpointkey,
    ev.entitlement_valuekey AS entvaluekey, 
    a.accountkey AS acctKey, 
    a.name AS accName, 
    ev.entitlement_value AS entitlementValue, 
    'Provision Access' AS Default_Action_For_Analytics 
FROM 
    users u 
JOIN 
    user_accounts ua ON u.userkey = ua.userkey 
JOIN 
    accounts a ON ua.accountkey = a.accountkey 
LEFT JOIN 
    dataset_values ds ON ds.ATTRIBUTE1 = u.employeeclass 
JOIN 
    entitlement_values ev ON ev.customProperty8 = u.CUSTOMPROPERTY4 
    AND ev.customProperty9 = u.DEPARTMENTNUMBER
JOIN 
    entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey 
JOIN 
    endpoints e ON a.endpointkey = e.endpointkey  
WHERE 
    e.endpointkey = 6  
    AND a.endpointkey = 6  
    AND ev.status = 1  
    AND a.status = '1'
    AND ds.ATTRIBUTE3 IS NULL;

[Roua]

@rushikeshvartak 
Thank you a lot! 😁😇