Solved

SQL Server 2000 Query

Posted on 2009-07-02
18
195 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

813 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

13 Experts available now in Live!

Get 1:1 Help Now