Need some decimal help

Question

Post reply

Need some decimal help

Greg Snidow

SSCoach

Points: 16108
More actions
October 24, 2007 at 11:25 am

#62478

Greetings folks. I have a simple query whereby I am trying to calculate a percentage as follows.
SELECT
STATE,
PROD_GRP,
FORECAST = SUM(forecast_q), -- INT
DEMAND = SUM(demand_q), -- INT
VARIANCE = (SUM(demand_q)/SUM(forecast_q))*100
FROM tblforecast_demand
WHERE month = '2007_09'
AND prod_grp IN ('FDH','Fiber Cable','Handhole','Splitters','Terminal')
GROUP BY prod_grp,state
I have messed around with trying to convert the VARIANCE calculation to FLOAT, REAL, and MONEY, just trying to get an answer with two numbers after the decimal. All I can come up with is zeros. What I need, for example is if DEMAND = 80 and FORECAST = 100, I need VARIANCE to be .80. Should be simple, but I can not figure it out.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 1

Try converting the demand_q and the forecast_q to decimal first....

sum(convert(decimal,demand_Q))

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

Thanks Rich. That worked, but the result has 6 digits to the left of decimal, where I only want 2. Is there a way to restrict? Also, I realized I do not need to have *100 at the end.

VARIANCE = SUM(CONVERT(DECIMAL,demand_q))/SUM(CONVERT(DECIMAL,forecast_q))

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Greg Snidow SSCoach Points: 16108 More actions · Answer 3

This did exactly what I needed

VARIANCE = LEFT(ROUND(SUM(CONVERT(DECIMAL,demand_q))/SUM(CONVERT(DECIMAL,forecast_q)),2),4)

Thanks for the help

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 4

When you are looking to restrict the DISPLAY of the answer, unlike the precision (as you first asked), it would normallly be handled by the front end application that is consuming the result of the query. In other words, let the developers worry about that !!!

If you don't have that option, then try convert(decimal(10,2),demand_q) and convert(decimal(10,2),forecast_q)

HTH

Greg Snidow SSCoach Points: 16108 More actions · Answer 5

Rich (10/24/2007)
When you are looking to restrict the DISPLAY of the answer, unlike the precision (as you first asked), it would normallly be handled by the front end application that is consuming the result of the query. In other words, let the developers worry about that !!!
If you don't have that option, then try convert(decimal(10,2),demand_q) and convert(decimal(10,2),forecast_q)
HTH

I'm the inadequately trained one stop shop at my job. I use access as a front, but this is just messing around for messing around's sake. I tried various configurations of precision and scale to no avail. BOL indicates precision is the total number of digits, and scale is the number of digits to the right of the decimal, so I tried this:

SELECT CONVERT(DECIMAL(2,0),81)/CONVERT(DECIMAL(2,0),93)

result = .870967

I can't change the precision or scale to anything that seems consistent with the BOL description. Any ideas where to look for a better explanation that BOL?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 6

to limit the display you want to cast or convert it after the math. Or you round it.

SELECT CONVERT(DECIMAL(2,0),83)/CONVERT(DECIMAL(2,0),97)

SELECT cast(CONVERT(DECIMAL(2,0),83)/CONVERT(DECIMAL(2,0),97) as decimal(9,2))

SELECT round(CONVERT(DECIMAL(2,0),83)/CONVERT(DECIMAL(2,0),97),2)

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Greg Snidow (10/24/2007)
What I need, for example is if DEMAND = 80 and FORECAST = 100, I need VARIANCE to be .80. Should be simple, but I can not figure it out.

As I see from your code VARIANCE should be 80, not .80

If it's right you don't need to convert anything, just move "* 100" to the front:

100 * ...

_____________
Code for TallyGenerator

Greg Snidow SSCoach Points: 16108 More actions · Answer 8

And the winner is ...

IF OBJECT_ID('TempDB..#Test','u') IS NOT NULL

DROP TABLE #Test

CREATE TABLE #Test

(

ID INT IDENTITY(1,1),

forecast INT,

demand INT

)

INSERT INTO #Test

SELECT 100,99 UNION ALL

SELECT 567,800 UNION ALL

SELECT 200,430 UNION ALL

SELECT 101,80

SELECT

VarianceA = 1 - demand/forecast,

VarianceB = 1 - CONVERT(DECIMAL,demand)/CONVERT(DECIMAL,forecast),

VarianceC = 1 - CONVERT(DECIMAL(5,2),demand)/CONVERT(DECIMAL(5,2),forecast),

VarianceD = 1 - ROUND(CONVERT(DECIMAL(5,2),demand)/CONVERT(DECIMAL(5,2),forecast),2),

VarianceE = 1 - CAST(CONVERT(DECIMAL(5,2),demand)/CONVERT(DECIMAL(5,2),forecast) AS DECIMAL(5,2))

FROM #Test

VarianceE and the CAST method. Thanks mrpolecat

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.