Hello,
We recently went from a local database and case management system to a provincial wide database and case management system. The downside is that I have lost all control of what I have access to and I am still in the learning process for the new software.
What I am trying to do is pull the earliest possible date of a specific type of Contact Log. A case can have many contact logs and each contact log will have it's own date. The code I am trying to use in a Query Calculation is below:
CASE
WHEN COUNT(CASE WHEN [CM Reporting].[Contact Log/Departure].[Contact Log Type] = 'Regular'
AND [CM Reporting].[Contact Log/Departure].[Contact Method] = 'Face to Face'
AND [CM Reporting].[Contact Log/Departure].[Contact Log Purpose] = 'Visit-Unannounced'
THEN 1
END) > 0
THEN (CASE
WHEN [CM Reporting].[Contact Log/Departure].[Contact Log Type] = 'Regular'
AND [CM Reporting].[Contact Log/Departure].[Contact Method] = 'Face to Face'
AND [CM Reporting].[Contact Log/Departure].[Contact Log Purpose] = 'Visit-Unannounced'
THEN 'YAY x 100' --here is supposed to be the code minimum([CM Reporting].[Contact Log/Departure].[Contact Log Start Date])
ELSE 'NOOOOOOOOOOOOOOOOOOO' --I end up here when the count above is greater than 0
END)
END
Any thoughts? Suggestions? It's driving me insane at this point.
Hello,
I've figured it out (as is usually the case when you "give up" but don't actually "give up". I used the following code and it worked for what I needed it to do.
minimum (CASE
WHEN [CM Reporting].[Contact Log/Departure].[Contact Log Type] = 'Regular'
AND [CM Reporting].[Contact Log/Departure].[Contact Method] = 'Face to Face'
AND [CM Reporting].[Contact Log/Departure].[Contact Log Purpose] IN ('Visit-Unannounced','Visit-Announced';'Case Activity')
THEN [CM Reporting].[Contact Log/Departure].[Contact Log Start Date]
END)