Custom query example
Top  Previous  Next
Important Differences Between Academic Values and Values in SQL
In order to get the most from the user defined group module it is important to understand some fundamental differences between the way academic values are used and the way SQL (or any spreadsheet/database application) understands them. In SQL A is worth less than F. This is different from the world of academia where a grade A is worth more than a grade F. This is easily dealt with providing that you bear this in mind when specifying conditions for your user defined group.
Example:

WHERE ICT_Y11_Predicted_GCSE is less than Value: C

Finds all the pupils whose ICT_Y11_Predicted_GCSE grade is either A,A* or B

WHERE ICT_Y11_Predicted_GCSE is greater than Value: C

Finds all the pupils whose ICT_Y11_Predicted_GCSE grade is either D,E,F or G

Numeric values can obviously be dealt with as expected.

Example Query
In the example below a new whole school query is generated to update the shared field Y11_Traffic_light to the Value: RED for all pupils in Y11 whose Y11_Predicted_Grade is greater than their FFT_GCSE_Estimate. Separate queries can also be executed to populate the traffic light field for AMBER and GREEN pupils.

ICT set 11A/IT1 before query execution
Set 11A/IT1 have been used to test this query. The image below displays the Y11_Traffic_light field for this set prior to query execution.
traffic_light_before

Adding a new whole school query
A new custom query is generated to update the shared field Y11_Traffic_light to the value: RED for all pupils in Y11 whose Y11_Predicted_Grade is greater than their FFT_GCSE_Estimate across all departments. A name and description is provided for the query and the query is not scheduled to run every night. Review query as statement has been clicked displaying the query about to be executed as a statement in both plain English and SQL (Structured Query Language).
new_whole_school_query

ICT set 11A/IT1 after query execution
The image below displays the Y11_Traffic_light field for this set after query execution. The Y11_Traffic_light field has now been populated with the Value: RED for pupils who have been predicted a GCSE grade lower than their Fischer Family Trust Estimated grade.

Note: The pupil Casey Loughman who has been predicted an A* but who has a FFT estimate of A, is displayed as RED which is not the desired result. A* causes problems because it has a higher value than A, just as B has a higher value than A. In a sorted alphabetical list A* would appear A,A*,B,C,D,E,F etc... Because the criteria WHERE Y11_Predict_Grade is greater than FFT_GCSE_Estimate has been used to determine the RED pupils, it means that pupils with a Y11_Predicted_Grade of A* and an FFT_GCSE_Estimate of A appear as RED pupils. This exception can be handled in a separate query by explicitly setting the traffic light of such pupils to GREEN. See the custom queries on the online demo for details and examples.

traffic_light_after

Viewing the Query Log
Clicking on Manage under the Action column for the query displays the Query Log, Query Statement, Query name and Query description along with the option to schedule the query. By studying the query log it is also possible to see the tables affected by the query along with the number of affected rows for each table.
query_log
This query can now be executed at any time.