error in query

i have this query that has been working fine for days. all the sudden i get this error:

Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

how do i solve this? thank you experts.
SELECT     TOP 100 PERCENT Board_ID, DATEPART(yyyy, Run_Date) AS year, DATEPART(m, Run_Date) AS month, SUM(TotalActual) AS TotalActual, SUM(TotalTarget) 
                      AS TotalTarget
FROM         dbo.TB_Cummulative
GROUP BY DATEPART(m, Run_Date), DATEPART(yyyy, Run_Date), Board_ID
ORDER BY Board_ID

Open in new window

sassy168Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Yes, BIGINT will work as it can handle a number of that size.  If you data will hold values that high, YES!

http://msdn.microsoft.com/en-us/library/ms187745.aspx
0
 
Faiga DiegelSr Database EngineerCommented:
Is the TotalActual and TotalTarget integers?
0
 
Kevin CrossChief Technology OfficerCommented:
You might have some non-numeric values in your data.

Try this:

SELECT     TOP 100 PERCENT Board_ID, DATEPART(yyyy, Run_Date) AS year, DATEPART(m, Run_Date) AS month, SUM(CASE ISNUMERIC(TotalActual) WHEN 0 THEN 0 ELSE TotalActual END) AS TotalActual, SUM(CASE ISNUMERIC(TotalTarget) WHEN 0 THEN 0 ELSE TotalTarget END) 
                      AS TotalTarget
FROM         dbo.TB_Cummulative
GROUP BY DATEPART(m, Run_Date), DATEPART(yyyy, Run_Date), Board_ID
ORDER BY Board_ID

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
k_rasuriCommented:
It could be the problem with the incoming source data..can you show some sample of the source data
0
 
sassy168Author Commented:
yes, totalActual totalTarget are int. I ran your example through query analyzer, i  got this but still with error:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

(4 row(s) affected)



NULL      NULL      NULL      0      0
1      2008      9      1662956      8738262
1      2008      10      575717568      1152717888
2      2008      9      1590907      8472954
0
 
sassy168Author Commented:
i think i figured out why, some of the data have null.
0
 
Kevin CrossChief Technology OfficerCommented:
Seems like you have some data with NULL Board_ID and Run_Date.  I have seen where once a query gets some valid data back from a column it will assign a datatype to that column and so subsquent data is constrainted against that type therefore you may be getting message due to fact that NULL can't be converted to INT for year, month or board_id.
0
 
Kevin CrossChief Technology OfficerCommented:
Since the date is the one in formula, try this (should know data type of board_id from database):
If you don't want this to default to a date like current date, then just put the ISNULL on the outside of the DatePart formula and set value to 0.
SELECT     TOP 100 PERCENT Board_ID, DATEPART(yyyy, ISNULL(Run_Date, getdate())) AS year, DATEPART(m, ISNULL(Run_Date, getdate())) AS month, SUM(CASE ISNUMERIC(TotalActual) WHEN 0 THEN 0 ELSE TotalActual END) AS TotalActual, SUM(CASE ISNUMERIC(TotalTarget) WHEN 0 THEN 0 ELSE TotalTarget END) 
                      AS TotalTarget
FROM         dbo.TB_Cummulative
GROUP BY DATEPART(m, Run_Date), DATEPART(yyyy, Run_Date), Board_ID
ORDER BY Board_ID

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
If you still get error, try ISNULL(Board_ID, 0).
If that works, you can take off the ISNULL off the Run_Date portion and test and see.
Alternatively, since the NULL rows aren't going to add value with all the defaulting you can just do this:
SELECT     TOP 100 PERCENT Board_ID, DATEPART(yyyy, Run_Date) AS year, DATEPART(m, Run_Date) AS month, SUM(CASE ISNUMERIC(TotalActual) WHEN 0 THEN 0 ELSE TotalActual END) AS TotalActual, SUM(CASE ISNUMERIC(TotalTarget) WHEN 0 THEN 0 ELSE TotalTarget END) 
                      AS TotalTarget
FROM         dbo.TB_Cummulative
WHERE Board_ID IS NOT NULL
GROUP BY DATEPART(m, Run_Date), DATEPART(yyyy, Run_Date), Board_ID
ORDER BY Board_ID

Open in new window

0
 
sassy168Author Commented:
okay i remove the record with null values, i still get this:

Arithmetic overflow error converting expression to data type int.

is it possible that the number is too big? here is one of the bigger numbers, 1152717888



0
 
sassy168Author Commented:
i used this and it worked,

SELECT Board_ID, DATEPART(yyyy, Run_Date) AS year, DATEPART(m, Run_Date) AS month,
SUM(cast(TotalActual as bigint)) as TotalActual, sum(cast(TotalTarget as bigint)) as TotalTarget
FROM         dbo.TB_Cummulative
GROUP BY DATEPART(m, Run_Date), DATEPART(yyyy, Run_Date), Board_ID
ORDER BY Board_ID


now should i just change the data time to big int instead?
0
 
Kevin CrossChief Technology OfficerCommented:
The alternative if this is financial data is to use MONEY:
http://msdn.microsoft.com/en-us/library/ms179882.aspx

Same effect, you need a variable big enough to store the highest value.
0
 
eszaqCommented:
Try to replace in your query :
WHEN 0 THEN 0 ELSE TotalTarget END
with:
WHEN null THEN 0 ELSE TotalTarget END
0
All Courses

From novice to tech pro — start learning today.