Approved Workman Database
Community Contributions => SQL Workbench => Topic started by: Rick Leffler on April 19, 2008, 03:26:49 PM
-
The following will list the clubber's Filing_Name and Sections_Done for all sections that were marked as completed between {starting date} and {ending date}.
Note you will need to modify the actual dates within the braces and keep the same YYYY-MM-DD format and single quotes. Copy only the text between the dashed lines. The paragraph breaks are not important and are only used for readability.
Comment: This takes several seconds to run and it is possible the SQL Workbench window will disappear momentarily and once the processing is complete the workbench window ends up in the background behind the main form's window. If this happens, just click the "# files open" button in the main toolbar and select the SQL Workbench to bring it back on top.
Works with version 2.7.3.1
----------------------------------
select
P.Filing_Name, Count ('aCount') as Sections_Done
from
Person P, Book B, Section S
where S.Person_ID = P.Person_ID and B.Book_ID = S.Book_ID and P.Person_ID = B.Person_ID and S.Completed_Date between '2006-09-01' and '2007-03-08' and S.Completed = 'Yes'
Group by P.Filing_Name
------------------------------
-
Very nice little query. To take it a step further, you can tack on a
Order By Sections_Done
to put the list in ascending order or
Order By Sections_Done DESC
to put the list in descending order
-
I know this is an old forum topic, but I wanted to revisit and learn more of this tool called SQL.
The results obtained using the formula given by Rick can also be obtained in the Reports > Achievements Report. Is there an advantage to running the SQL, or just an alternative to some of us (not me, yet ;)), that know it?
I suppose if one knew this SQL 'language' well, you could break the data down even in more details, i.e., clubs, specific dates and other ranges! What if we had a place to submit club specific report requests and a "translator" :) supplied the SQL code to run it. Whenever I needed a particular report, I could look through the list and find what I needed or close to it. How cool would that be? 8)
Is learning SQL more difficult than learning to write HTML codes? I can edit HTML but writing from scratch is not attainable yet!
Maybe I need to find a "SQL for Dummies" book? ;D
-
This topic is very interesting to me as well. I even bought a book "Teach yourself SQL" because having come from using ACCESS, I was used to creating my own reports and miss that capability. I haven't even opened the book, though, so I don't know if it's as easy as HTML.
I think Rick has created this section of the forum so that we can share reports that people create. Your idea would take that one step further, but in the past this isn't an area Rick was ready to get into. The concern is people who really don't know what they are doing trying to create and run reports and then the issue of support for them which just isn't feasible at this time.
Rick - did I state that correctly?
-
:) I have been waiting for someone to call me out on this. I was having an "Alice in Wonderland" moment and dreaming BIG. I hope no one would ever mess around with SQL if they do not know it. Imagine the headaches to correct them all..! :o But if someone knew of some nice queries to run, sharing is a good thing.