Question about getting the currently set Club Date in a query.

[Stephen]

AW Version: 4.3.3.4

Hello Folks,

I have a question about the query I posted below. I am using Administration.Current_Club_Date and it pulls data from the last club night of the 2010/2011 club year. When I change the Club Date to some other club night of 2010/2011 the query still pulls the data from the last club night of the club year.

Does anyone know if this is a bug or if there is some other field I can query so that this query will pull data based on whatever date I have set in the Club Date drop down menu?

The query is for calculating bucks earned by clubbers based on our clubs criteria.
These criteria are...
1 buck for being on time.
1 buck for each section passed.
1 buck per visitor brought.
1 buck if clubber has their Uniform, Bible and Handbook.

The query is...

SELECT P.Person_ID AS "Clubber ID",
A.Current_Club_Date AS "Club Date",
PS.Club_Name AS "Club",
P.Mailing_Name AS "Name",
Custom_Chkbox1 AS  "On Time",
PS.Sections AS "Sections",
PS.Visitors_Num AS "Visitors",
CASE WHEN (PS.Uniform = 1 AND PS.Bible = 1 AND PS.Handbook = 1) THEN 1 ELSE 0 END AS "Unif_Bible_Book",

SUM
(CASE
WHEN (PS.Uniform = 1 AND PS.Bible = 1 AND PS.Handbook = 1)
THEN Custom_Chkbox1 +PS.Sections + PS.Visitors_Num + 1
ELSE Custom_Chkbox1 +PS.Sections + PS.Visitors_Num
END)
AS "Total Bucks"

FROM Person P, Person_Session PS, Administration A
WHERE P.Person_ID = PS.Person_ID and P.Member_Type = 'Clubber' and (Status = 'Active' or Status = 'Visitor') and PS.Session_Date = A.Current_Club_Date and (PS.Club_Name = 'T&T Girls' or PS.Club_Name = 'T&T Boys')
GROUP BY P.Person_ID
ORDER BY PS.Club_Name, P.Mailing_Name

[Rick Leffler]

Hello Stephen and welcome to the forum.  It's great to have you involved in the Approved Workman Database project and sharing your advanced SQL knowledge.  I often learn new things when highly skilled technical folks like you get involved. As a result, we all benefit from the new ideas and solutions we work together on! 

After seeing your posting I did a little testing and confirmed that you have indeed found a bug.  Sorry. The Administration.Current_Club_Date field value is not getting updated when the user changes the Current Club Date value from the main toolbar's drop down list.   I have logged this defect and it will be fixed in the next release going out this summer before the new club year starts.  By the way, the current version is 4.3.3.6 so I encourage you to log into AWdb and click Help > Check for updates.   If you happen to be using a trial version presently, please send an email to support@approvedworkman.com and I'll send you the 4.3.3.6 update.

Alternative / Workaround:

For now... if you want to use the SQL statement you've shared below, try just referencing a specific date and ignore Administration.Current_Club_Date.  For example, try the following changes:

in line 2 change: 
           A.Current_Club_Date AS "Club Date",
  to
           PS.Session_Date AS "Club_Date",

and in the Where clause change:   
           and PS.Session_Date = A.Current_Club_Date
  to:   
           and PS.Session_Date = '2010-10-6'
 
