Null Error

The following query results in the below error msg, althoug it worked just a
few days earlier and the data hasn't changed since then(notice the date
range). Can someone put me on the right track?
SELECT AVG(Arrival_to_Closed_numeric_seconds) AS 'Q115c- Average Time
Arrival to Clear'
FROM [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
WHERE (Create_time BETWEEN '10/01/04' AND '09/30/05')
Warning: Null value is eliminated by an aggregate or other SET operation.
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.Is your date type for field Arrival_to_Closed_numeric_seconds a number? You
may have nulls in that column for some of the records. You can try casting
them or set the null to zero prior to this query.
"Dave S." <davidstedman@.colliergov.net> wrote in message
news:eQPYCgnJGHA.2012@.TK2MSFTNGP14.phx.gbl...
> The following query results in the below error msg, althoug it worked just
> a
> few days earlier and the data hasn't changed since then(notice the date
> range). Can someone put me on the right track?
> SELECT AVG(Arrival_to_Closed_numeric_seconds) AS 'Q115c- Average Time
> Arrival to Clear'
> FROM [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
> WHERE (Create_time BETWEEN '10/01/04' AND '09/30/05')
>
> Warning: Null value is eliminated by an aggregate or other SET operation.
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
>|||Casting? I think I got it confuse with MySQL. I meant VAL() so you can try
SELECT AVG(val('' & Arrival_to_Closed_numeric_seconds)) AS 'Q115c-
Average Time
Arrival to Clear'
FROM [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
WHERE (Create_time BETWEEN '10/01/04' AND '09/30/05')
UNTESTED..................
"Grant" <email@.nowhere.com> wrote in message
news:uiUg%23qnJGHA.3492@.TK2MSFTNGP09.phx.gbl...
> Is your date type for field Arrival_to_Closed_numeric_seconds a number?
> You may have nulls in that column for some of the records. You can try
> casting them or set the null to zero prior to this query.
> "Dave S." <davidstedman@.colliergov.net> wrote in message
> news:eQPYCgnJGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||Don't be too certain that the data has not changed. Time stamps are not
always updated as you might expect, and Create_time does not logically
correspond to the last time a value was modified.
I would bet if you select the data you will find nulls in there...
select Arrival_to_Closed_numeric_seconds, Create_time
from [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
where Arrival_to_Closed_numeric_seconds is null
"Dave S." <davidstedman@.colliergov.net> wrote in message
news:eQPYCgnJGHA.2012@.TK2MSFTNGP14.phx.gbl...
> The following query results in the below error msg, althoug it worked just
a
> few days earlier and the data hasn't changed since then(notice the date
> range). Can someone put me on the right track?
> SELECT AVG(Arrival_to_Closed_numeric_seconds) AS 'Q115c- Average Time
> Arrival to Clear'
> FROM [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
> WHERE (Create_time BETWEEN '10/01/04' AND '09/30/05')
>
> Warning: Null value is eliminated by an aggregate or other SET operation.
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
>|||Create time corresponds to the time a 911 dispatcher took the call, so it
should not change legally. I did some experimenting and found that if I run
this on a smaller date range it works. A that point I thought same as you
that a single or small group of records was messing me up, but this turned
out to be not true since I could move the interval of 5 months or so up and
down the calendar with the same results when I should ahve been able to pin
it down...unless it takes more than one null to mess it up...will try VAL in
any case though.
ds
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23VdaqRoJGHA.1368@.TK2MSFTNGP10.phx.gbl...
> Don't be too certain that the data has not changed. Time stamps are not
> always updated as you might expect, and Create_time does not logically
> correspond to the last time a value was modified.
> I would bet if you select the data you will find nulls in there...
> select Arrival_to_Closed_numeric_seconds, Create_time
> from [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
> where Arrival_to_Closed_numeric_seconds is null
> "Dave S." <davidstedman@.colliergov.net> wrote in message
> news:eQPYCgnJGHA.2012@.TK2MSFTNGP14.phx.gbl...
just
> a
Time
operation.
>|||Is val a valid function for SQL2k? Can't find a mention in help.
"Grant" <email@.nowhere.com> wrote in message
news:eKbM8LoJGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Casting? I think I got it confuse with MySQL. I meant VAL() so you can try
> SELECT AVG(val('' & Arrival_to_Closed_numeric_seconds)) AS 'Q115c-
> Average Time
> Arrival to Clear'
> FROM [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
> WHERE (Create_time BETWEEN '10/01/04' AND '09/30/05')
> UNTESTED..................
> "Grant" <email@.nowhere.com> wrote in message
> news:uiUg%23qnJGHA.3492@.TK2MSFTNGP09.phx.gbl...
Time
operation.
>|||Dave,
I guess you are not concerned about the warning (with respect to NULLs).
The NULLs are not causing the error.
The error clearly shows that the selected data set has changed. What SQL
Server will do is add up all Arrival_to_Closed_numeric_seconds values
and finally divide this by the number of rows.
If the sum exceeds the outer limit of the data type (in this case
2,147,483,647), an overflow will occur. This is probably happening here.
It also explains why the query works if you limit the date range.
One way around this, is to case the Arrival_to_Closed_numeric_seconds
value to a different data type, such as bigint or decimal.
Gert-Jan
"Dave S." wrote:
> The following query results in the below error msg, althoug it worked just
a
> few days earlier and the data hasn't changed since then(notice the date
> range). Can someone put me on the right track?
> SELECT AVG(Arrival_to_Closed_numeric_seconds) AS 'Q115c- Average Time
> Arrival to Clear'
> FROM [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
> WHERE (Create_time BETWEEN '10/01/04' AND '09/30/05')
> Warning: Null value is eliminated by an aggregate or other SET operation.
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.|||I ran this ( looking for the sum to see what the total was and if it was
greater than 2b):
SELECT cast(sum(Create_to_closed_numeric_second
s) AS decimal(20,2))
FROM [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
WHERE (Create_time BETWEEN '10/01/04' AND '09/30/05')
and I still got this
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
I also verified by shortening a day at a time that the total sum before it
starts behaving like this is just over 40787180 ( it crashing after that and
the next day adds only 219649 more)
ds
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43DFBEBD.69510FCF@.toomuchspamalready.nl...
> Dave,
> I guess you are not concerned about the warning (with respect to NULLs).
> The NULLs are not causing the error.
> The error clearly shows that the selected data set has changed. What SQL
> Server will do is add up all Arrival_to_Closed_numeric_seconds values
> and finally divide this by the number of rows.
> If the sum exceeds the outer limit of the data type (in this case
> 2,147,483,647), an overflow will occur. This is probably happening here.
> It also explains why the query works if you limit the date range.
> One way around this, is to case the Arrival_to_Closed_numeric_seconds
> value to a different data type, such as bigint or decimal.
> Gert-Jan
>
> "Dave S." wrote:
just a
Time
operation.|||Try casting the data before you sum it, rather than after...
SELECT sum(cast(Create_to_closed_numeric_second
s AS decimal(20,2)))
FROM [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
WHERE (Create_time BETWEEN '10/01/04' AND '09/30/05')
"Dave S." <davidstedman@.colliergov.net> wrote in message
news:%236I%23Hv2JGHA.2828@.TK2MSFTNGP12.phx.gbl...
> I ran this ( looking for the sum to see what the total was and if it was
> greater than 2b):
> SELECT cast(sum(Create_to_closed_numeric_second
s) AS decimal(20,2))
> FROM [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
> WHERE (Create_time BETWEEN '10/01/04' AND '09/30/05')
> and I still got this
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
> I also verified by shortening a day at a time that the total sum before it
> starts behaving like this is just over 40787180 ( it crashing after that
and
> the next day adds only 219649 more)
> ds
>
>
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:43DFBEBD.69510FCF@.toomuchspamalready.nl...
> just a
date
> Time
> operation.
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:581752
Dave,
You need to cast the value to a bigger data type before the SUM (or
AVG). Try this:
SELECT sum(cast(Create_to_closed_numeric_second
s AS decimal(20,2)))
FROM [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
WHERE (Create_time BETWEEN '10/01/04' AND '09/30/05')
Gert-Jan
"Dave S." wrote:
> I ran this ( looking for the sum to see what the total was and if it was
> greater than 2b):
> SELECT cast(sum(Create_to_closed_numeric_second
s) AS decimal(20,2))
> FROM [27.0.3.63].UDT4.dbo.v_Incident_TimesMedical
> WHERE (Create_time BETWEEN '10/01/04' AND '09/30/05')
> and I still got this
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
> I also verified by shortening a day at a time that the total sum before it
> starts behaving like this is just over 40787180 ( it crashing after that a
nd
> the next day adds only 219649 more)
> ds
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:43DFBEBD.69510FCF@.toomuchspamalready.nl...
> just a
> Time
> operation.