August 22, 2008 at 10:15 pm
How do you fix the sql divide statements so that they work in the first select statement:
The statements have the problems are the following:
a. cast(PreviousMonth AS numeric(8,6)) / CAST(PREVIOUSMONTHTOT as numeric(8,6)) as PREVIOUSMONTHPCT
b. DIFPCT= cast(PREVIOUSMONTHTOT as decimal(6,4)) - cast(CURRENTMONTHTOT as decimal(6,4))/cast(PREVIOUSMONTHTOT as decimal(6,4))
The sql in question is listed below:
SELECT REGION,
TYPE,
case when B.PREVIOUSMONTH is null then 0 else B.PREVIOUSMONTH end as PreviousMonth,
cast(PreviousMonth AS numeric(8,6)) / CAST(PREVIOUSMONTHTOT as numeric(8,6)) as PREVIOUSMONTHPCT,
case when a.CURRENTMONTH is null then 0 else A.CURRENTMONTH end as CURRENTMONTH,
cast(CURRENTMONTH as numeric(6,4)) / cast(CURRENTMONTHTOT as numeric(6,4)) AS CURRENTMONTHPCT,
cast(CURRENTMONTH as integer) - cast(PREVIOUSMONTH as integer) as differenttot,
DIFPCT= cast(PREVIOUSMONTHTOT as decimal(6,4)) - cast(CURRENTMONTHTOT as decimal(6,4))/cast(PREVIOUSMONTHTOT as decimal(6,4)),
case when PREVIOUSMONTHTOT is null then 0 else PREVIOUSMONTHTOT end as PREVIOUSMONTHTOTAL,
case when CURRENTMONTHTOT is null then 0 else CURRENTMONTHTOT end as CURRENTMONTHTOTAL
FROM (select distinct Region,Type
From dbo.rptMonthly
Where region between 10 and 50
) RDM
LEFT JOIN
(SELECT rptMonthly.region,type,
Count(rptMonthly.UniqueID) as PREVIOUSMONTH
FROM dbo.rptMonthly
WHERE (rptMonthly.complete_date>=case when datepart(month,getdate()) <> 1 Then ltrim(rtrim(str(month(dateadd(month,-2,getdate()))))) + '/01/' + ltrim(rtrim(str(year(getdate()))))
else ltrim(rtrim(str(month(dateadd(month,-2,getdate()))))) + '/01/' + ltrim(rtrim(str(year(dateadd(year,-1,getdate()))))) end
And rptMonthly.complete_date 1 Then ltrim(rtrim(str(month(dateadd(month,-1,getdate()))))) + '/01/' + ltrim(rtrim(str(year(getdate()))))
else ltrim(rtrim(str(month(dateadd(month,-1,getdate()))))) + '/01/' + ltrim(rtrim(str(year(dateadd(year,-1,getdate()))))) end )
GROUP BY rptMonthly.region, type
) B
On RDM.Region = B.Region
and RDM.Type = B.Type
LEFT JOIN
(SELECT rptMonthly.region,
count(rptMonthly.UniqueID) as PREVIOUSMONTHTOT
FROM dbo.rptMonthly
WHERE rptMonthly.complete_date>=case when datepart(month,getdate()) <> 1 Then ltrim(rtrim(str(month(dateadd(month,-2,getdate()))))) + '/01/' + ltrim(rtrim(str(year(getdate()))))
else ltrim(rtrim(str(month(dateadd(month,-2,getdate()))))) + '/01/' + ltrim(rtrim(str(year(dateadd(year,-1,getdate()))))) end
And rptMonthly.complete_date 1 Then ltrim(rtrim(str(month(dateadd(month,-1,getdate()))))) + '/01/' + ltrim(rtrim(str(year(getdate()))))
else ltrim(rtrim(str(month(dateadd(month,-1,getdate()))))) + '/01/' + ltrim(rtrim(str(year(dateadd(year,-1,getdate()))))) end )
GROUP BY rptDataMonthly.region
) B1
on RDM.Region = B1.Region
Left Join
(SELECT rptMonthly.region,type,
Count(rptDataMonthly.UniqueID) as CURRENTMONTH
FROM dbo.rptMonthly
WHERE (rptMonthly.complete_date>=case when datepart(month,getdate()) <> 1 Then ltrim(rtrim(str(month(dateadd(month,-1,getdate()))))) + '/01/' + ltrim(rtrim(str(year(getdate()))))
else ltrim(rtrim(str(month(dateadd(month,-1,getdate()))))) + '/01/' + ltrim(rtrim(str(year(dateadd(year,-1,getdate()))))) end
And rptMonthly.complete_date 1 Then ltrim(rtrim(str(month(getdate())))) + '/01/' + ltrim(rtrim(str(year(getdate()))))
else ltrim(rtrim(str(month(getdate())))) + '/01/' + ltrim(rtrim(str(year(getdate())))) end )
GROUP BY rptMonthly.region,type
) A
On RDM.Region = A.Region
and RDM.Type = A.Type
Left Join
(SELECT rptMonthly.region,
Count(rptMonthly.UniqueID) as CURRENTMONTHTOT
FROM dbo.rptMonthly
WHERE rptMonthly.complete_date>=case when datepart(month,getdate()) <> 1 Then ltrim(rtrim(str(month(dateadd(month,-1,getdate()))))) + '/01/' + ltrim(rtrim(str(year(getdate()))))
else ltrim(rtrim(str(month(dateadd(month,-1,getdate()))))) + '/01/' + ltrim(rtrim(str(year(dateadd(year,-1,getdate()))))) end
And rptMonthly.complete_date 1 Then ltrim(rtrim(str(month(getdate())))) + '/01/' + ltrim(rtrim(str(year(getdate()))))
else ltrim(rtrim(str(month(getdate())))) + '/01/' + ltrim(rtrim(str(year(getdate())))) end )
GROUP BY rptDataMonthly.region
) A1
On RDM.Region = A1.Region
and a1.region=a.region
August 25, 2008 at 7:04 am
Just a guess since I don't know what your error is, but if your columns can be NULL and you are handling that for display purposes you also need to handle that in your calculations as well so if PREVIOUSMONTH can be null then any calculation using that would have to handle that. Like the PREVIOUSMONTHPCT calculation, when PREVIOUSMONTH Is Null then PREVIOUSMONTHPCT should either be NULL or 0 based on what you want.
Have you isolated which calculation is producing the overflow?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 8:04 am
1 . Use this Query for Getting First Date of Previous Month
SELECT DATEADD(MONTH,-1, DATEADD(DAY,1,DATEADD(DAY,-DAY(GETDATE()),GETDATE())))
2. If you declare numeric(8,6) Means
Sql Allot 2 Digit for Numeric & 6 Digit For Decimal
So increase Numeric Digits to avoid Arithmetic Over Flow Error
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply