Solved

SQL Server 2000 Query

Posted on 2009-07-02
18
191 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
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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

20 Experts available now in Live!

Get 1:1 Help Now