Author Topic: List clubbers with completed sections one of last 2 times attended club?  (Read 2714 times)

aggies8889

  • Guest
I need to print a list of all clubbers who have completed any section(s) within the last 2 times that clubber ATTENDED club.
I have been compiling a list by hand using 2 lists from the db (attendance dates @ clubber & completed sections dates @ clubber).
Can anyone help me with this?
Thanks!

dave4him

  • Global Moderator
  • Full Member
  • *****
  • Posts: 64
  • I love this job!
    • First Baptist Church Awana Clubs
Re: List clubbers with completed sections one of last 2 times attended club?
« Reply #1 on: January 22, 2010, 08:16:00 PM »
For a report close to what you want, try the SQL code that Rick supplied in another form. Here's the link: http://approvedworkman.com/forum/index.php?topic=49.msg93#msg93

Copy and paste the code into the top section of the SQL page. (Adm>Database Utilities>SQL Workbench)
After pasting the code, change the date range in the code and RUN SQL. Once generated you can print from the Preview window or you could export to Excel for further sorting if needed.

Maybe someone who knows SQL could write a precise code for your query.  ;D
« Last Edit: April 17, 2019, 09:38:10 AM by Annette »
David Barnes
Awana Commander
First Baptist Church
Branford, FL
N.Fl. Awana Ministry Team

Sherri

  • Global Moderator
  • Full Member
  • *****
  • Posts: 42
Re: List clubbers with completed sections one of last 2 times attended club?
« Reply #2 on: January 24, 2010, 11:17:02 PM »
I couldn't easily think of a query that narrows the list down to exactly what you want.  I think it would require pl/sql or some logic within the application.  But by running the following query, you would at least have all the info you needed in one report.

The results show all of the session dates for which a clubber attended & the total sections that were said on that date.  You'd just have to find the last two entries for each clubber & check that at least one of them has sections done.  Within the query, I narrowed the list down to 'Active' & 'Visitor' clubbers & to those not in the 'Awana' or 'Nursery' club.  Make sure you adjust those values (or remove them if you want all clubbers) to meet your needs.

SELECT P.Person_ID,PS.Club_Name,PS.Team_Color, P.Mailing_Name AS "Name", PS.Session_Date, PS.Sections
FROM Person P, Person_Session PS
WHERE P.Person_ID = PS.Person_ID and
    P.Member_Type = 'Clubber' and
    (Status = 'Active' or Status = 'Visitor') and
    PS.Club_Year = '2009-2010' and
    (PS.Club_Name <> 'Awana' and PS.Club_Name <> 'Nursery') and
    PS.Attend = 1
ORDER BY P.Mailing_Name


Hope this helps!
Sherri Meadows
Monument, CO

dave4him

  • Global Moderator
  • Full Member
  • *****
  • Posts: 64
  • I love this job!
    • First Baptist Church Awana Clubs
Re: List clubbers with completed sections one of last 2 times attended club?
« Reply #3 on: January 25, 2010, 12:46:07 AM »
Great job Sherri..!

I used your code and then filtered and sorted with Excel. I was able to come up with a easy to read list with the needed results. The jpegs have instructions on them. Thanks.

Hope this works for you Aggies8899


I need to work on my graphic sizing  ::)  If you click the name instead of the jpeg, it will fully open in your own application.
« Last Edit: April 17, 2019, 09:39:50 AM by Annette »
David Barnes
Awana Commander
First Baptist Church
Branford, FL
N.Fl. Awana Ministry Team

aggies8889

  • Guest
Re: List clubbers with completed sections one of last 2 times attended club?
« Reply #4 on: February 11, 2010, 02:54:41 PM »
Thanks bunches!!  I will try the query Sherri.  Looks like it will work.

Rick Leffler

  • Administrator
  • Sr. Member
  • *****
  • Posts: 327
    • Approved Workman
Re: List clubbers with completed sections one of last 2 times attended club?
« Reply #5 on: February 14, 2010, 06:15:22 PM »
I love these kinds of topic discussions!  :)     Thanks Sherri and Dave for helping out.

Just a reminder... if you do run an SQL statement, the results grid of the SQL Workbench DOES allow filtering, sorting, grouping, printing, thus you may not need to export to Excel for such features. You can also save and recall SQL Statements if you need to.

You may want to add to Sherri's SQL the following...

(to get only those session records where at least one section was completed) 

                " and PS.Sections > 0 "


(if you want to narrow the result set down to only those session records for a particular two-week timeframe like  2010-02-03  through 2010-02-10)

                " and PS.Session_Date between 'yyyy-mm-dd' and 'yyyy-mm-dd'  "     
Rick at Leffler Systems
Murfreesboro, Tennessee