(Note: I used an example of Oct 6, 2010 as the date and of course you'll need to modify this to get the correct club date according to your needs. When specifying dates in AWdb SQL statements, just know that hard-coded dates have to be in single quotes using a format of YYYY-MM-DD.)

Future consideration:

I know that AWdb uses the term "points" where it appears your club prefers "bucks'. However, if you make the mental substitution and just swap points for bucks... you won't even have to run any SQL because the system will keep track of all of the "bucks/points" for you, it will do all the math and you can see the bucks/point values  on screen and on various reports and even use the Tools > Club Store screen to spend the bucks/points.

If you go to Admin > Club Setup > Club Names, Logos, Dues and Points  you will see that you can enter the exact amount of points that clubbers receive for each of the usual "Check-in" categories (Attendance, Uniform, Bible, Handbook, Bonus, Visitors, etc.   (note the Custom1 or Custom2 field can be renamed "OnTime" if you want to track that))

Points for completing handbook sections are set up under Admin > Curriculum. Once there click the "Set Point Values" button in the Curriculum tool bar.

After having changed any point value settings under Admin, to make sure that all of the points/bucks are recalculated to reflect the new values go to the main Tools menu and select "Recalculate Total Points".

To view a clubber's net Points/Bucks, you can look at the main Clubbers List grid and examine the Net Points column. Remember you'll need to turn Calculations "On" in the toolbar. 

On the Check-in List grid, the "Points" aka "Bucks" column shows the points earned for the current club week, however you can make visible another column that will show a clubber's "Net" points too.  (Use the tiny button in the upper left corner of the grid next to the Last Name header to get a drop down check list of all available columns.)

Try running the Achievement Report (under the Reports tab) and experiment with the various options and you can probaly get a report output to meet your needs.  The Point List and Worksheet may be helpful too if you're preparing for Store Night.

If none of the existing reports can give you exactly what you're looking for, please do share more about your needs and we can likley add a new report in the future so you won't have to resort to using the SQL Workbench. (Though it IS pretty handy to be able to do stuff like this, especially if you use the results grid's Filtering, Sorting, Grouping, Printing and Exporting features.) 

[millersrus96]

hey rick, we used to also give 1 buck for Uniform / bible / book. I think this is a common practice
Went with your 1 per method & changed prices in store to facilitate.
Its been OK. Kids adapted quickly.
Would kind of like the ability to link them, that is, if you only do 2 items, you don't get any points, something like that. or each is worth .33, but  you can only spend whole #'s

[Stephen]

Thank you for the quick response and the tips. At the very least it looks like the query will work as I hoped it would by the start of the next club year.

I'll look into some of the options you mentioned and see what works best for us.

Thanks!

[Stephen]

Hi Rick, it doesn't look like Administration.Current_Club_Date is working yet. Is that something that you still think might get done before end of September?

[Rick Leffler]

According to our defect tracking log, this bug is fixed in whatever version is released after 5.0.0.3. (probably 5.0.0.4). Thanks for keeping watch!

[Stephen]

Hi Rick, since the Summer update it looks like Administration.Current_Club_Date has been removed, is there an alternative so my queries can get the current club date without me having to update our scripts manually each week?

[marvin]

Stephen,
This should do it for you...

Code: [Select]

SELECT
  P.Person_ID AS "Clubber ID",
  PS.Session_Date AS "Club Date",
  PS.Club_Name AS "Club",
  P.Mailing_Name AS "Name",
  Custom_Chkbox1 AS  "On Time",
  PS.Sections AS "Sections",
  PS.Visitors_Num AS "Visitors",
  CASE WHEN (PS.Uniform = 1 AND PS.Bible = 1 AND PS.Handbook = 1) THEN 1 ELSE 0 END AS "Unif_Bible_Book",
  SUM
    (CASE
      WHEN (PS.Uniform = 1 AND PS.Bible = 1 AND PS.Handbook = 1) THEN Custom_Chkbox1 +PS.Sections + PS.Visitors_Num + 1
      ELSE Custom_Chkbox1 +PS.Sections + PS.Visitors_Num
    END) AS "Total Bucks"
FROM
  Person P,
  Person_Session PS
WHERE
  P.Person_ID = PS.Person_ID
  AND P.Member_Type = 'Clubber'
  AND P.Status IN ('Active', 'Visitor')
  AND PS.Club_Name IN ('T&T Girls', 'T&T Boys')
  AND PS.Session_Date IN
    (SELECT session_date
      FROM Club_Session
      WHERE (session_date - current_date()) IN
        (SELECT MAX(session_date - current_date())
          FROM Club_Session
          WHERE session_date - current_date() <= 0
        )
    )
GROUP BY
  P.Person_ID
ORDER BY
  PS.Club_Name,
  P.Mailing_Name

I changed your status and club_name predicates (WHERE clause criteria) to use IN instead of OR to make it a little easier to read. 

I changed the Administration current_club_date reference, like Rick mentioned, to the PS.Session_Date and was able to remove the Administration table altogether.  We then make sure that that session_date is the most recent one (up to and including today).

For anyone else that wants to test this in their database (I used the demo db) just comment out his club_name criteria.

Code: [Select]

--  AND PS.Club_Name IN ('T&T Girls', 'T&T Boys')


[Stephen]

Thanks Marvin!

Using the "current_date()" function solves the issue.
How did you know that function was available?
I believe if we have access to the main DB server we can use Sql Server Management Studio to get all available functions, tables, etc..., do you know if we can do this?

[marvin]

Sorry for the late reply, I didn't get an email about this post like I thought I would.  Anyway, I had to figure out what DBMS was being used to find out what dialect of SQL it would understand.  You can find the SQL help here:
http://www.elevatesoft.com/manual?action=topics&id=dbisam4&product=delphi&version=5&section=sql_reference

As far as SQL Mgmt Studio-- that won't work because it's not a Microsoft SQL database.