I'm curious if anyone has successfully created a SQL Function that counts and scores a constituent that attended a certain number of events within a date range.
By some stroke of luck, I think I was able to create the SQL function, but can't seem to figure out how to score the constituent based on the count returned. If >= 10, then score 5, if between 5 and 9, score 3, etc.
Bam! That's exactly what I ended up doing.
EDITED to say - found my mistake! The last line should have read "AND PR.RecordsID = @RecordID" - that pesky extra "s" threw everything off! (the fact that I needed to declare the variable should have been my clue...) Leaving the rest of this comment unchanged so others can learn from my mistake :)
Using your example, I built the following SQL function. When I run it in SQL Server Management Studio 2012 (leaving off the Declare statement, and substituting a specific RecordsID value for @RecordsID in the Where clause), I get a score of 5 for my test record. When I run the function below on the same test record, it runs fine, but gives me a score of 0. What am I doing wrong?
AND PR.RecordsID = @RecordsID