0

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.

Improve this question

2 Answers 2

Reset to default
1

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

SELECT  j.SubscriberKey,
        j.PolicyNumber,
        (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.

Improve this answer
-1

Please try this query:

SELECT JE.SubscriberKey,JE.PolicyNumber,C.StatusCode,     
CASE   
    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
Improve this answer
1
  • This returns multiple rows -- one for each Case -- which is not what the OP wanted.
    – Adam Spriggs
    Commented Jul 6, 2017 at 22:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .