Community Contributions > SQL Workbench

Point Totals

(1/1)

Commander Jon:
I need some SQL for the following.  I need to print point totals for all clubbers in a single club, but I need the points totaled within a date range.  I also need to do a little math on the point total before it is printed.  AND ... I need to divide by 10, always rounding up.

dave4him:
A work around could be to just use the Achievement Report. Print to XSL file, open and customize there.

I would like to see an option to just print points and/or the sections, maybe a drop down list option.

HCCAwana:
You could try something like this. Adjust the dates as appropriate.

SELECT PS.Club_Name, P.Filing_Name,Ceiling(SUM(PS.Points)/10) AS MyPoints
FROM Person_Session PS, Administration Admin, Person P
WHERE PS.Club_Year=Admin.Current_Club_Year
AND PS.Person_Status='Active'
AND PS.Member_Type='Clubber'
AND PS.Session_Date BETWEEN '2011-09-01' AND '2011-10-01'
AND P.Person_ID=PS.Person_ID
GROUP BY PS.Club_Name, P.Filing_Name

Or if you also want section counts:

SELECT PS.Club_Name, P.Filing_Name, SUM(PS.Req_Section_Num) AS SectionCount, SUM(PS.Extra_Section_Num) AS ExtraSectionCount, SUM(PS.Points) AS TotalPoints, SUM(PS.Req_Section_Points) AS SectionPoints,Ceiling(SUM(PS.Points)/10) AS MyPoints
FROM Person_Session PS, Administration Admin, Person P
WHERE PS.Club_Year=Admin.Current_Club_Year
AND PS.Person_Status='Active'
AND PS.Member_Type='Clubber'
AND PS.Session_Date BETWEEN '2011-09-01' AND '2011-10-01'
AND P.Person_ID=PS.Person_ID
GROUP BY PS.Club_Name, P.Filing_Name

Commander Jon:
Thanks!  I'll give it a shot!

Navigation

[0] Message Index