Calculations

1. Senior Member
Join Date
Dec 1969
Posts
2,031

## Calculations

I have a database with Start Times and Stop times, both of which are in the following format:<BR>5/14/2001 11:10:00 AM<BR>I need to find the average, min and max resolution times for all of the records in the database. A simplified example of what I need to do:<BR>Start Time <BR>5/16/2001 10:00:00 AM <BR>Stop Time <BR>5/16/2001 11:00:00 AM <BR>(resolution time is 1 hour)<BR>Start Time:<BR>5/16/2001 9:00:00 AM <BR>Stop Time: <BR>5/16/2001 12:00:00 AM <BR>(resolution time is 3 hours)<BR><BR>Average resolution time is 1.5 hours ...((3hours + 1hour)/2records)<BR>Min resolution is 1 hour<BR>max resolution is 3 hours<BR>How can I accomplish this, since the usual min, max won&#039;t work in the SQL statement since I am not finding the min and max of any existing value in the db, but a calculated value based on fields in the db. Any help appreciated!!!

2. Kentium Guest

## RE: Calculations

I guess i&#039;m confused... You&#039;re trying to figure this stuff out in the SQL statement?<BR><BR>Why not set some vars equal to your data, and then run the math on the vars?<BR><BR>You could use regular expressions to pull out the times, and then run the math from there....that&#039;s how i&#039;d do it.

3. Senior Member
Join Date
Dec 1969
Posts
2,031

## Not in the SQL

I&#039;m not trying to figure it out in the SQL, I just wanted to make sure I posted that you couldn&#039;t do this within the SQL so that I wouldn&#039;t get suggestions to do:<BR>SQL = "SELECT AVG(Field) etc. <BR>I planned to use variables but I wasn&#039;t sure how to use min, max and avg with variables..never done it before.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Easy enough...

...though you certainly *CAN* do this in most dialects of SQL, if you wanted to.<BR><BR>But anyway, in VBS:<BR><BR>&#060;%<BR>... get the recordset ...<BR><BR>resTotal = 0<BR>resCount = 0<BR>resMin = 1999999999 &#039; hopefully SOME res less than this!<BR>resMax = -1999999999 &#039; and one greater than this!<BR><BR>Do Until RS.EOF<BR>&nbsp; &nbsp; startTime = RS("startTime")<BR>&nbsp; &nbsp; stopTime = RS("stopTime")<BR>&nbsp; &nbsp; resTime = DateDiff( "h", startTime, stopTime )<BR>&nbsp; &nbsp; If resTime &#060; resMin Then resMin = resTime<BR>&nbsp; &nbsp; If resTime &#062; resMax Then resMax = resTime<BR>&nbsp; &nbsp; resTotal = resTotal + resTime<BR>&nbsp; &nbsp; resCount = resCount + 1<BR>&nbsp; &nbsp; ... other processing of this record ...<BR>&nbsp; &nbsp; RS.MoveNext<BR>Loop<BR>%&#062;<BR>Minimum resolution time was &#060;% = FormatNumber( resMin,2 ) %&#062; hours.&lt;br&#062;<BR>Maximum resolution time was &#060;% = FormatNumber( resMax,2 ) %&#062; hours.&lt;br&#062;<BR>Average resolution time for &#060;% = resCount %&#062; incidents was &#060;% = FormatNumber( resTotal / resCount, 2 ) %&#062; hours.&#060;P&#062;<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## And in Access SQL...

Select Min( DateDiff(&#039;h&#039;,startTime,stopTime) ) AS resMin,<BR>&nbsp; &nbsp; &nbsp; Max( DateDiff(&#039;h&#039;,startTime,stopTime) ) AS resMax,<BR>&nbsp; &nbsp; &nbsp; Avg( DateDiff(&#039;h&#039;,startTime,stopTime) ) AS resAvg<BR>FROM table<BR>WHERE ...<BR><BR>

#### Posting Permissions

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