SQL Server 2000 Query

Dear all,

I want to construct SQL 2000 query to create new Table from a view to calculate the no of closed, opened, cancelled, and escalated calls per analyst on particular year say 2009.

The helpdesk view vw_helpdesk contains the following fields :

1- AnalystName (nvarchar)
2- CallAssignedDate (datetime)
3-CallClosedDate (datetime)
4-CallStatus (closed,Opened, Escalated,canceled) (nvarchar)

I want  to create new table called CallsStat with the following fields

1- AnalystName
2- NoOfClosedCalls
3-NoOfOpenedCalls
4-NoOfEscalatedCalls
5-NoOfCanceledCalls
6-AvgResolutionTime (Hours)

Regards
LVL 1
AZZA-KHAMEESAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rahul Goel ITILSenior Consultant - DeloitteCommented:
you can put group by and count on your query
0
StuartMc77Commented:
Try this:
SELECT
  AnalystName,
  CallStatus,
  COUNT(callstatus)
FROM vw_helpdesk
WHERE
  CallAssignedDate BETWEEN '2009-01-01' AND '2010-01-01'
GROUP BY
  AnalystName,
  CallStatus
ORDER BY
  AnalystName

Open in new window

0
sunil_mailsCommented:
U may require small fine tuuning in avg calculation as it will count non-working hours as well.
Logiic and technique is as below

select AnalystName, sum(case CallStatus when 'closed' then 1 else 0 end) ClosedCalls
,sum(case CallStatus when 'Opened' then 1 else 0 end) OpenedCalls
,sum(case CallStatus when 'Escalated' then 1 else 0 end) EscalatedCalls
,sum(case CallStatus when 'canceled' then 1 else 0 end) canceledCalls
, sum(case CallStatus when 'closed' then datediff('H',CallClosedDate,CallAssignedDate)  end)
/ sum(case CallStatus when 'closed' then 1 else 0 end) as AvgResolutionTime
from  vw_helpdesk
group by AnalystName
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I want  to create new table called CallsStat with the following fields

You wont be able to create a new table from an existing view vw_helpdesk.
Anyhow you can create another view on top of it like this to achieve your objective:

Hope this helps
create view new_view as 
SELECT AnalystName, sum( CASE WHEN CallStatus = 'Closed' THEN 1 ELSE 0 END) NoOfClosedCalls,
sum( CASE WHEN CallStatus = 'Opened' THEN 1 ELSE 0 END) NoOfOpenedCalls,
sum( CASE WHEN CallStatus = 'Escalated' THEN 1 ELSE 0 END) NoOfEscalatedCalls,
sum( CASE WHEN CallStatus = 'Cancelled' THEN 1 ELSE 0 END) NoOfCanceledCalls,
avg(ResolutionTime) AvgResolutionTime
FROM ( SELECT AnalystName,datediff( hh, CallAssignedDate, CallClosedDate) ResolutionTime
       FROM  vw_helpdesk) temp
GROUP BY AnalystName
 
GO

Open in new window

0
StuartMc77Commented:
I don't understand why you would use the SUM function when SQL is already capable of counting... that is a very complicated and processing heavy method of doing what is needed... though the create view part is valid so I would try:
CREATE VIEW new_view AS 
SELECT
  AnalystName,
  CallStatus,
  COUNT(callstatus)
FROM vw_helpdesk
WHERE
  CallAssignedDate BETWEEN '2009-01-01' AND '2010-01-01'
GROUP BY
  AnalystName,
  CallStatus
ORDER BY
  AnalystName

Open in new window

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I don't understand why you would use the SUM function when SQL is already capable of counting.

StuartMc77.. The asker requires the resultset for a single AnalystName in a single row and not in Several Rows.
Kindly note that your query will provide 4 rows whereas my Query will return it in a single record as asked.

And I believe I am following requirements without complicating it. Kindly revert if I am wrong.

AZZA-KHAMEES,
    Kindly include WHERE CallAssignedDate BETWEEN '2009-01-01' AND '2010-01-01'
