pigmentarts
asked on
my average waiting time question?
I have a support system written in ColdFusion and now would like to generate a report using some of data I have in the backend database.
I need to output the average waiting time this month for my tickets
In my database each ticket has a time and date, i need to know how i would pull these and work out this..
so example data in my database would be
ticketID datetime
1 Nov 13 2006 5:05PM
1 Nov 13 2006 5:05PM
1 Nov 13 2006 5:05PM
1 Nov 13 2006 5:05PM
2 Nov 13 2006 5:05PM
2 Nov 13 2006 5:05PM
3 Nov 13 2006 5:05PM
3 Nov 13 2006 5:05PM
1) i first need to get all tickets out of the database, take the first datetime for each ticket.
2) then i need to get the second datetime for each ticket (if it has one) as this is the first time the ticket was responded to, all datetimes after that for that ticket does not matter.
we now have all the start datetime of the each ticket and all the first post (the response) of all tickets
3) i then need to work out the average waiting time per month based on the set of data i have for all the tickets
can you see now why i am so confused? after all that i would like to maybe use the new cfchart to present my data?
is any of this possible and could anyone give me a hand understand this?
I need to output the average waiting time this month for my tickets
In my database each ticket has a time and date, i need to know how i would pull these and work out this..
so example data in my database would be
ticketID datetime
1 Nov 13 2006 5:05PM
1 Nov 13 2006 5:05PM
1 Nov 13 2006 5:05PM
1 Nov 13 2006 5:05PM
2 Nov 13 2006 5:05PM
2 Nov 13 2006 5:05PM
3 Nov 13 2006 5:05PM
3 Nov 13 2006 5:05PM
1) i first need to get all tickets out of the database, take the first datetime for each ticket.
2) then i need to get the second datetime for each ticket (if it has one) as this is the first time the ticket was responded to, all datetimes after that for that ticket does not matter.
we now have all the start datetime of the each ticket and all the first post (the response) of all tickets
3) i then need to work out the average waiting time per month based on the set of data i have for all the tickets
can you see now why i am so confused? after all that i would like to maybe use the new cfchart to present my data?
is any of this possible and could anyone give me a hand understand this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sorry done that, thanks
ASKER
<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>
<cfquery name="getavg" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">
select avg(waittime) AS wtime FROM temp WHERE waittime IS NOT NULL
</cfquery>
<cfoutput query="getavg">
#wtime#
</cfoutput>