RE: Omit record from SQL AVG based on field value

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Rick, I don't know where to start with your SQL statement, it's too much
work for Friday afternoon :(

I do believe that what you need is to simply ignore the fields where you
have no values by casting them to NULLs.  You do this with CASE clause.
I googled and found this link that illustrates the point:

http://www.craigsmullins.com/ssu_0899.htm

See SQL statement #3.

If you can simplify your statement or at least narrow down the problem, post
back.

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
Subject: Omit record from SQL AVG based on field value


I have an SQL statement that averages process times for a group of
records.  The issue I am having is that some records contain incomplete
data.  That is acceptable, but it's throwing the average way off.  Each
record has six timestamps, each one for a different step in the process.
It is possible that the process will be stopped before all steps have
been performed.

What I want is to calculate the average process time for each step but
omit the steps that were not performed.  For example, if a record has
time stamps for steps 1 - 4 but not steps 5 & 6, include the process
times into the average for steps 1 - 4 but not in steps 5 & 6.  I have
been working on this since yesterday but I'm just not seeing how to do
it without moving to RPG.  If anyone has any ideas I'd really appreciate
the help.

This is the statement:
with AvgTime as
    (select 'Average', 999999999,                                   
      CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,                         
         Cast(avg(locrtst - loctst) as dec(20,6)),                  
      CURRENT_TIMESTAMP, Cast(Avg(loctptst - locrtst) as dec(20,6)),
      CURRENT_TIMESTAMP, Cast(Avg(locbtst - locrtst) as dec(20,6)), 
      CURRENT_TIMESTAMP, Cast(Avg(locftst - locbtst) as dec(20,6)), 
      CURRENT_TIMESTAMP, Cast(Avg(loctftst - locftst) as dec(20,6)) 
    from acdtalib.lopcadt

    where locdte = '2007-02-01'                                      
    group by locdte)                                                
                                                                    
    select 'Application', locapp,                                   
      loctst as Received,                                           
      locrtst as parsed, locrtst - loctst as Parse_Time,         
      loctptst as Ack_XML, loctptst - locrtst as Ack_XML_Time,      
      locbtst as Booked, locbtst - locrtst as Booking_Time,

      locftst as Funded, locftst - locbtst as Funding_Time,

      loctftst as Processed_XML, loctftst - locftst as
Processed_XML_Time  
   from acdtalib.lopcadt

   where locdte = '2007-02-01'

   Union All

   select * from AvgTime

   order by 1 Desc;    

Rick Chevalier