Solved

my average waiting time question?

Posted on 2006-11-13
3
264 Views
Last Modified: 2013-12-24
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?


0
Comment
Question by:pigmentarts
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
danrosenthal earned 500 total points
Comment Utility
Here is an idea...

select t.ticketID
      , MIN(t.[datetime]) as firstdate
      ,(
            select TOP 1 [datetime]
            FROM ticket
            WHERE ticketID = t.ticketID
            AND [datetime] <> MIN(t.[datetime])
            ORDER BY [datetime]
      ) AS seconddate
      ,
CAST(
      (
            (
                  select TOP 1 [datetime]
                  FROM ticket
                  WHERE ticketID = t.ticketID
                  AND [datetime] <> MIN(t.[datetime])
                  ORDER BY [datetime]
            )
            - MIN(t.[datetime])
      )
      AS float
) AS waittime
INTO #temp
from ticket t
GROUP BY t.ticketID

select avg(waittime) FROM #temp





0
 
LVL 12

Author Comment

by:pigmentarts
Comment Utility
cool, i used you example above, just one last question, possible to exstend this a bit and only output the avg for this month i.e. only show the waiting time for the current month?

<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>
0
 
LVL 12

Author Comment

by:pigmentarts
Comment Utility
sorry done that, thanks
0

Featured Post

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

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Remove Index.php in Codeigniter 12 52
Problem to Eclipse 16 99
SCCM vs Windows server 5 70
AWS New EC2 Instance and EBS Storage 2 60
This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

6 Experts available now in Live!

Get 1:1 Help Now