We help IT Professionals succeed at work.

SQL Server 2000 Query

AZZA-KHAMEES
AZZA-KHAMEES asked
on
Medium Priority
211 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
Comment
Watch Question

Rahul Goel ITILSenior Consultant - Deloitte
CERTIFIED EXPERT

Commented:
you can put group by and count on your query
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

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
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> 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

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

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> 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.
Ah... I see what your saying rrjegan17... Good point.
@rrjegan17........ copying my query and calling it urs.... hmmm.. ur allowed as its open source :)

Author

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
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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...
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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..

Author

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.
 
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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

Author

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
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks alot you deserve the 500 points.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Welcome and glad to help you out...

Author

Commented:
Thank you and well done

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.