[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server 2000 Query

Posted on 2009-07-02
18
Medium Priority
?
201 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

649 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