Average of most recent records

[benmort81]

Hello All,

I have a table "Staff" and a related table "Lesson_Observations".  A Staff record has many Lesson_Observation records.  Each lesson observation is attributed a score.

I have a working ExecuteSQL calculation that finds the average of all lesson observations scores for staff in a specific Faculty ("English" in the example below) and only where the Staff record has the attribute "Active".

Let ( [ $query = "SELECT AVG(L.obCritAverageVal) 
FROM Lesson_Observation L INNER JOIN Staff S 
ON L.FK_StaffCode = S.PK_StaffCode
WHERE S.Fk_FacName = ? AND S.Status = ?"

; $result = ExecuteSQL ( $query
; "" ; ""
; "English"; "Active")

]; $result
)

Each Lesson_Observation record has an attribute "Observation_Date".  Is there a way I can modify the query above to find the average of only the MOST RECENT lesson_observation for each member of staff in a Faculty where that member is staff is still Active?

Thank you!

Ben

[eos]

Try

ExecuteSQL ( "
  SELECT AVG ( O.score ) 
  FROM Observations O

  JOIN Staff S On S.id = O.id_staff

  WHERE 
    S.faculty = ? AND
    S.status = ? AND
    O.TS = ( 
      SELECT MAX (O2.TS)
      FROM Observations O2
      WHERE O2.id_staff = O.id_staff
      )
  " ; "" ; "" ; "English" ; "Active" 
)

which you obviously need to adapt to your nomenclature

Edited September 1, 2015 by eos

[benmort81]

Genius!  This is neat!

I had worked around it by sorting the relationship in descending date order and then adding a calculated field Last_Ob_Score to the Staff table which = observation score from the Observation table - then doing a simple ExecuteSQL from the Last_Ob_Score field.

No need for the extra database overhead with your query.

Thanks