Approved Workman Database

Community Contributions - Legacy => SQL Workbench => Topic started by: Commander Jon on October 06, 2011, 01:38:07 PM

Title: Point Totals
Post 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.
Title: Re: Point Totals
Post by: dave4him on October 07, 2011, 05:33:30 PM
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.
Title: Re: Point Totals
Post by: HCCAwana on October 13, 2011, 03:17:57 PM
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
Title: Re: Point Totals
Post by: Commander Jon on October 18, 2011, 09:04:32 PM
Thanks!  I'll give it a shot!