Arithmetic Overflow Error

I'm getting an overflow error (see ERROR below) when trying to calculate
myPercent (see MY CODE below). I've tried using CAST (see MY CODE below) but
can't get a successful execution. I posted my DDL (see DDL below).
Can someone please help me get over this overflow? I've lost a day trying to
solve this data type problem. I'm sure the proper CAST or CONVERT will solve
the issue, but I'm out of luck and out-of-work until this gets fixed.
Any help would be GREATLY appreciated.
****************
ERROR: Arithmetic overflow error converting numeric to data type numeric.
****************
MY CODE:
SELECT pDate, SUM(pGross) AS SumpGross, SUM(pLoss) AS SumpLoss,
CAST(SUM(CONVERT(decimal(10, 6), p1grade)) /
SUM(CONVERT(decimal(10, 6), pgross - pLoss)) AS DECIMAL(4, 3)) AS myPercent
FROM pPercent
GROUP BY pDate
****************
DDL:
CREATE TABLE [pPercent] (
[pID] [int] IDENTITY (1, 1) NOT NULL ,
[pDate] [datetime] NULL ,
[pShift] [int] NULL ,
[pGross] [int] NULL ,
[pLoss] [int] NULL ,
[p1Grade] [int] NULL
) ON [PRIMARY]
GO
insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
('20051121', '1', '241711', '18525', '24989')
insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
('20051121', '2', '172161', '8268', '22690')
insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
('20051120', '1', '210979', '11487', '21737')decimal(10,6) means 10 digits total, 6 of which are to the right of the
decimal.
e.g., 1234.567890
which only leaves 4 to the left. most of your int values are larger than
this.
change the decimal definition to something large enough - I tend to go
with 10 to the left of the decimal - so decimal(16,6)
Scott wrote:
> I'm getting an overflow error (see ERROR below) when trying to calculate
> myPercent (see MY CODE below). I've tried using CAST (see MY CODE below) b
ut
> can't get a successful execution. I posted my DDL (see DDL below).
> Can someone please help me get over this overflow? I've lost a day trying
to
> solve this data type problem. I'm sure the proper CAST or CONVERT will sol
ve
> the issue, but I'm out of luck and out-of-work until this gets fixed.
> Any help would be GREATLY appreciated.
>
> ****************
> ERROR: Arithmetic overflow error converting numeric to data type numeric.
> ****************
> MY CODE:
> SELECT pDate, SUM(pGross) AS SumpGross, SUM(pLoss) AS SumpLoss,
> CAST(SUM(CONVERT(decimal(10, 6), p1grade)) /
> SUM(CONVERT(decimal(10, 6), pgross - pLoss)) AS DECIMAL(4, 3)) AS myPercen
t
> FROM pPercent
> GROUP BY pDate
> ****************
> DDL:
> CREATE TABLE [pPercent] (
> [pID] [int] IDENTITY (1, 1) NOT NULL ,
> [pDate] [datetime] NULL ,
> [pShift] [int] NULL ,
> [pGross] [int] NULL ,
> [pLoss] [int] NULL ,
> [p1Grade] [int] NULL
> ) ON [PRIMARY]
> GO
>
> insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
> ('20051121', '1', '241711', '18525', '24989')
> insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
> ('20051121', '2', '172161', '8268', '22690')
> insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
> ('20051120', '1', '210979', '11487', '21737')
>
>