Solved

avg wating time, work out

Posted on 2006-11-14
8
337 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 12

Author Comment

by:pigmentarts
Comment Utility
so could i state to leave the null values out?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 12

Author Comment

by:pigmentarts
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
o i see,
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now