?
Solved

avg wating time, work out

Posted on 2006-11-14
8
Medium Priority
?
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 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