Approved Workman Database
Community Contributions => SQL Workbench => Topic started by: aggies8889 on January 22, 2010, 11:04:14 AM
-
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!
-
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 (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
-
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!
-
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.
-
Thanks bunches!! I will try the query Sherri. Looks like it will work.
-
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' "