Percent calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Percent calculation

Author

Topic

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-18 : 08:08:23

Hi
I am calculating the number of telephone calls taken by a department and the number of tickets logged by that department, the department aim is to have the same number of tickets as telephone calls , my script below give me this information from now going back 7 days, what would be really nice is to calcualte the percentage of tickets logged v the number of telephone calls, telephone calls will always be the reference point, i have spent several hours trying to work it out but i just cant see how to do it ... again any help massively appreciated :)

SELECT COUNT(ccmdb1.dbo.agent.agent_name),agent_activity_cur.date_from,
SUM(agent_activity_cur.acd_grp_conv_call_cnt) AS ACDCALLS,

(SELECT COUNT(supportdeskdev.suppdeskdev.F0004_SUPEVENT.REFERENCE)
FROM supportdeskdev.suppdeskdev.F0004_SUPEVENT

WHERE

supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE = agent_activity_cur.date_from and supportdeskdev.suppdeskdev.F0004_SUPEVENT.CUSTOMERREF <>'Premium'

GROUP BY supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE) as HOTH

FROM agent INNER JOIN agent_activity_cur
ON ccmdb1.dbo.agent.agent_id = agent_activity_cur.agent_id

where agent_activity_cur.date_from >getdate()-10 and agent_activity_cur.agent_grp_id='16'

GROUP BY datepart(day,agent_activity_cur.date_from), agent_activity_cur.date_from
HAVING (sum(acd_grp_conv_call_cnt + acd_dir_conv_call_cnt)) <>'0'
ORDER BY date_from DESC

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-18 : 14:08:53

Perhaps it would be helpful if you could come up with table(s) structure, few data rows (for every table in your above query) and the desired output format in light of those information.. as its hard to understand for me at least about your requirements from your above query

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-21 : 12:30:07

Hi MIK

here is some data and structure hope this helps

DB : Supportdeskdev table F0004_SUPEVENT

CUSTOMERREF CATTYPE CATSTATUS CATPRIORITY SUPPORTTAG OPENDATE CLOSEDATE TITLE
Alarm Supplies 942 27 0 Mchamb 04/02/2010 00:00 04/02/2010 00:00 price query
City Fire 942 27 0 Ranil 04/02/2010 00:00 04/02/2010 00:00 price query
Fire 942 27 0 Mchamb 04/02/2010 00:00 04/02/2010 00:00 price query
TF ltd 942 27 0 Ranil H 01/02/2010 00:00 01/02/2010 00:00 price query

DB : CCMDB1 table agent_activity_cur

agent_id date_from time_from time_to agent_grp_id acd_grp_conv_call_cnt
2 01/03/2004 00:00 01/01/1900 00:00 01/01/1900 00:15 2 5
2 01/03/2004 00:00 01/01/1900 00:15 01/01/1900 00:30 2 5
2 01/03/2004 00:00 01/01/1900 00:30 01/01/1900 00:45 2 0
2 01/03/2004 00:00 01/01/1900 00:45 01/01/1900 01:00 2 1
2 01/03/2004 00:00 01/01/1900 01:00 01/01/1900 01:15 2 0

DATABASE :ccmdb1 Table dbo.agent

agent_id: agent_num: agent_name: agent_grp_id: active_date: inactive_date:
66 92 AB 14 24/11/2004 00:00 01/01/2035 00:00
85 39 A Brewin 18 22/03/2006 00:00 14/01/2010 00:00
129 2 A Fels 4 26/03/2010 00:00 01/01/2035 00:00
62 12 Anelia 26 04/11/2002 00:00 01/01/2035 00:00

Desired output format would be

Date: Count of telephone calls taken : Count of helpdesk tickets logged : Percent of tickets logged v calls taken (helpdesktickets/telephone calls taken ):

Hope this makes sense ? ...

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-21 : 13:09:52

, as per given data can you provide the values for the desired output fields? And if possible also explain as how you extracted that output. this will help the folks in this forum to manipulate it for you via Transact SQL (including me - if i could)

hope you are getting what i am asking for

Cheers
MIK

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-21 : 19:56:23

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions AND formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible AND not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

you are being very rude.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-22 : 05:55:17

Hi Mik, hopefully i am understanding what you are asking for :
the sql syntax is



SELECT COUNT(ccmdb1.dbo.agent.agent_name),agent_activity_cur.date_from,
SUM(agent_activity_cur.acd_grp_conv_call_cnt) AS ACDCALLS, 


(SELECT COUNT(supportdeskdev.suppdeskdev.F0004_SUPEVENT.REFERENCE)
FROM supportdeskdev.suppdeskdev.F0004_SUPEVENT


WHERE 

supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE = agent_activity_cur.date_from and supportdeskdev.suppdeskdev.F0004_SUPEVENT.CUSTOMERREF <>'Premium'

GROUP BY supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE) as HOTH

FROM agent INNER JOIN agent_activity_cur
ON ccmdb1.dbo.agent.agent_id = agent_activity_cur.agent_id

where agent_activity_cur.date_from >getdate()-10 and agent_activity_cur.agent_grp_id='16' 



