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.  

Thank you.

Hi Jeffery,
What does the "certain key" relate to on the action? I will give you an answer once I know that.
Regards,
Steve

The action type in the following is just an example, if you want to base it something else just let me know.

SELECT CASE
WHEN COUNT(ID) > 9 THEN 5
WHEN COUNT(ID) > 4 AND COUNT(ID) ELSE 0 END
FROM ACTIONS
LEFT OUTER JOIN TABLEENTRIES AS ACTIONTYPES ON ACTIONS.TYPE = ACTIONTYPES.TABLEENTRIESID
WHERE ACTIONS.DTE >= '2014-01-01' AND ACTIONS.DTE AND ACTIONTYPES.Longdescription = 'Birthday'
AND ACTIONS.RECORDS_ID = @RecordsID

"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.

There is no "Description" field on an Action. if it is in fact the type, just change the sql as follows

SELECT CASE
WHEN COUNT(ID) > 9 THEN 5
WHEN COUNT(ID) > 4 AND COUNT(ID) ELSE 0 END
FROM ACTIONS
LEFT OUTER JOIN TABLEENTRIES AS ACTIONTYPES ON ACTIONS.TYPE = ACTIONTYPES.TABLEENTRIESID
WHERE ACTIONS.DTE >= '2014-01-01' AND ACTIONS.DTE AND ACTIONTYPES.Longdescription LIKE 'AC%'
AND ACTIONS.RECORDS_ID = @RecordsID

This is great.

How would I add some conditions where the Note associated with the action is of a certain type?

Hi Jeffrey,
Do you want points for each action that has any notepad of that type or points if any action has that type?
For example, if a record has 3 actions, 1 has that notepad type once, and 1 has that type notepad twice, how would you want points awarded?
Steve

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.

--5 would be your points,
--TOP(1) limits it to one row returned regardless of how many times the notepad
--type is on an action or how many actions have a notepad of that type
SELECT TOP(1) 5 FROM ACTIONS AS A
LEFT OUTER JOI

Thank you.  

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.

 

Thoughts?

It seems to be working correctly for me. What exactly is not working?

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.

We need to clarify what ID we are talking about. The statement I provided is written specifically for use in ScoreOmatic, the @RecordsID must be used and it gets replaced
by the system record id of the record being processed when running the profile. The "Constituent ID" as used in RE is the ID that appears on the record and is user defined
or auto-generated but can be edited, and that value is not valid anywhere in the SQL because it is not a foreign key to any other table.
Can you post the SQL that you are using or send it to me directly at steves@omaticsoftware.com?

How does this look?

 

Select CASE

  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

ELSE 0

END

FROM Actions 

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'