Solved

SQL Server 2000 Query

Posted on 2009-07-02
18
197 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:AZZA-KHAMEES
  • 7
  • 5
  • 3
  • +2
18 Comments
 
LVL 9

Expert Comment

by:Rahul Goel ITIL
ID: 24762094
you can put group by and count on your query
0
 
LVL 1

Expert Comment

by:StuartMc77
ID: 24762161
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
 
LVL 2

Expert Comment

by:sunil_mails
ID: 24762174
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24762186
>> 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
 
LVL 1

Expert Comment

by:StuartMc77
ID: 24762260
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24762289
>> 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
 
LVL 1

Expert Comment

by:StuartMc77
ID: 24762402
Ah... I see what your saying rrjegan17... Good point.
0
 
LVL 2

Expert Comment

by:sunil_mails
ID: 24762655
@rrjegan17........ copying my query and calling it urs.... hmmm.. ur allowed as its open source :)
0
 

Author Comment

by:AZZA-KHAMEES
ID: 24762690
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24762755
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24762787
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
 

Author Comment

by:AZZA-KHAMEES
ID: 24762953
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24762985
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
 

Author Comment

by:AZZA-KHAMEES
ID: 24763142
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
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24763407
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
 

Author Closing Comment

by:AZZA-KHAMEES
ID: 31599129
Thanks alot you deserve the 500 points.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24764168
Welcome and glad to help you out...
0
 

Author Comment

by:AZZA-KHAMEES
ID: 24764430
Thank you and well done

0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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