Beastformula

NIKKI
NIKKI Member
in Charting

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.

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 
    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 
    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • NIKKI
    NIKKI Member

    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
    GrantSmith Coach

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories