I'm looking for some assistance/guidance on creating an sql function that will count actions that begin with a certain key. So, within a date range, if a constituent has 10+ of a specific action recorded, they get one score. If they have between 5 and 9, they get another score, etc.
"certain key" would be that the action description start with a 2-letter code. So in my case AC, AR, etc. So the Case/switch statement would count ACs and then output a number/score.
How would I add some conditions where the Note associated with the action is of a certain type?
Good question. The simple answer is we want to count the actions where there is a specific notepad type on the action and assign points that way. If I see how you do the join, I might be able to modify to fit our criteria. So the number of notepads on an action is not relevant, rather the type of note on a type of action is relevant.
I think this is close, but it is not pulling the right information. Is the join section correct? I've modified the statement and am trying to execute in SQL Developer to see the results. But I'm not getting them.
I'm providing a specific constituent ID and know that there is at least 1 action with the note type present. But the result isn't showing the result.
How does this look?
WHEN COUNT(ID) >= 10 THEN 5
WHEN COUNT(ID) >= 5 AND COUNT(ID)
WHEN COUNT(ID) >= 2 AND COUNT(ID)
WHEN COUNT (ID) = 1 THEN 1
LEFT OUTER JOIN ActionNotepad AS ANP ON Actions.ID = ANP.ParentID INNER JOIN TABLEENTRIES AS NP_Type
ON ANP.NoteTypeId = NP_Type.TABLEENTRIESID
WHERE NP_Type.LONGDESCRIPTION = 'Substantive Action'
AND (Actions.Type = 9625 OR Actions.Type = 9626 OR Actions.Type = 9627 OR Actions.Type = 9628 OR Actions.Type = 9629 OR Actions.Type = 9631 OR Actions.Type = 9632)
AND ACTIONS.DTE >= CAST('6/1/2010' AS DATETIME)
AND Actions.Records_ID = '0042926'