avg wating time, work out

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?

LVL 12
pigmentartsAsked:
Who is Participating?
 
Gautham JanardhanConnect With a Mentor Commented:
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
 
Gautham JanardhanCommented:
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
 
pigmentartsAuthor Commented:
no, if i take out the where bit i still get the same number, the problem i think is in the first query.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Gautham JanardhanCommented:
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
 
pigmentartsAuthor Commented:
so could i state to leave the null values out?
0
 
pigmentartsAuthor Commented:
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
 
pigmentartsAuthor Commented:
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
 
pigmentartsAuthor Commented:
o i see,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.