I need to calculate a field for a Data Extension based on multiple records in another Data Extension.

JourneyEntrance (Data Extension to be updated)

SubscriberKey, PolicyNumber, Complete

Cases (Data Extension to reference)

SubscriberKey, PolicyNumber, StatusCode

I need to updated JourneyEntrance.Complete field to True if All records in Cases have a StatusCode of E or N. If any Cases record has a value other than E or N then the JourneyEntrance.Complete will be False. The records are related based on PolicyNumber.


I switched it up a little. I think this should work. Haven't tested yet.

SELECT  j.SubscriberKey,
        (SELECT COUNT(c.SubscriberKey) FROM Cases c WHERE c.PolicyNumber = 
j.PolicyNumber AND c.StatusCode IN('E','N')) AS CountNotComplete
FROM JourneyEntrance j 

The thought is once the count reaches 0 then I will know all Cases are Complete.


Please try this query:

SELECT JE.SubscriberKey,JE.PolicyNumber,C.StatusCode,     
    WHEN (C.StatusCode = 'E' OR C.StatusCode ='N') THEN 'TRUE'  
    ELSE 'FALSE'  
END AS Complete  
FROM [JourneyEntrance] JE  
INNER JOIN [Cases] C  
ON JE.PolicyNumber = C.PolicyNumber
  • This returns multiple rows -- one for each Case -- which is not what the OP wanted. – Adam Spriggs Jul 6 '17 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.