Solved

calculating percentage in query

Posted on 2009-07-06
10
333 Views
Last Modified: 2012-06-27
i am trying to calculate dispositions for certain agents in the table. I am trying to calculate the percentage of SAVES(disposition) for an agent by total number of calls taken by him.  I have many databases. i am union the databases. For right now i am using only 2. There are many different kinds of dispositions. like cancel, wrong number etc. I am looking for particular disposition
This is how the data should looks like.

agnetname   disposition    database1         database2         TotalCalls     percentofcalls
bill                    save               1                             2                     5                      60%
Joe                   save               0                            5                     10                     50%
Mike                  save               2                           5                      20                     35%

This is kind of sql i have.  I need to look for a particular disp and then calculate the percentage.

declare @StartDate datetime
declare @EndDate datetime
set @StartDate='4/15/2009'
set @EndDate='6/20/2009'

;with rawdata as (

SELECT LocalUserId as [name],disposition,
COUNT(*) AS TCDatabase1, 0 as TCDatabase2
FROM
test1.dbo.tblCallReceived
WHERE
CallDate BETWEEN @StartDate AND @EndDate

GROUP BY Disposition, LocalUserId
-----------------------------------------------------------------------------------------------------------
union all

SELECT LocalUserId as [name],disposition,
0 AS TCDatabase1,count(*) as TCDatabase2
FROM
test2.dbo.tblCallReceived
WHERE
CallDate BETWEEN @StartDate AND @EndDate
GROUP BY Disposition, LocalUserId
), totals as
select [name],disposition,sum(TCDatabase1) as ACAI, sum(TCDatabase2)AS ACAIXS,

SUM(TCDatabase1+TCDatabase2)

as Total_Calls
from rawdata
GROUP BY Disposition, name
)
SELECT *
, Total_Calls * 100.0 / (SELECT SUM(Total_Calls) FROM totals) AS PercentOfCalls
FROM totals
ORDER BY name, Disposition

                     
0
Comment
Question by:soorraj
  • 6
  • 4
10 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24787902
Try this:
declare @StartDate datetime

declare @EndDate datetime

set @StartDate='4/15/2009'

set @EndDate='6/20/2009'
 

;With rawdata as (
 

SELECT LocalUserId as [name],disposition, COUNT(*) AS TCDatabase1, 0 as TCDatabase2

FROM test1.dbo.tblCallReceived

WHERE CallDate BETWEEN @StartDate AND @EndDate

GROUP BY Disposition, LocalUserId
 

UNION ALL
 

SELECT LocalUserId as [name],disposition,0 AS TCDatabase1,count(*) as TCDatabase2

FROM test2.dbo.tblCallReceived

WHERE CallDate BETWEEN @StartDate AND @EndDate

GROUP BY Disposition, LocalUserId

), Totals as 

Select	LocalUserID, 

		[name], 

		disposition, 

		SUM(TCDatabase1) as ACAI, 

		SUM(TCDatabase2)AS ACAIXS,

		SUM(TCDatabase1+TCDatabase2) as DispTotal, 

		SUM(TCDatabase1+TCDatabase2) OVER(partition by LocalUserID Order by LocalUserID)  as Total_Calls

From	rawdata   

GROUP BY LocalUserID, Disposition, name

Order by LocalUserID
 
 

SELECT *, DispTotal * 100.0 / Total_Calls AS PercentOfCalls 

FROM totals

ORDER BY name, Disposition

Open in new window

0
 
LVL 1

Author Comment

by:soorraj
ID: 24788741
WIth you query i am geting an error
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near 'order'.

I also did it but i am getting two rows for agent for two different databases.

This is how i did it

