× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



My general solution to this type of problem is something like

Select
Sum(ThingIWantToAverage)/sum(Case when ThingIWantToAverage = 0 then 0
else 1) from some_table

If you fields that you want to drop do not actually contain a 0 then you
will have to put a case to force them to 0 in the numerator.

Chris

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Rick.Chevalier@xxxxxxxxxxxxxxx
Sent: Friday, February 02, 2007 2:19 PM
To: midrange-l@xxxxxxxxxxxx
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
AmeriCredit
Enterprise Solutions
817-525-7178



Privileged and Confidential.  This e-mail, and any attachments there to,
is intended only for use by the addressee(s) named herein and may
contain privileged or confidential information.  If you have received
this e-mail in error, please notify me immediately by a return e-mail
and delete this e-mail.  You are hereby notified that any dissemination,
distribution or copying of this e-mail and/or any attachments thereto,
is strictly prohibited.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.