SQL Avg Date Query 2

[Richo1980]

Hi,

I have an access DB which has various information on ticket closures within my department

I'm attempting to create a query which shows the average time it takes between a call being opened and resolved using the following

rs.Open "SELECT AVG(DATEDIFF(hh, [HPD Opended Date Time], [HPD Resolved Date Time])) FROM HD", conn

The format of my date and Time settings in my DB is

d/mm/yyyy h:mm:ss am

Anyone able to advise what i'm doing wrong?

Thanks

 

[bborissov]

What is wrong? That works for me as expected:

declare @test table ([HPD Opended Date Time] datetime, [HPD Resolved Date Time] datetime)
INSERT INTO @test values (GETDATE(),DATEADD(hh,10,GETDATE()))
INSERT INTO @test values (GETDATE(),DATEADD(hh,20,GETDATE()))
INSERT INTO @test values (GETDATE(),DATEADD(hh,30,GETDATE()))
SELECT * from @Test
SELECT AVG(DATEDIFF(hh, [HPD Opended Date Time], [HPD Resolved Date Time])) FROM @test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

First thing is, you might want to post this in the Access forum forum701

Second, how are you trying to display the recordset? And have you properly dim'd your connection and everything?

Third, are you sure that you are storing the dates as Date datatype, and not as Text?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Also....

You are not aliasing the column, so that may be causing your problem.

rs.Open "SELECT AVG(DATEDIFF(hh, [HPD Opended Date Time], [HPD Resolved Date Time])) [!]As AverageDuration[/!] FROM HD", conn

And...

DateDiff returns an integer, so your average will be an integer. I suggest you change your DateDiff to calculate the difference in hours, and then divide by 60.

rs.Open "SELECT AVG(DATEDIFF([!]minute[/!], [HPD Opended Date Time], [HPD Resolved Date Time])) [!]/ 60.0 AS AverageDuration[/!] FROM HD", conn

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Richo1980]

Thanks for the responses

Here is how I am displaying calling the data from my asp page

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "\\nsyd9009pap\c$\Inetpub\[URL unfurl="true"]wwwroot\GMS_Stats\Stats\db\Remedy_2006.mdb"[/URL]

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT AVG(DATEDIFF(hh, [HPD Opended Date Time], [HPD Resolved Date Time])) WHERE [HPD Resolved Date Time] is NULL FROM HD", conn

%>

<table align="center" border="0" size="100%">
	<tr>
		<td class="hnav">
			<b>Ticket No</b>
		</td>
		<td class="hnav">
			<b>Date Opened</b>
	</tr>	
<%for each x in rs.Fields
    next%>
</tr>
<%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
       <td><%Response.Write(x.value)%></td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
rs.close
conn.close
%>

</table>

</body>
</html>

I'm sorry I'm new to using this so I have probably written this incorrectly? Could you advise a better way to do it?

Thanks

 

[gmmastros]

Your [!]From[/!] clause needs to come before the [!]Where[/!] clause.

rs.Open "SELECT AVG(DATEDIFF(hh, [HPD Opended Date Time], [HPD Resolved Date Time])) [!]FROM HD[/!] WHERE [HPD Resolved Date Time] is NULL", conn

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

No matter that if you post in wrong forum (Here is SQL SERVER one not Access) but I see one HUGE mistake (along with one George point to):

SELECT AVG(DATEDIFF(hh, [HPD Opended Date Time],
                        [HPD Resolved Date Time]))
WHERE [COLOR=red][b][HPD Resolved Date Time] is NULL[/color][/b] FROM HD

What is the difference between '2007/03/28 13:00:00' and NULL?
According to SQL Server it is NULL, don't know what Access will return but I suspect it will be also NULL.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Boris, good catch. I suppose that it should really be Is Not NULL.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Richo1980]

Thanks Guys...

When I execute the code you suggested

rs.Open "SELECT AVG(DATEDIFF(hh, [HPD Opended Date Time], [HPD Resolved Date Time])) FROM HD WHERE [HPD Resolved Date Time] is NULL", conn

I get an Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters...It is referring to the code above

 

[Richo1980]

Thanks Guys...

When I execute the code you suggested

rs.Open "SELECT AVG(DATEDIFF(hh, [HPD Opended Date Time], [HPD Resolved Date Time])) FROM HD WHERE [HPD Resolved Date Time] is Not NULL", conn

I get an Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters...It is referring to the code above

 

[gmmastros]

That usually means you misspeelled one of your column names. Maybe this one:

[HPD [!]Opended[/!] Date Time]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Richo1980]

Hmm no all the field names are correctly listed (although you are correct about the crap spelling

 

[gmmastros]

Ok, well... since there are differences between SQL Server and Access, it's not surprising that we didn't catch this right away. I suggest you try...

rs.Open "SELECT AVG(DATEDIFF([!]'[/!]hh[!]'[/!], [HPD Opended Date Time], [HPD Resolved Date Time])) FROM HD WHERE [HPD Resolved Date Time] is Not NULL", conn

Put single quotes aroung the hh.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Richo1980]

Thanks for helping me with this mate! Still throwing an error though using 'hh'

Microsoft JET Database Engine error '80040e14'
Invalid procedure call

 

[gmmastros]

Sorry.

Take a look at this page.

http://www.techonthenet.com/access/functions/date/datediff.php

Looks like you should be using 'h' NOT 'hh'. If this doesn't resolve your problem, I would suggest that you post this question in the access forum.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

According to VBA help (I have only Excel and Word installed here):

The interval argument has these settings:

Setting Description 
yyyy Year 
q Quarter 
m Month 
y Day of year 
d Day 
w Weekday 
ww Week 
[COLOR=red]h Hour [/color red]
n Minute 
s Second

So I suppose the query must look like this:

rs.Open "SELECT AVG(DATEDIFF("h", [HPD Opended Date Time], [HPD Resolved Date Time])) FROM HD WHERE [HPD Resolved Date Time] is Not NULL", conn

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Hmmm, and maybe it is also wrong because of quotes used, maybe you should use a single quote for the string:

rs.Open 'SELECT AVG(DATEDIFF("h", [HPD Opended Date Time], [HPD Resolved Date Time])) FROM HD WHERE [HPD Resolved Date Time] is Not NULL', conn

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Richo1980]

It worked using the 'h'...

Much appreciated gmmastros and bborissov..