Hi, I have the following code but I'm getting a zero divide error due to the nulls, I've tried adding COALESCE to all different parts of the following 2 lines, but I cant seem to get it to work, can anyone help please?
> Convert(VarChar,DateAdd(MS,Sum(Case When StatusKey In ('on call', 'campaign call', 'manual dial', 'on call manual') Then StateDuration End) * 1000,0),108) as "Connected",
> FORMAT((Sum(Case When StatusKey In ('on vacation','gone home','out of the office', 'acdagentnotanswering', 'invalid status') Then Convert(Decimal(7,2),StateDuration) End)/Sum(Case When StatusKey is not null Then Convert(Decimal(7,2),Stateduration) End)),'P') as [Inactive%]
Select Convert(varchar, CAST(EndDateTimeGMT AT TIME ZONE 'UTC' AT TIME ZONE 'GMT standard time' as datetime), 103) as Date, Case When ICUserID = 'xxx' Then 'x1' Else (c.Firstname + ' ' + c.Lastname) End as "Name", Convert(VarChar,DateAdd(MS,Sum(Case When StatusKey In ('on call', 'campaign call', 'manual dial', 'on call manual') Then StateDuration End) * 1000,0),108) as "Connected", FORMAT((Sum(Case When StatusKey In ('on vacation','gone home','out of the office', 'acdagentnotanswering', 'invalid status') Then Convert(Decimal(7,2),StateDuration) End)/Sum(Case When StatusKey is not null Then Convert(Decimal(7,2),Stateduration) End)),'P') as [Inactive%] From [I3_IC].[dbo].[AgentActivityLog] b Left Outer Join I3_IC.dbo.individual c on b.userid = c.icuserid Where datediff(day,EndDateTimeGMT,getdate()) = 1 and (Company Like '%UK%' or ICUserID In ('name1', 'name2)) and ICUserID Not In ('name3', 'name4', 'name5') and c.Firstname is not null Group by Convert(varchar, CAST(EndDateTimeGMT AT TIME ZONE 'UTC' AT TIME ZONE 'GMT standard time' as datetime), 103), Case When ICUserID = 'xxx' Then 'x1' Else (c.Firstname + ' ' + c.Lastname) End Order By name