;with rawdata as (

SELECT LocalUserId as [name],
sum(case disposition when 'save' then 1 else 0 end) as saves,
COUNT(*) AS TCDatabase1, 0 as TCDatabase2
FROM
test1.dbo.tblCallReceived
WHERE
CallDate BETWEEN '5/15/2009' AND '7/16/2009' AND (callid is NULL OR len(callid) > 2)
GROUP BY LocalUserId

-----------------------------------------------------------------------------------------------------------
union all
-----------------------------------------------------------------------------------------------------------

SELECT LocalUserId as [name],sum(case disposition when 'save' then 1 else 0 end) as saves,
0 AS TCDatabase1,count(*) as TCDatabase2
FROM
test2.dbo.tblCallReceived
WHERE
CallDate BETWEEN '5/15/2009' AND '7/16/2009' AND (callid is NULL OR len(callid) > 2)
GROUP BY  LocalUserId
),

totals as (
select [name],saves,sum(TCDatabase1) as test1, sum(TCDatabase2)AS test2,

SUM(TCDatabase1+TCDatabase2)

as Total_Calls
from rawdata
GROUP BY [name],saves
)
SELECT *
, saves * 100.0 / Total_Calls AS PercentOfCalls
FROM totals
ORDER BY name

The data is showing up like this
name                saves               test1                   test2                 totacalls               percentofcalls
jon                         2                    0                        2                             2                               100
jon                          3                    5                       0                             5                                60%

but i want data to show up like this

name                saves               test1                   test2                 totacalls               percentofcalls
jon                         5                   5                          2                           7                             71.42



0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24788906
Okay, I am little confused. Don't you have more then one disposition? If yes, then are you not calculating for all dispositions or just for save as you posted above.
In your above code do not group by column "saves" and you will get one record for each user.
Also, try this, I was missing brackets:

declare @StartDate datetime

declare @EndDate datetime

set @StartDate='4/15/2009'

set @EndDate='6/20/2009'

 

;With rawdata as (

 

SELECT LocalUserId as [name],disposition, COUNT(*) AS TCDatabase1, 0 as TCDatabase2

FROM test1.dbo.tblCallReceived

WHERE CallDate BETWEEN @StartDate AND @EndDate

GROUP BY Disposition, LocalUserId

 

UNION ALL

 

SELECT LocalUserId as [name],disposition,0 AS TCDatabase1,count(*) as TCDatabase2

FROM test2.dbo.tblCallReceived

WHERE CallDate BETWEEN @StartDate AND @EndDate

GROUP BY Disposition, LocalUserId

), Totals as (

Select	[name], 

		disposition, 

		SUM(TCDatabase1) as ACAI, 

		SUM(TCDatabase2)AS ACAIXS,

		SUM(TCDatabase1+TCDatabase2) as DispTotal, 

		SUM(TCDatabase1+TCDatabase2) OVER(partition by [name] Order by [name])  as Total_Calls

From	rawdata   

GROUP BY Name, Disposition

Order by name, 

) 

-- For each disposition 

SELECT *, DispTotal * 100.0 / Total_Calls AS PercentOfCalls 

FROM totals

ORDER BY name, Disposition

Open in new window

0
 
LVL 1

Author Comment

by:soorraj
ID: 24789406
i have never used over Partition . just curious how will i use SAVE disp in your query.
it is still throwing an error on this line

  SUM(TCDatabase1+TCDatabase2) OVER(partition by [name] Order by [name])  as Total_Calls

Incorrect syntax near 'order'.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24789447
Sorry yea, have to take out the order by clause:

declare @StartDate datetime

declare @EndDate datetime

set @StartDate='4/15/2009'

set @EndDate='6/20/2009'

 

