4 Replies Latest reply on Feb 1, 2013 3:40 AM by Gingernut

    Execute SQL AVG with unstored calculation field issue

    Gingernut

      I am unable to find the answer to my issue which is that AVG does not seem to be working in ExecuteSQL statement and I was wondering if it is because I am using an unstored calcualtion field to calculate the average on?

       

      Response Time is a TIME unstored calculated field

       

      Response Time = If ( IsEmpty ( Response Time Override ) ; (NetWorkingTime ( ArrivalDate ; Date ; CallArrivalTime ; CallTimePlaced )-LookupDateDiff*28800); Response Time Override )

       

      ExecuteSQL ( "SELECT
      Avg ("Response Time")
      FROM jobsheet
      WHERE ArrivalDate
      BETWEEN ? AND ?";

      "|" ; "" ;Interface::gFromDate; Interface::gToDate ) This returns ?

       

       

       

      This however does work

      ExecuteSQL ( "SELECT
      Count ("Response Time")
      FROM jobsheet
      WHERE ArrivalDate
      BETWEEN ? AND ?"

      ;

      "|" ; "" ;Interface::gFromDate; Interface::gToDate ) - but this returns "429" (the number of Jobsheets in the date range)

       

      Thanks in advance

       

      Cathy