Start a new topic

SQL Function to Count Events

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.

Any tips/hints?


Something like this...

SELECT CASE
WHEN COUNT(ID) > 9 THEN 5
WHEN COUNT(ID) > 4 AND COUNT(ID) ELSE 0 END
FROM participants WHERE RecordsID = @RecordID

Bam!  That's exactly what I ended up doing.

 

Thank you.

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 yes, I know there's an Events Summary query feature in RE7.95 that can do this much easier - I am still running on 7.93 and don't have that option. I was hoping to use this SQL function to tide me over until we can upgrade...)

Declare @RecordsID int;
Select
Case
when COUNT(RecordsID) >6 THEN 5
when COUNT(RecordsID) >2 AND COUNT(RecordsID)
when COUNT(RecordsID) >0 AND COUNT(RecordsID)
else 0 END
FROM participants PR INNER JOIN special_event EV ON PR.EVENTID=EV.ID
WHERE EV.GROUPID=14622
and PR.ATTENDED=-1

AND PR.RecordsID = @RecordsID

Login to post a comment
JS Bin