;With rawdata as (

 

SELECT LocalUserId as [name],disposition, COUNT(*) AS TCDatabase1, 0 as TCDatabase2

FROM test1.dbo.tblCallReceived

WHERE CallDate BETWEEN @StartDate AND @EndDate

GROUP BY Disposition, LocalUserId

 

UNION ALL

 

SELECT LocalUserId as [name],disposition,0 AS TCDatabase1,count(*) as TCDatabase2

FROM test2.dbo.tblCallReceived

WHERE CallDate BETWEEN @StartDate AND @EndDate

GROUP BY Disposition, LocalUserId

), Totals as (

Select	[name], 

		disposition, 

		SUM(TCDatabase1) as ACAI, 

		SUM(TCDatabase2)AS ACAIXS,

		SUM(TCDatabase1+TCDatabase2) as DispTotal, 

		SUM(TCDatabase1+TCDatabase2) OVER(partition by [name])  as Total_Calls

From	rawdata   

GROUP BY Name, Disposition

Order by name, 

) 

-- For each disposition 

SELECT *, DispTotal * 100.0 / Total_Calls AS PercentOfCalls 

FROM totals

ORDER BY name, Disposition

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Expert Comment

by:pssandhu
ID: 24789456
Please run to see if those are the results you are looking for. If not, then please post back how they are and how you want them to be based on the result set you get from the above query. Hopefully there should be no more errors.
P.
0
 
LVL 1

Author Comment

by:soorraj
ID: 24789626
Well there are still errors
It gives this error  on this line Order by name
the ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

If i comment it out then it gives
Msg 8120, Level 16, State 1, Line 6
Column 'rawdata.TCDatabase1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 6
Column 'rawdata.TCDatabase2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

When i add the TCDatabase1,TCDatabase2 in the group by clause it works but duplicate data.
 It should look like this. i am looking for the disposition SAVE. This needs to be in the query

name                saves               test1                   test2                 totacalls               percentofcalls
jon                         5                   5                          2                           7                             71.42
Luke                      7                   2                           10                       12                              58.33

0
 
LVL 17

Accepted Solution

by:
pssandhu earned 400 total points
ID: 24789943
Alright, I have looked at the errors and have tried to clean up the syntax, please see if this runs okay and you get your desired results:
DECLARE	@StartDate datetime,

		@EndDate datetime
 

SET @StartDate	= '4/15/2009'

SET @EndDate	= '6/20/2009'
 

;With RawData as (
 

SELECT LocalUserId as UserName,Disposition, COUNT(*) AS TCDatabase1, 0 as TCDatabase2

FROM test1.dbo.tblCallReceived

WHERE CallDate BETWEEN @StartDate AND @EndDate

GROUP BY Disposition, LocalUserId
 

UNION ALL
 

SELECT LocalUserId as UserName,Disposition,0 AS TCDatabase1,count(*) as TCDatabase2

FROM test2.dbo.tblCallReceived

WHERE CallDate BETWEEN @StartDate AND @EndDate

GROUP BY Disposition, LocalUserId

), Totals as ( 

Select	UserName, 

		Disposition, 

		TCDatabase1, 

		TCDatabase2,

		TCDatabase1+TCDatabase2 as Disp, 

		SUM(TCDatabase1+TCDatabase2) OVER(partition by UserName)  as Total_Calls

From	RawData

), GrandTotal as (

Select	UserName, 

		Disposition, 

		SUM(TCDatabase1) as ACAI, 

		SUM(TCDatabase2) as ACAIXS,

		SUM(Disp) as DispTotal, 

		MAX(Total_Calls) as Ttl_Calls

From	Totals

Group by UserName, Disposition

)
 

SELECT	*, (DispTotal*100)/Ttl_Calls AS PercentOfCalls 

FROM	GrandTotal

ORDER BY UserName, Disposition

Open in new window

0
 
LVL 1

Author Comment

by:soorraj
ID: 24795934
Well it i still not giving the right results. I played around with your sql and i then i figured it out. Thanks for your help
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24795998
Sometimes it is nice to have an example dataset to work to get exact results, mostly when the query is a little complicated. But on the other hand figuring it out yourself is always and good learning curve.
No problem. I am glad to what ever extent I could help.
Cheers!
P.
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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

18 Experts available now in Live!

Get 1:1 Help Now