GROUP BY datepart(day,agent_activity_cur.date_from), agent_activity_cur.date_from
HAVING (sum(acd_grp_conv_call_cnt + acd_dir_conv_call_cnt)) <>'0'
ORDER BY date_from DESC

** the approach is to count the ACDCALLS - these are telephone calls , by summing the call count for each telephone agent
i obtain ACDCALLS ie the number of calls taken by agent

I then count the number of helpdesk tickets ( F0004_SUPEVENT_REFERENCE)
the WHERE statement matches up the date on when the call
was taken (date_from)and the helpdesk ticket opened ( OPENDATE),

i exclude Premium as these are calls we want to exlude

I use an inner join as i need to only view telephone calls where the agent_id belongs to agent_grp_id='16' this
allows me only to see data for a specific group of telephone operators.

Using getdate to only show me last 10 days and the Having statement to not show we where there is a zero count ( as i know this is a weekend )
**

My Output is shown below


nocolumnname   date_from                   ACDCALLS     HOTH
29            2011-02-22 00:00:00          111          109
51            2011-02-18 00:00:00          137          137
68            2011-02-17 00:00:00          114          113
83            2011-02-16 00:00:00          140          139
83            2011-02-15 00:00:00          120          119
118           2011-02-14 00:00:00          117          117

what i am hoping to achieve is an extra colum PERCENT showing the percent of ACDCALLS and HOTH


nocolumnname   date_from                   ACDCALLS     HOTH  Percent
29            2011-02-22 00:00:00          111          109   98
51            2011-02-18 00:00:00          137          137   100
68            2011-02-17 00:00:00          114          113   99
83            2011-02-16 00:00:00          140          139   99
83            2011-02-15 00:00:00          120          119   99
118           2011-02-14 00:00:00          117          117   100

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-22 : 07:59:05

Select
nocolumnname
,date_from
,ACDCALLS
,HOTH
,Cast((HOTH/cast(ACDCALLS as Numeric(6,3)))*100 as Int) as Percentage
From (
Your Select Query Here
) A

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-22 : 08:54:43

Hi MIK, really appreciate your quick reply

, as a newbie i hope i have interpreted your reply correctly , but i get the following error ...




Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'A'.


Select 
nocolumnname
,date_from
,ACDCALLS
,HOTH
,Cast((HOTH/cast(ACDCALLS as Numeric(6,3)))*100 as Int) as Percentage
From (SELECT COUNT(ccmdb1.dbo.agent.agent_name),agent_activity_cur.date_from,
SUM(agent_activity_cur.acd_grp_conv_call_cnt) AS ACDCALLS, 

(SELECT COUNT(supportdeskdev.suppdeskdev.F0004_SUPEVENT.REFERENCE)
FROM supportdeskdev.suppdeskdev.F0004_SUPEVENT

WHERE 

supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE = agent_activity_cur.date_from and supportdeskdev.suppdeskdev.F0004_SUPEVENT.CUSTOMERREF <>'Premium'

GROUP BY supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE) as HOTH

FROM agent INNER JOIN agent_activity_cur
ON ccmdb1.dbo.agent.agent_id = agent_activity_cur.agent_id

where agent_activity_cur.date_from >getdate()-10 and agent_activity_cur.agent_grp_id='16' 



GROUP BY datepart(day,agent_activity_cur.date_from), agent_activity_cur.date_from
HAVING (sum(acd_grp_conv_call_cnt + acd_dir_conv_call_cnt)) <>'0'


) A

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-22 : 12:20:13

quote:

Originally posted by emailuser

Hi MIK, really appreciate your quick reply

, as a newbie i hope i have interpreted your reply correctly , but i get the following error ...




Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'A'.


Select 
nocolumnname
,date_from
,ACDCALLS
,HOTH
,Cast((HOTH/cast(ACDCALLS as Numeric(6,3)))*100 as Int) as Percentage
From (SELECT COUNT(ccmdb1.dbo.agent.agent_name) as noColumnName,agent_activity_cur.date_from,
SUM(agent_activity_cur.acd_grp_conv_call_cnt) AS ACDCALLS, 

(SELECT COUNT(supportdeskdev.suppdeskdev.F0004_SUPEVENT.REFERENCE)
FROM supportdeskdev.suppdeskdev.F0004_SUPEVENT

WHERE 

supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE = agent_activity_cur.date_from and supportdeskdev.suppdeskdev.F0004_SUPEVENT.CUSTOMERREF <>'Premium'

GROUP BY supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE) as HOTH

FROM agent INNER JOIN agent_activity_cur
ON ccmdb1.dbo.agent.agent_id = agent_activity_cur.agent_id

where agent_activity_cur.date_from >getdate()-10 and agent_activity_cur.agent_grp_id='16' 



GROUP BY datepart(day,agent_activity_cur.date_from), agent_activity_cur.date_from
HAVING (sum(acd_grp_conv_call_cnt + acd_dir_conv_call_cnt)) <>'0'


) A

:) give a name to the highlighted in red column as required. And replace the Blued one with that name .. OR I have named it as NoColumnName in the above qouted code so if no specific name needs to be given then use the above qouted code

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-23 : 04:35:23

Hi MIK you are a star that worked a treat really appreciate you help and patience ... many many thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-23 : 04:39:26

you're welcome!

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources