I have the following code to work fine...

SELECT AWD.EmpName, IsNull(FirstMonth.Productivity,0) as
FirstMonthProductivity


Left Outer Join (
        select EmpName,
        CASE (IsNull(SUM(Time_Avail_Queries + AvailTime + AcdTime + AcwTime +
AbnTime + ringtime + othertime),0)) When 0 Then 0 Else
(((IsNull(Sum(Calls_Answered),0)/Avg(OC5CPHTarget))+(IsNull(Sum(WorkOutPut)+0.0,0)/60/60))/(SUM(0.0
+ Time_Avail_Queries + AvailTime + AcdTime + AcwTime + AbnTime +
ringtime + othertime)/60/60)*100) End as Productivity
        from AgentWorkDetail
        Where Timesheetdate between
        Case When month(current_timestamp-1)=8 Then '20060801' End
        and
        Case When month(current_timestamp)=8 Then '20060831' End
        group by EmpName
        ) as FirstMonth on Firstmonth.EmpName = AWD.EmpName


Where AWD.empname = 'Joe Bloggs'
group by AWD.EmpName, FirstMonth.Productivity

but i am trying to add the round function as i require the figure to be
rounded to 2dp.

If i add the Round function here i get an error...

This is the code with the round function...

select EmpName,
CASE (IsNull(Round(SUM(Time_Avail_Queries + AvailTime + AcdTime +
AcwTime + AbnTime + ringtime + othertime),0)) When 0 Then 0 Else
(((IsNull(Sum(Calls_Answered),0)/Avg(OC5CPHTarget))+(IsNull(Sum(WorkOutPut)+0.0,0)/60/60))/(SUM(0.0
+ Time_Avail_Queries + AvailTime + AcdTime + AcwTime + AbnTime +
ringtime + othertime)/60/60)*100),2) End as Productivity
from AgentWorkDetail

This is the error...

Server: Msg 174, Level 15, State 1, Line 10
The isnull function requires 2 arguments.

I must be placing the round function in the wrong place but cannot seem
to get this right.

Can anyone help?

Watch your parenteses:

                                                            --> <--

ML

---
http://milambda.blogspot.com/

....I forgot to mention that there's also no second parameter for the ROUND
function.

This might be better:
CASE (IsNull(Round(SUM(Time_Avail_Queries + AvailTime + AcdTime + AcwTime +
AbnTime + ringtime + othertime), 2), 0))



ML

---
http://milambda.blogspot.com/

Seems that a second ROUND( is required for the ELSE part.

CASE (IsNull(Round(SUM(Time_Avail_Queries + AvailTime + AcdTime +
AcwTime + AbnTime + ringtime + othertime),0)) When 0 Then 0 Else
ROUND(
(((IsNull(Sum(Calls_Answered),0)/Avg(OC5CPHTarget))+(IsNull(Sum(WorkOutPut)+
0.0,0)/60/60))/(SUM(0.0
+ Time_Avail_Queries + AvailTime + AcdTime + AcwTime + AbnTime +
ringtime + othertime)/60/60)*100),2) End as Productivity
from AgentWorkDetail


(((IsNull(Sum(Calls_Answered),0)/Avg(OC5CPHTarget))+(IsNull(Sum(WorkOutPut)+
0.0,0)/60/60))/(SUM(0.0
Show quoteHide quote(((IsNull(Sum(Calls_Answered),0)/Avg(OC5CPHTarget))+(IsNull(Sum(WorkOutPut)+
0.0,0)/60/60))/(SUM(0.0
Show quoteHide quote

Many Thanks


Regards
Batty

SET/SELECT truncate string
parse out set of numbers from a column
stored procedure stopped working
How is the design for this complex requirement ?
trying to use a cursor to update table..