Solved

avg wating time, work out

Posted on 2006-11-14
8
367 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

696 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