divide by zero error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

divide by zero error

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2014-04-30 : 00:57:18

Hi guys,

I cant work out why I would get a divide by zero error here.

declare @UserID varchar(255)
declare @FromDate datetime
declare @toDate datetime

select @UserID='dimbrian'
select @FromDate='19 april 2014'
select @toDate='26 april 2014'

select 4 / cast(COUNT(distinct [agent_login_id])AS decimal(10,2)) as TT_SiteAvg
from tt left join Agents on Responder=agent_login_id WHERE location = (Select distinct location from Agents where agent_login_id = @UserID)
and [Correspondence_date] between @FromDate AND @toDate

This only happens if I include the last line:

and [Correspondence_date] between @FromDate AND @toDate

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-30 : 01:02:59

it is because between that date range, there isn't any record

run this and see


select distinct [agent_login_id]
from tt left join Agents on Responder=agent_login_id WHERE location = (Select distinct location from Agents where agent_login_id = @UserID)
and [Correspondence_date] between @FromDate AND @toDate

KH
[spoiler]Time is always against us[/spoiler]

2revup
Posting Yak Master

112 Posts

Posted - 2014-04-30 : 01:35:23

Your quite right... Thanks for this I now found this issue because of this - is data related.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources