April 7, 2009 at 1:28 pm
I'm not exactly a newbie, but this seems like a newbie question:
this query:
select
'Systemwide',
sum(ft) as 'Full Time Operators',
sum(pt) as 'Part Time Operators',
sum(totalftes) as 'Total FTEs',
sum(vcbs) as 'VCBs',
sum(ocbs) as 'OCBs',
sum(ft_adwa) as 'FT ADWA',
sum(pt_adwa) as 'PT ADWA',
sum(totaladwa) as 'Total ADWA',
sum(totalftes)/sum(totaladwa) as 'Op Assign Ratio',
convert(char(10), weekendingdate, 101) as 'Week Ending'
from oardata
where weekendingdate = '2-28-2009'
group by weekendingdate
returns this error message:
Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting numeric to data type numeric.
totalftes and totaladwas are computed columns
totalftes = ft + pt + bdof
totalADWA = ft_adwa + pt_adwa + bdofADWA
all columns except the computed and date ones are numeric(10,0)
computeds are numeric(18,0)
This is a total of 15 rows being summed. What's the issue and how can I correct it?
April 7, 2009 at 1:34 pm
I'd recommend breaking the query down into smaller pieces and finding which one gives you the error. Just run one column at a time, comment out the rest, till you get the error. That'll at least tell you where to start.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 1:39 pm
I've already done that. The problem is in the dividing of the calculated columns.
April 7, 2009 at 1:43 pm
Can you change those to Float data type? Or expand the range for the Numeric?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply