Author Topic: Zeroing Point Totals at a Specific Date  (Read 1059 times)


  • Newbie
  • *
  • Posts: 1
Zeroing Point Totals at a Specific Date
« on: January 30, 2011, 09:00:45 AM »
Our club would like to help our kids visulalize the church in action.  One strategy we have for this is using club point totals, not individual totals, to determine if the kids have earned an end of year party (bounce houses, pizza, etc).  Is there an easy way I can delete all points earned in the 2010 calendar year, leaving only those points earned in 2011?  I should have taken care of this over the holidays, but...

Chris Xynides

Rick Leffler

  • Administrator
  • Sr. Member
  • *****
  • Posts: 339
    • Approved Workman
Re: Zeroing Point Totals at a Specific Date
« Reply #1 on: February 01, 2011, 11:54:37 AM »
The easiest way to accomplish this is to use the Misc procedure named "Reset Net Points to Zero for All Members". This is located under Admin > Database Utilities > Misc Procedures.

However, here is the catch...  since you ONLY want to zero out points earned PRIOR to 1/1/2011, you would have needed to run this during the first club date in January.   If you run it now (Feb 1, 2011) it will zero ALL points, even those earned in Jan 2011 that you would want to keep.

Though rare, this is not the first time this request has surfaced, so I should probably enhance the aforementioned Misc procedure to allow users to select a particular date so that all points earned prior to the selected date can be zeroed (spent), thus leaving any points earned subsequent to the selected date.

For now though, here is a workaround:

1. Open the SQL Workbench under Admin > Database Utilities, then copy/paste the following text:

select P.Person_ID, P.Filing_Name sum (PS.Points) as Total_Pts_In_2010 from Person P, Person_Session PS
where PS.Club_Year = '2010-2011' and PS.Session_Date < '2011-1-1' and P.Person_ID = PS.Person_ID and P.Member_Type = 'Clubber'
group by P.Person_ID order by P.Filing_Name

2. Click the Run SQL button and you will see the results appear in the grid. This will be a list of the total points earned in the 2010-2011 club year PRIOR to 1/1/2011.  Thus, this total point value shown would be the number of points you would want to spend in the Club Store screen.

3. Open the Club Store screen by clicking Tools > Club Store. If you have a wide monitor you can resize the SQL Workbench window and position it side-by-side with the Club Store screen so you can refer to it as you work in the Club Store.  You can also use the Preview/Print feature of the grid if you'd rather work with a paper copy.  Note: On the Club Store screen, you will probably want to set your CLUB DATE WHEN POINTS SPENT value to the first Club Date in January.  Keep in mind that the Club Store screen will often show MORE points than those point totals in the SQL Workbench result set because the Club Store screen is also including points earned in January and February.
Rick at Leffler Systems
Murfreesboro, Tennessee