Beastformula

NIKKI

I have this data with an ID column that are repeated because each unique id have 4 different event type, What I am trying to achieve is if one id with = 4 different category and are all completed in the event status column and within 45days of event date and originalenrollment date then it is completed.

I have this code below but seems not be capturing the statement above,

CASE
WHEN COUNT(DISTINCT ChildPlusID) =Event Type IN ('Hearing', 'Vision', 'ASQ3') AND Event Status Code = 'Completed' AND DATEDIFF(Event Date, OriginalEnrollmentDate) <= 45 THEN 'Completed' END

Please help,

Thank you.

GrantSmith

CASE WHEN COUNT(CASE WHEN `Event Type` IN ('Hearing', 'Vision', 'ASQ3') THEN `ChildPlusID` END) = COUNT( 
  CASE WHEN `Event Type` IN ('Hearing', 'Vision', 'ASQ3') AND 
  `Event Status Code` = 'Completed' AND
  DATEDIFF(`Event Date`, `OriginalEnrollmentDate`) <= 45 THEN `ChildPlusID` END) THEN 'Completed' END

This is assuming there are 4 records in your dataset with each of the event types. It will count the number of events found in that list and then compare it to the count of the number int the list which are also within 45 days.

GrantSmith

CASE WHEN COUNT(CASE WHEN `Event Type` IN ('Hearing', 'Vision', 'ASQ3') THEN `ChildPlusID` END) = COUNT( 
  CASE WHEN `Event Type` IN ('Hearing', 'Vision', 'ASQ3') AND 
  `Event Status Code` = 'Completed' AND
  DATEDIFF(`Event Date`, `OriginalEnrollmentDate`) <= 45 THEN `ChildPlusID` END) THEN 'Completed' END

This is assuming there are 4 records in your dataset with each of the event types. It will count the number of events found in that list and then compare it to the count of the number int the list which are also within 45 days.

NIKKI

Thank you GrantSmith.

1. CASE WHEN COUNT(CASE WHEN `Event Type` IN ('Hearing', 'Vision', 'ASQ3') THEN `ChildPlusID` END) = COUNT(
2. CASE WHEN `Event Type` IN ('Hearing', 'Vision', 'ASQ3')

This part is giving syntax error

GrantSmith

Can you post a screenshot of your code and the error your getting?