Solved

avg wating time, work out

Posted on 2006-11-14
8
354 Views
Last Modified: 2008-03-06
hi, i am trying to work out my avg waiting time, you guys helped me with the month range, but the avg time outputed on the the query below is wrong can anyone see where i am going wrong here?

first i get all my start and end first post times out of my database for each ticket

<cfquery name="getDomain" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">



select t.ticketID
     , MIN(t.[datetime]) as firstdate
     ,(
          select TOP 1 [datetime]
          FROM issues
          WHERE ticketID = t.ticketID
          AND [datetime] <> MIN(t.[datetime])
          ORDER BY [datetime]
     ) AS seconddate
     ,
CAST(
     (
          (
               select TOP 1 [datetime]
               FROM issues
               WHERE ticketID = t.ticketID
               AND [datetime] <> MIN(t.[datetime])
               ORDER BY [datetime]
          )
          - MIN(t.[datetime])
     )
     AS float
) AS waittime
INTO temp
from issues t
GROUP BY t.ticketID


</cfquery>

one it created a temp table i now have a list of all my ticket times like so


my temp TABLE as you can see from above its not getting the correct values or i am missing something?

- waittime -                                      firstdate                                   seconddate                        
6.94444444444444E-04                     13/11/2006 13:39:00                13/11/2006 13:40:00            
0.661805555555556                          13/11/2006 17:05:00                14/11/2006 08:58:00            
1.38888888888889E-03                     13/11/2006 15:07:00                 13/11/2006 15:09:00        
NULL                                                13/11/2006 15:20:00                 NULL
NULL                                                13/11/2006 19:14:00                 NULL
6.94444444444444E-04                     13/11/2006 21:56:00                 13/11/2006 21:57:00



<cfquery name="getavg" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">
select avg(waittime) AS wtime  FROM temp
where firstdate >= dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  )
and firstdate < dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
</cfquery>

<cfoutput query="getavg">
#wtime#
</cfoutput>


the output i get is:  0.166145833333  which is wrong?

0
Comment
Question by:pigmentarts
  • 5
  • 3
8 Comments
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17937224
1) Returns the average of the values in a group. Null values are ignored.

2) it might be due to the time part in the where caluse.

select dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  )

will give u 2006-11-01 00:00:00.000


and

select dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))

will give u 2006-12-01 00:00:00.000

now ur query will look like

select avg(waittime) AS wtime  FROM temp
where firstdate >= 2006-11-01 00:00:00.000
and firstdate < 2006-12-01 00:00:00.000

Check is that what u actually require.



0
 
LVL 12

Author Comment

by:pigmentarts
ID: 17937231
no, if i take out the where bit i still get the same number, the problem i think is in the first query.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17937293
select (6.94444444444444E-04 + 0.661805555555556 + 1.38888888888889E-03 + 6.94444444444444E-04 )/ 4

this is eqv to ur average and its right as far as ue temp table data is now concerned.

it has 4 not null values and (sum of 4 not null values / 4) will give u the avg which is 0.166....

so its right
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 12

Author Comment

by:pigmentarts
ID: 17937501
so could i state to leave the null values out?
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 17937506
where firstdate >= dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  )
and firstdate < dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))


and where seconddate IS NOT NULL?
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 17937526
using

<cfquery name="getavg" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">
select avg(waittime) AS wtime  FROM temp
where firstdate >= dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  )
and firstdate < dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
AND seconddate IS NOT NULL                        
</cfquery>

i now get this...

0.109018535053
0
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 500 total points
ID: 17937653
date null is not what i am talking abt, read the first comment i posted

1) Returns the average of the values in a group. Null values are ignored.

null values in waittime is ignored for avg calculation.

for ex lets say u have a column with values

10
20
30
null
null
40

the average here is 100/4 = 25
and not 100/6 = 16.66

Hope its clear



0
 
LVL 12

Author Comment

by:pigmentarts
ID: 17937685
o i see,
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question