|
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 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.