Averaging Datetime Fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Averaging Datetime Fields

Author

Topic

drprune
Starting Member

4 Posts

Posted - 2007-08-07 : 10:39:27

I have an ASP page that creates and populates an HTML table from a SQL table. The SQL table contains two datetime fields (SignInTime and RepTimeIn). In the SELECT statement that populates the HTML table, I use the following code to populate a third column called "Wait Time" that is the difference between the two fields named above:

convert(varchar,RepTimeIn-SignInTime,108) Wait

This works fine. However, in another location on the same page I would like to display the average of all of these "wait times", but I have been unable to do so. Can anyone suggest a possible solution?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-07 : 10:45:04

select convert(datetime,avg(convert(float,waittime)))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

drprune
Starting Member

4 Posts

Posted - 2007-08-07 : 11:26:25

Thus far, I'm getting no results. Here is the code for the section of the page where I want to write the average of my wait times:

<table border="1" cellspacing="0" cellpadding="3" bordercolor="#7577af" bgcolor="#ffffff" align="center">
<%
Response.Write "<tr>"
Response.Write "<td align='center' class='dlink' bgcolor='#dadada'><b>Average Wait Time</td>"
Response.Write "</tr>" & VbCr

sql=""
sql=sql & "SELECT convert(datetime,avg(convert(float,waittime))) FROM " & DBTableName
sql=sql & " WHERE CreatedDate = '" & Date() & "' "

If DebugMode then Response.Write "<br>[" & sql & "]<br>"

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3

NumberOfRows=rs.RecordCount

Response.Write "<tr valign=top>"
Response.Write "<td align='center' class='dlink' bgcolor='#C6ECF9'> " & trim(rs("waittime")) & "<br></td>"
Response.Write "</tr>" & VbCr

rs.Close
Set rs=Nothing
%>
</table>

Of course, my "Wait" field from my original query is not in this query, so I'm guessing that I must add the original wait time calculation from my first query (convert(varchar,RepTimeIn-SignInTime,108) Wait) to your statement in place of "waittime"?

drprune
Starting Member

4 Posts

Posted - 2007-08-07 : 13:46:59

nr,

Many thanks - I have it working now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources