The following examples exist for the web version although some may work in the client for the time it is still relevant:


--Sometimes you need to set a value to a blank string ""

--This changes U9 to a space in the client

CHANGE STU U9 TO " "

--Vs

--This changes it to a blank, zero length string in the web

CHANGE STU U9 TO (( '' ))


--Grad Year in a math function that you never have to change (web only)

LIST STU SC ID NM GR (( SUBSTRING(DB_NAME(),4,2) + 2001 + 12 - GR ))


--Gradebook term scores and percent (right column contains DTS of last term specific recalculation most likely caused by teacher intervention and will suffice as a rudimentary last teacher modification timestamp)

LIST STU GBV GBU GBK GBE MST TCH STU.SC STU.ID STU.NM STU.GR STU.TG? GBK.GN GBV.CD GBK.NM TCH.TE GBE.SE MST.CN GBV.MK GBV.PC (( CAST(GBV.DTS AS DATETIME) )) IF GBK.GN = GBV.GN AND GBK.SC = TCH.SC


--Gradebook term scores and percent if you wish to focus in on a specific day

LIST STU CAR GBV GBU GBK GBE MST TCH STU.SC STU.ID STU.NM STU.GR STU.TG? GBK.GN GBV.CD GBK.NM TCH.TE MST.PD GBE.SE MST.CN GBV.MK GBV.PC BY STU.ID GBV.CD MST.PD IF GBK.GN = GBV.GN AND GBK.SC = TCH.SC AND CAR.SE = GBE.SE AND CAR.DS <= "3/27/2018" AND CAR.DE >= "3/27/2018"


--Gradebook term scores and percent including assignment percentage when title is like “Final”

LIST STU GBS GBA GBV GBU GBK GBE MST TCH STU.SC STU.ID STU.NM STU.GR STU.TG? GBK.GN GBV.CD GBK.NM TCH.TE GBE.SE MST.CN GBV.MK GBV.PC GBA.DD GBA.AD GBS.PC IF GBK.GN = GBV.GN AND GBK.SC = TCH.SC AND GBA.AD : "FINAL"


--Gradebook Assignment tallies

TOTAL TCH GBK GBA GBR TCH.TE GBK.NM GBR.CD GBK.PD BY TCH.TE GBR.CD GBK.NM GBK.PD IF GBK.SC = TCH.SC AND GBA.DD >= GBR.SD AND GBA.DD <= GBR.ED


--Gradebook Missing Assignment

LIST STU GBU GBS GBA GBK GBR TCH STU.ID STU.NM GBK.GN GBR.CD GBK.NM TCH.TE GBU.CSC GBU.CMK GBA.AN GBA.AD GBA.DD GBA.GC GBS.MK IF GBA.GC = 1 AND GBS.MK = " " AND GBA.DD >= GBR.SD AND GBA.DD <= GBR.ED AND GBK.SC = TCH.SC


--Gradebook Comments Made on Scores

LIST TCH GBK GBA GBS STU TCH.SC TCH.TE GBK.NM GBA.AD STU.ID STU.NM GBS.MK GBS.CO IF GBS.CO > " "


--A Query to find Double quotes in first or last names...

LIST STU ID NM IF LN : "'+CHAR(34)+'" OR FN : "'+CHAR(34)+'"


Why does this query work in Client but not in Web?

LIST STU SC NM ID GR GP DE DA DP (( LEFT ( (( DP / DE * 100 )) , 5 ) + "%" )) BY NM (( DP / DE )) GP IF DE # 0 AND DA # 0

--SQL is the engine behind the Aeries web version and not MS Access.

--SQL does not presume the output to be a different precision than the elements being divided (integer divided by integer will cast output as an integer)


LIST STU ID NM (( 1 / 2 ))                                            = 0   ?!?!


--If one of the elements in the division operation where a decimal the output could then be depicated as a decimal and give the value you may need

LIST STU ID NM (( 1 / 2.0 ))                                         = 0.5


--Multiplying either element by 1.0 is harmless and will allow SQL to depict the final outcome as a decimal.

