A SQL Query Use Case

Sometimes t.admins need information that is not accessible through TGReports.  Alternative solutions include customized TG reports or SQL queries.  The following use case involved a customer request and the resulting SQL query. This query can be used by customers who use TG LAN_sql or TG Web.

 This content is intended for t.admins who have

• An intermediate to advanced knowledge of Test Generator and TG Reports... 
• Created, edited and published tests...
• Experience using sql queries or have technical assistance available
  
  

 

Customer Request Request:

"Is there a way to see how long a person has taken to complete a test?  I would like the following information:"  

Course Name	Test Name
Attempts	Possible Score
Score	Percent
Start Date / Time	End Date / Time
Time Used (seconds)	Pass / Fail
First Name	Last Name
Email	Time Taken hh:mm:ss
Review Time hh:mm:ss

Solution: SQL Queries and more...

Providing SQL queries has become the preferred solution--faster to implement and involves less development time.  Other, fee-based options include customizing Crystal Reports (2008 or later) and browser-based reports accessible using TG's Admin Dashboard.  Please contact us if you'd like to explore these options,  

 

---

MySQL Query

 Note : A MSSQL version of the query is also available, please contact us for more information.

SELECT

               c.cl_code 'Class Name',

               t.tst_name 'Test Name',

               SU_ATTEMPTS 'Attempt #',

               s.SU_Q_BANK_SCORE 'Possible Score',

               s.SU_LASTSCORE Score,

               floor((s.SU_LASTSCORE/s.SU_Q_BANK_SCORE)*100+.5) Percent,

               SU_DATESTARTED 'Start Date',

               SU_DATEENDED 'End Date',

               SU_TIMEUSED 'Time Used',

               if(su_passed=-1,

               'Passed',

               'Failed') Result ,

               ta.TA_FIRST 'First Name',

               ta.TA_LAST 'Last Name',

               TA_Email 'E-mail' ,

               SEC_TO_TIME(SU_TIMEUSED) 'Time Taken hh : mm :ss',

               CASE when IFNULL(s.su_review_timeused,

               0) =0 then s.su_review_timeused else SEC_TO_TIME(TIMESTAMPDIFF(second,

               s.su_dateended,

FROM_UNIXTIME(s.su_review_timeused))) end 'Review Time hh : mm :ss'

FROM

               tests t,

               takers ta,

               takers_tests tt,

               tests_classes tc ,

               classes c ,

               summary s

WHERE

               t.TST_ID= tt.tst_id AND

               ta.TA_ID = tt.ta_id AND

               t.tst_id = tc.tst_id AND

               tc.cl_id= c.cl_id AND

               t.TST_ID= s.TST_ID AND

               tc.CL_ID= s.CL_ID AND

               ta.TA_ID= s.TA_ID

ORDER BY

               c.cl_code,

               t.tst_name,

               ta.TA_ID,

               s.SU_ATTEMPTS;