---
title: A Guide to Test Generator SQL Queries
description: Sometimes t.admins need information that is not accessible through TGReports.  Alternatives include customized TG reports or SQL queries.  Use Case example
image: https://www.testshop.com/hubfs/Support-Documents/tutorials/Rescore%20Test/tools_rescore-test-menu.png
---

[![Testshop](https://www.testshop.com/hs-fs/hub/95240/file-2207962380.png?width=226&name=file-2207962380.png "Testshop")](https://www.testshop.com)

### [![call testshop](https://www.testshop.com/hs-fs/hub/95240/file-2214127187-png/testshop/phone_img.png?width=23&name=phone_img.png "call testshop")](https://www.testshop.com/sales) Call Us : +1 928-380.6570

# TG Data Resouces A Guide to Test Generator's SQL Queries 

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

#### ![training2](https://www.testshop.com/hs-fs/hubfs/icons_2019/training2.png?width=130&name=training2.png)

- 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

 

#### **![request-2](https://www.testshop.com/hs-fs/hubfs/icons_2019/request-2.png?width=1&name=request-2.png)![request-2](https://www.testshop.com/hs-fs/hubfs/icons_2019/request-2.png?width=130&name=request-2.png)Customer Request **Request:

"I need the following information in a single report."  

| Course Name | Percent |
| --- | --- |
| Attempts | End Date/Time |
| Result | First Name |
| Last Name | UserID |
| Custom User Fields |   |

**![question-bank_nesting-1](https://www.testshop.com/hs-fs/hubfs/icons_2019/question-bank_nesting-1.png?width=130&name=question-bank_nesting-1.png)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](https://www.testshop.com/contact) if you'd like to explore these options,  

 

---

#### Query

** Video : a short, silent demonstration is available [here](https://www.testshop.com/hubfs/Query-info_2019/query-tutorial.mp4)*  
*video capture courtesy loom.com**

**SELECT**

                t.tst_name as 'Test',

                c.cl_code 'Course Name',

                s.SU_ATTEMPTS 'Attempt #',

                case when s.SU_Q_BANK_SCORE =0 then 0 else

                round(s.SU_LASTSCORE/s.SU_Q_BANK_SCORE *100,

                2) end 'Percent',

                s.SU_DATEENDED 'End Date',

                case when s.su_passed = -1 then 'Passed' else 'Failed' end Result,

                tk.TA_FIRST 'First Name',

                tk.TA_LAST 'Last name',

--- replace the CF #s with the names of your custom fields and remove any not in use

                tk.ta_custom1 as 'CF-1',

                tk.ta_custom2 as 'CF-2',

                tk.ta_custom3 as 'CF-3',

                tk.ta_custom4 as 'CF-4',

                tk.ta_custom5 as 'CF-5',

                tk.ta_custom6 as 'CF-6',

                tk.ta_custom7 as 'CF-7',

                tk.ta_custom8 as 'CF-8',

                tk.ta_custom9 as 'CF-9',

                tk.ta_custom10 as 'CF-10'

**FROM**

                tests t,

                tests_classes tc,

                classes c,

                takers tk,

                takers_tests tt,

                summary s

**WHERE**

                t.TST_ID = tc.TST_ID AND

                tc.CL_ID= c.CL_ID AND

                t.TST_ID = tt.tst_id AND

                tc.CL_ID = tt.cl_id AND

                tt.ta_id= tk.TA_ID AND

                tt.tst_id = s.TST_ID AND

                tt.cl_id = s.CL_ID AND

                tt.ta_id= s.TA_ID AND

                s.su_passed=-1 AND

                c.cl_code='your course name here'

**ORDER BY**

                c.cl_code,

                t.tst_name,

                tk.TA_ID,

                s.SU_ATTEMPTS

## Contact

- +1-928-380-6570
- [support@testshop.com](mailto:)

- <https://www.youtube.com/user/TestGenerator>
- <https://twitter.com/share>
- <https://www.linkedin.com/shareArticle?mini=true&url=https://www.testshop.com/a-guide-to-test-generator-sql-queries>

## company

- [About Us](https://www.testshop.com/about-new)
- [Support](https://www.testshop.com/tg-support-request)
- [Blog](https://www.testshop.com/blog)

## Recent post

<https://www.testshop.com>[![Testshop](https://www.testshop.com/hs-fs/hub/95240/file-2211619311.png?width=34&name=file-2211619311.png "Testshop")](https://www.testshop.com) Testshop 2024. All RIGHTS RESERVED