SQLTeam.com | Weblogs | Forums

RESOLVED - Where to add the COALESCE in my code

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

Resolved

FORMAT((Coalesce(Sum(Case When StatusKey In ('on vacation','gone home','out of the office', 'acdagentnotanswering', 'invalid status') Then Convert(Decimal(7,2),StateDuration) End)/NULLIF(Sum(Case When StatusKey is not null Then Convert(Decimal(7,2),Stateduration) End),0),0)),'P') as [Inactive%]