Author Topic: Point Totals  (Read 5444 times)

Commander Jon

  • Jr. Member
  • **
  • Posts: 16
    • SVBC Awana Clubs
Point Totals
« 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.
Jonathan Coulter
Commander
Shenandoah Valley Baptist Church
Stephens City VA

dave4him

  • Global Moderator
  • Full Member
  • *****
  • Posts: 48
  • I love this job!
    • First Baptist Church Awana Clubs
Re: Point Totals
« Reply #1 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.
David Barnes
Awana Commander
First Baptist Church
Branford, FL
N.Fl. Awana Ministry Team

HCCAwana

  • Newbie
  • *
  • Posts: 3
Re: Point Totals
« Reply #2 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
David Post
Highlands Community Church
Awana DB Admin

Commander Jon

  • Jr. Member
  • **
  • Posts: 16
    • SVBC Awana Clubs
Re: Point Totals
« Reply #3 on: October 18, 2011, 09:04:32 PM »
Thanks!  I'll give it a shot!
Jonathan Coulter
Commander
Shenandoah Valley Baptist Church
Stephens City VA