sql exception arithmetic overflow?
- by MyHeadHurts
In my program the user imports a date and it works whenever the year is in 2011 but if i try a date in 2010 i get this error which is weird
[
SqlException (0x80131904): Arithmetic overflow error converting int to data type numeric.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.HasMoreRows() +157
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +197
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +78
System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +164
System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +282
System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +19
System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) +222
System.Data.DataTable.Load(IDataReader reader) +14
(
@YearToGet int,
@current datetime,
@y int,
@search datetime
)
AS
SET @YearToGet = 2006;
WITH Years AS (
SELECT DATEPART(year, GETDATE()) [Year]
UNION ALL
SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet
),
q_00 as (
select
DIVISION
, DYYYY
, sum(PARTY) as asofPAX
, sum(InsAmount) as asofSales
from dbo.B101BookingsDetails
INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year
where Booked <= CONVERT(int, DateAdd(year, (Years.Year - @y), @search))
and DYYYY = Years.Year
group by DIVISION, DYYYY, years.year
having DYYYY = years.year
),
q_01 as (
select
DIVISION
, DYYYY
, sum(PARTY) as YEPAX
, sum(InsAmount) as YESales
from dbo.B101BookingsDetails
INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year
group by DIVISION, DYYYY , years.year
having DYYYY = years.year
),
q_02 as (
select
DIVISION
, DYYYY
, sum(PARTY) as CurrentPAX
, sum(InsAmount) as CurrentSales
from dbo.B101BookingsDetails
INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year
where Booked <= CONVERT(int,@current)
and DYYYY = (year( getdate() ))
group by DIVISION, DYYYY
)
select
a.DIVISION
, a.DYYYY
, asofPAX
, asofSales
, YEPAX
, YESales
, CurrentPAX
, CurrentSales
,asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales,
CAST((asofpax) AS DECIMAL(5,1))/yepax as percentpax
from q_00 as a
join q_01 as b on (b.DIVISION = a.DIVISION and b.DYYYY = a.DYYYY)
join q_02 as c on (b.DIVISION = c.DIVISION)
JOIN Years as d on (b.dyyyy = d.year)
where A.DYYYY <> (year( getdate() ))
order by a.DIVISION, a.DYYYY ;
