soorraj
asked on
calculating percentage in query
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(TCD atabase1) as ACAI, sum(TCDatabase2)AS ACAIXS,
SUM(TCDatabase1+TCDatabase 2)
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
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(TCD
SUM(TCDatabase1+TCDatabase
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
ASKER
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(TCDatabas e1) as test1, sum(TCDatabase2)AS test2,
SUM(TCDatabase1+TCDatabase 2)
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
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(TCDatabas
SUM(TCDatabase1+TCDatabase
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
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:
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
ASKER
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+TCDatabase 2) OVER(partition by [name] Order by [name]) as Total_Calls
Incorrect syntax near 'order'.
it is still throwing an error on this line
SUM(TCDatabase1+TCDatabase
Incorrect syntax near 'order'.
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
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.
P.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
No problem. I am glad to what ever extent I could help.
Cheers!
P.
Open in new window