LIST STU ID NM (( 1.0 * 1 / 2 ))                                   = 0.500



--This does attendance percentage in Aeries Query in the web by including a decimal multiplication before division and will allow the output to be cast as a decimal.

LIST STU NM DE DA DP (( 1.0 * 100 * DP/DE )) IF DE # 0

--Use of the STU.DE/DA/DP fields is unnecessary recalculation work. Use this Aeries query in Web after enabling Attendance History recalculation nightly.

LIST STU AHS STU.NM AHS.EN AHS.AB AHS.PR (( 1.0 * 100.0 * AHS.PR/AHS.EN )) IF AHS.EN # 0 AND AHS.YR = "2016-2017"

--Or a more complicated example rounding to two decimal places and concatenating a ‘%’ on the end of it…

LIST STU AHS STU.LN STU.FN STU.GR AHS.YR AHS.EN AHS.PR (( CAST( CAST( ROUND ( 1.0 * 100 * AHS.PR / AHS.EN ,2 ) AS DEC(20,2) ) AS VARCHAR(21) ) + "%" )) BY (( 1.0 * 100 * AHS.PR / AHS.EN )) IF AHS.EN # 0 AND AHS.YR = "2017-2018"


--An Admin Account can query the LOG table to investigate modifications of data

LIST LOG IF CD : "CON"


--Including a math function of (( DTS )) will list the Date Time Stamp

LIST LOG TID DT SC SN ID USR RCD OD ND (( DTS )) IF CD : "CON"


--An Admin Account can also query the email log table (EML)

LIST EML EID TN UN SCL DT FAD TAD SUB (( DTS )) IF TAD : "parent.brown@example.com"

--Columns that are already a date time may be depicted as a date when you also need the time. Try the following when querying ASL

LIST ASL SC TN DT P0 P1 P2 P3 P4 P5 P5 P7 P8 P9 (( CAST(T0 AS DATETIME) )) (( CAST(T1 AS DATETIME) )) (( CAST(T2 AS DATETIME) )) (( CAST(T3 AS DATETIME) )) (( CAST(T4 AS DATETIME) )) (( CAST(T5 AS DATETIME) )) (( CAST(T6 AS DATETIME) )) (( CAST(T7 AS DATETIME) )) (( CAST(T8 AS DATETIME) )) (( CAST(T9 AS DATETIME) ))


--Total D's or F's for GRD Mark 1

TOTAL STU GRD STU.FN STU.LN GRD.M1 BY STU.FN STU.LN GRD.M1 IF GRD.M1 : "D" OR GRD.M1 : "F"



--Listing Program involvement isn’t easy as you will only see those that do have it or those with no other PGM records at all with the following two queries.

LIST STU PG STU.SC STU.ID STU.NM STU.GR STU.SP STU.SP? PGM.CD? IF PGM.CD = "135" OR PGM.CD = NULL


--Trying to get very precise still fails to list all students

LIST STU PG STU.SC STU.ID STU.NM STU.GR STU.SP STU.SP? PGM.CD? IF ( PGM.CD = "135" OR PGM.CD = NULL ) AND ( ( PGM.PSD <= "3/7/2017" AND ( PGM.PED >= "3/7/2017" OR PGM.PED = NULL ) ) OR ( PGM.ESD <= "3/7/2017" AND ( PGM.EED >= "3/7/2017" OR PGM.EED = NULL ) ) )


Using the Custom Report Builder is a far easier way to output program involvement and other sub groups.


Analytics is another friendlier way to dig in to data. You can drill down by any other sub group far easier than having to be adept at a query language.


Since people still had an interest in having a lot of relational data depicted on one line, I have made SQL queries to do that. The future may allow for the easy execution of store SQL Queries, Views, or maybe even Stored Procedures from Aeries Query. 


Here's a link to that SQL Query...

https://support.aeries.com/solution/articles/14000082583-dynamic-sql-queries-to-provide-a-rich-one-line-result-of-whatever-unique-programs-and-or-authorizatio