Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help Grouping Records

Status
Not open for further replies.

jjohns09

Technical User
Jun 26, 2001
15
US
I am new to SQL and am hoping that someone can help.

I have a table of calls that come into our helpdesk that include the date,
time, talk_time, queue_time and destination. If the destination is 0, then
the call was abandoned before it could be handled by one of our helpdesk
people.

What my manager wants is to group these records by half hour increments such
calls between 12:00:00 AM and 12:29:59 AM would be grouped as 12:00 AM.
12:30:00 AM to 12:59:59 AM would be grouped as 12:30:00 and so on.
Additionally, each grouping would calculate Number Calls Offered, Number of
Calls Handled (Destination <> 0), Calls Abandoned (Destination = 0), Average
Handle Time on Handled Calls (Avg (Talk_Time) Where Destination <> 0)) and
so on.

Half Hour Offered Handled Abandoned Avg Handle
12:00AM 10 8 2 0:04:56
12:30AM 8 8 0 0:03:34
01:00AM 11 9 2 0:05:12
-
-
-
11:30PM 27 24 3 0:06:36

Thank you for taking the time to respond.

Glod Bless America

James

 

Here is a solution using a combination of sub query, group by, aggregate functions and the CASE statement.

/* Totals query uses the sub query as a derived table */
Select
HalfHour,
Sum(Handled) As Handled,
Sum(Abandoned) As Abandoned,
Avg(Talk_Time) As AvgHandle
From
/* the sub query - most of the work is done here */
(Select
/* Get the hour part of the date */
str(datepart(hour,time)) +
/* Determine the 1/2 hour */
Case
When datepart(minute,time) < 30 Then ':00'
Else ':30'
End As HalfHour,
/* Check if handled call */
Case
When Destination<>0 Then 1
Else 0
End As Handled,
/* Check if abandoned call */
Case
When Destination=0 Then 1
Else 0
End As Abandoned,
talk_time
From #HelpDesk) As sq
/* Group by half hour to get totals for each period */
Group By HalfHour Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Terry,

Thank you for quickly helping me with my problem. Can you recommend any good websites or books on SQL Server?

James
 
Check the FAQ area of this forum. Subject 10- SQL Server Resources has some entries that point to web sites. In particular check faq183-513 and faq183-694.

You can check these two pages at my personal website.


I gave some book recommendations in thread183-108206. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top