Data Replication 2
Results 1 to 2 of 2

Thread: Report Designer / Query Builder - Calculating AVG Time

  1. #1
    Laynester is offline Junior Member
    Join Date
    Apr 2007
    Posts
    1
    Rep Power
    0

    Report Designer / Query Builder - Calculating AVG Time

    G'day Attunity Experts please advise on how to do a AVG time calculation that can be preformed on the date fields within Attunity Report Designer / Query Builder JDBC<->ODBC to Tandem / Oracle

    AVG Time Problem: finding a way to "average" the time found within that 1d range.

    Query Builder select:

    TASK - Develop report from FINDNGP (tandem SQL Table) of avg amt of time between SERV_DATA_CREATE_TS and the DATA_TS by FINDNG_FAC_NUM where FINDNG_SERV_TYPE_CDE = ASSESS or INO.

    QB SELECT USED: select FINDNGP.SERV_DATA_CREATE_TS, FINDNGP.DATA_TS, FINDNGP.FINDNG_SERV_TYPE_CDE, FINDNGP.FINDNG_FAC_NUM from FINDNGP where FINDNGP.FINDNG_SERV_TYPE_CDE = 'ASSESS' and convert(FINDNGP.SERV_DATA_CREATE_TS,DATE) = {d '2007-03-27'}

    This select selects over 200,000 rows of data for a single day and returns it to QB in about 45min time or so. My next step is to AVG the time difference between FINDNGP.SERV_DATA_CREATE_TS and FINDNGP.CREATE_TS

    How can this be done? Do I need to export to EXCEL and formulate? Or can this be done with the RD function 'average(column,[group])?

    Humblely yours,

    Laynester


  2. #2
    laura is offline Support
    Join Date
    Aug 2006
    Posts
    19
    Rep Power
    0

    QP Average function (AVG)

    As describe in the Attunity Connect User Guide, we have an Average function (AVG) - please refer to the Chapter 69 Attunity SQL Syntax / Functions / Aggregate Functions.

    But our QP AVG function does not work on date fields - it only works on numbers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •