Awesome Average Time between date fields SQL function

Researching this function and finding a solution took alittle bit of time but it does work very well in finding any average time between date fields in SQL and sorting by a value and including a conditional range of time.

First - you'll need to isolate on your two time/date fields and make them compatible with the SQL report front end you're using. You'll need to find a time field value that can be subtracted from itself. Critical; basing in minutes should do the job.

e.g. [date1] - [date2] = yeilding the appropriate elapsed time. In this case minutes was the time value base.

Side note: Attunity Report Designer / Query Builder SQL Reporting tool was used as a front end to the back end Tandem Non Stop CareCast(GE Healthcare Application)/Oracle DB. Should be dooable in SQL Report Writer too.

Take the two time stamp fields and subtract the time diff between them yeilding the correct converted elapsed time value. I know it can be hard but it should be dooable; takes some testing / date-time conversion manipulation / manual reading.

Add in the datediff, avg, (and round/truncate functions if needed)

Average (any time diff) Date/Time duration SQL sorted by DEPT + additional TABLE/FIELD conditions.

select DEPT, avg(datediff(minute,DATE1_TIMESTAMP,DATE2_TIMESTAMP)) from TABLE where (DATE2_TIMESTAMP BETWEEN TO_DATE('2007-04-27') and TO_DATE('2007-05-04') and TABLE FIELD CONDITION = '??????; if needed') or TABLE FIELD CONDITION = '??????; if needed' group by DEPT having DEPT BETWEEN 1 and 5 order by DEPT asc

This function can be modified further (w/testing of course) to produce a rounded or truncated figure for the Boss.

trunc(avg(datediff(minute,DATE1_TIMESTAMP,DATE2_TIMESTAMP)),0)

One heck of a SQL Average date time solution (function) for a given conditional range sorted by a value. The boss loves checking utilization of ...ah em... work being performed .... please...do no evil - for constructive use only. Received on Tue May 15 2007 - 10:57:33 CDT