zkeown
asked on
Probably easy SQL but urgent
Fairly simple here, probably. Percent Goal, Units_data_warehouse, and OEM_Goal are all integers. When I get my output, Percent goal is either 0 or 100. I assume this is because of data types, but every attempt to fix it has resulted in arithmetic overflow or some other error.
UPDATE #output
SET Percent_Goal = ((Units_Data_Warehouse / OEM_Goal) * 100)
Try this.
UPDATE #output
SET Percent_Goal = (Units_Data_Warehouse * 100) / OEM_Goal
UPDATE #output
SET Percent_Goal = (Units_Data_Warehouse * 100) / OEM_Goal
UPDATE #output
SET Percent_Goal = ((CAST(Units_Data_Warehous e AS FLOAT)/ CAST(OEM_Goal AS FLOAT) * 100.00)
SET Percent_Goal = ((CAST(Units_Data_Warehous
ASKER
Wow. OK all solutions are close, however if the math evaluates to 4.6 I'm getting 4 and I need 5.
Use CEIL function.
UPDATE #output SET Percent_Goal = CEIL((Units_Data_Warehouse * 100) / OEM_Goal);
UPDATE #output SET Percent_Goal = CEIL((Units_Data_Warehouse
UPDATE #output
SET Percent_Goal = ROUND ( (( cast(Units_Data_Warehouse as decimal(10,2) ) / OEM_Goal) * 100) , 2)
SET Percent_Goal = ROUND ( (( cast(Units_Data_Warehouse as decimal(10,2) ) / OEM_Goal) * 100) , 2)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Neither is working... For instance one row has
Units_data_warehouse = 2
OEM_Goal = 43
2 / 43 = .0465... * 100 = 4.65
But says 4.
Units_data_warehouse = 2
OEM_Goal = 43
2 / 43 = .0465... * 100 = 4.65
But says 4.
ASKER
Ah ha! Thanks aneeshattingal!
SET Percent_Goal = (( cast(Units_Data_Warehouse as decimal(10,2) ) / OEM_Goal) * 100)