Approved Workman Database
Community Contributions => SQL Workbench => Topic started by: Commander Jon on October 06, 2011, 01:38:07 PM
-
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.
-
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.
-
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
-
Thanks! I'll give it a shot!