in the Inner query as I have missed that one earlier.
0
StuartMc77Commented:
Ah... I see what your saying rrjegan17... Good point.
0
sunil_mailsCommented:
@rrjegan17........ copying my query and calling it urs.... hmmm.. ur allowed as its open source :)
0
AZZA-KHAMEESAuthor Commented:
Dear rrjegan17,
I tried your code and got the following errors :
Server: Msg 207, Level 16, State 3, Procedure new_view, Line 2
Invalid column name 'CallStatus'.
Server: Msg 207, Level 16, State 1, Procedure new_view, Line 2
Invalid column name 'CallStatus'.
Server: Msg 207, Level 16, State 1, Procedure new_view, Line 2
Invalid column name 'CallStatus'.
Server: Msg 207, Level 16, State 1, Procedure new_view, Line 2
Invalid column name  'CallStatus'.
 
Any hints ?
 
Thanks
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
AZZA-KHAMEES,
    As per your stating, CallStatus is a column available in vw_helpdesk right.
If not kindly change that one to make it run...
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
sunil_mails,
   
>> copying my query and calling it urs

LOL...
Kindly check out the times when we posted the messages.
Based upon the coding constructs any one may be easily able to identify whether it is a copied one or created one..
0
AZZA-KHAMEESAuthor Commented:
Dear rrjegan17:
When I removed the sql code from line 6 till line 8 and replaced it with FROM vm_helpdesk, the query works without errors. However, I need to calculate the average Call Resolution time per Analyst.
 
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Checked it and its my mistake:

This should help you out.
create view new_view as 
SELECT AnalystName, sum( CASE WHEN CallStatus = 'Closed' THEN 1 ELSE 0 END) NoOfClosedCalls,
sum( CASE WHEN CallStatus = 'Opened' THEN 1 ELSE 0 END) NoOfOpenedCalls,
sum( CASE WHEN CallStatus = 'Escalated' THEN 1 ELSE 0 END) NoOfEscalatedCalls,
sum( CASE WHEN CallStatus = 'Cancelled' THEN 1 ELSE 0 END) NoOfCanceledCalls,
avg(ResolutionTime) AvgResolutionTime
FROM ( SELECT AnalystName,CallStatus,datediff( hh, CallAssignedDate, CallClosedDate) ResolutionTime
       FROM  vw_helpdesk) temp
GROUP BY AnalystName
 
GO

Open in new window

0
AZZA-KHAMEESAuthor Commented:
Thanks rrjegan17 this worked for me. Where do I insert the condition for the year 2009 'WHERE CallAssignedDate BETWEEN '2009-01-01' AND '2010-01-01' and can I create a table instead of view ?
regards
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
First one to Create a view
Second one to create a table or insert records into a table.

Hope this helps
-- Create View
 
create view new_view as 
SELECT AnalystName, sum( CASE WHEN CallStatus = 'Closed' THEN 1 ELSE 0 END) NoOfClosedCalls,
sum( CASE WHEN CallStatus = 'Opened' THEN 1 ELSE 0 END) NoOfOpenedCalls,
sum( CASE WHEN CallStatus = 'Escalated' THEN 1 ELSE 0 END) NoOfEscalatedCalls,
sum( CASE WHEN CallStatus = 'Cancelled' THEN 1 ELSE 0 END) NoOfCanceledCalls,
avg(ResolutionTime) AvgResolutionTime
FROM ( SELECT AnalystName,CallStatus,datediff( hh, CallAssignedDate, CallClosedDate) ResolutionTime
       FROM  vw_helpdesk
       WHERE CallAssignedDate BETWEEN '2009-01-01' AND '2010-01-01') temp
GROUP BY AnalystName
GO
 
-- Insert records into a table
-- Replace temp with the required table name
 
SELECT AnalystName, sum( CASE WHEN CallStatus = 'Closed' THEN 1 ELSE 0 END) NoOfClosedCalls,
sum( CASE WHEN CallStatus = 'Opened' THEN 1 ELSE 0 END) NoOfOpenedCalls,
sum( CASE WHEN CallStatus = 'Escalated' THEN 1 ELSE 0 END) NoOfEscalatedCalls,
sum( CASE WHEN CallStatus = 'Cancelled' THEN 1 ELSE 0 END) NoOfCanceledCalls,
avg(ResolutionTime) AvgResolutionTime
INTO temp
FROM ( SELECT AnalystName,CallStatus,datediff( hh, CallAssignedDate, CallClosedDate) ResolutionTime
       FROM  vw_helpdesk
       WHERE CallAssignedDate BETWEEN '2009-01-01' AND '2010-01-01') temp
GROUP BY AnalystName

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AZZA-KHAMEESAuthor Commented:
Thanks alot you deserve the 500 points.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome and glad to help you out...
0
AZZA-KHAMEESAuthor Commented:
Thank you and well done

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.