I was able to find a function out hear that I was able to modify to get get most of the way to what I want, but I am not getting the results I expect.

What I want is to award a single point for each of a specific events that the constituent has attended.  However, I do not want the points awarded to exceed 4 points.

I am struggling to find documentation on syntax in this tool as in my previous life I have worked with three different tools that had slight nuances to each other.

Here is what I have:

SELECT

 CASE

  WHEN COUNT(RecordsID) >3 THEN 4

  WHEN COUNT(RecordsID) >0 THEN COUNT(RecordsID)

  ELSE 0

  END

FROM participants PR INNER JOIN special_event EV ON PR.EVENTID=EV.ID

WHERE EV.EVENTID IN ('2018-09-15-Arden Hills', '2019-04-06-Bloomington', '2019-04-04-19', '2019-03-21-Duluth', '2019-01-26--Duluth', '2018-10-13-Duluth', '2019-06-29 Duluth', '2019-03-09-Diuluth', '2018-11-09-Duluth', '2018-12-04HolidayDinner', '2018-11-02-Duluth', '2018-10-12-Duluth', '2019-05-02-duluth', '2018-11-15 Duluth', '2018-09-06-Duluth 00', '2019-02-07 Duluth', '2019-02-01 Fort Meyers', '2018-08-09 Green Bay', '2018-Oct Duluth-00', '2018-10-03 St Paul', '2018-11-10-Blloomington', '2018-05-03-Duluth', '2018-09-24-Miami', '2019-04-13-Minneapolis', '2019-06-21 to 06-23', '2019-03-26-Phoenix', '2019-06-27-PresDinner', '2019-06-28-Duluth 00', '2018-09-16-Duluth-01', '2019-03-23 San Diego', '2019-06-29-SASS', '2019-04-06-Seattle', '2019-03-02 St. Paul', '2019-04-25-St Paul', '2019-02-08 St, Paul', '2018-08-23-9-3 St. Paul', '2018-11-14-Duluth', '2018-05-31-Twin Cities', '2018-12-8-18', '2019-02-02-DC')

 AND PR.ATTENDED = 1

Can you tell me why everyone gets no points or max points?

Thank you.

Joel Clasemann

jclasema@css.edu

I see a few possible issues here. (I'm querying in SQL Server, not ScoreOmatic, but the answers should be the same.)

1) RE uses  0 and -1 for the boolean operator. So if you are querying on attended = yes, then you want to say "AND PR.ATTENDED = -1". Querying on PR.attended = 1 would get you no points across the board.

2) You seem to be grouping all participants together and counting all of their attendance. When I ran your query as-is (replacing only the event IDs with ones from my system, and adjusting 1 to -1), I got 4. Just 4, not 4 per record. I had to select the Records ID as well, and group by records ID, and then it started counting just fine. However, if this query is nested within a scoreomatic query, this may not be an issue.

3) Logically, I don't think you will end up with anyone with a score of 0, because your WHERE filters to ONLY those who have attended. Instead, if you change your count to sum(abs(PR.ATTENDED)) instead of count(RECORDSID), and remove the WHERE PR.ATTENDED = -1 clause, then you get the count you want (including 0s). You have to include the ABS function to get absolute value, of course, to turn that -1 into a 1

My final query below, and it seems to be counting just fine

SELECT

 PR.RecordsID,

 CASE

  WHEN sum(abs(pr.attended)) >3 THEN 4

  WHEN sum(abs(pr.attended)) >0 THEN sum(abs(pr.attended))

  ELSE 0

  END score

FROM participants PR INNER JOIN special_event EV ON PR.EVENTID=EV.ID

WHERE EV.EVENTID IN ('19EV01','19EV02','19EV03','19EV04','19EV05','19EV06','19EV07','19EV08','19EV09','19EV10','19EV11','19EV12','19EV13','19EV14','19EV15','19EV16','19EV17','19EV18','19EV19','19EV20','19EV21','19EV22','19EV23','19EV24','19EV25','19EV26','19EV27','19EV28')

group by pr.RecordsID

I dropped that into an SQL function inside Scoreomatic and every single person in my list of 164 constituents received a 4.  I know that 80% of them should have scored a 0.  When I set it up as a join for an SQL query inside Scoreomatic everyone gets a 0.

Things that make you go hmmmm.