Community Contributions > SQL Workbench

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

<< < (2/2)

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
  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

--- End code ---

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: -----  AND PS.Club_Name IN ('T&T Girls', 'T&T Boys')

--- End code ---

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.

Navigation

[0] Message Index

[*] Previous page