I’m using SQL Server 2005, I want to check the “TypeHopper_0x” value if = 2, add “CycleHopper_0x” to a @nat_weight, This part I was able to accomplish. Now @nat_weight = 1871.

Now I want to divide the value of "cycleHopper_0x) by the @nat_weight and UPDATE the value to “HPR_x_PRCT”, this is where I’m having problems.

1-For hopper 5 the operation UPDATE should be 177/1871 = 0.0149, but instead it’s a 0

2-I’m targeting index 8000 with this code, but eventually I’ll need to run for the entire table.

For hopper 5 the operation UPDATE should be 177/1871 = 0.0149, but instead it’s a 0

[code]DECLARE @nat_weight INT;

DECLARE @mattype1 INT;

DECLARE @mattype2 INT;

DECLARE @mattype3 INT;

– get material type, need only hoppers 1-3, hopper 4,5,6 material type will never = 2

SET @mattype1 = (SELECT typehopper_01 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000);

SET @mattype2 = (SELECT typehopper_02 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000);

SET @mattype3 = (SELECT typehopper_03 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000);

– if material type=2 then add to @nat_weight ,

IF @mattype1 = 2

set @nat_weight = (SELECT cyclehopper_01 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000);

IF @mattype2 = 2

set @nat_weight =@nat_weight+ (SELECT cyclehopper_01 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000);

IF @mattype3 = 2

set @nat_weight =@nat_weight+ (SELECT cyclehopper_03 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000)

UPDATE mm_Cycle_Data

Set HPR_1_PRCT =CycleHopper_01/@nat_weight, --my error might be here

HPR_2_PRCT =CycleHopper_02/@nat_weight,

HPR_3_PRCT =CycleHopper_03/@nat_weight,

HPR_4_PRCT =CycleHopper_04/@nat_weight,

HPR_5_PRCT =CycleHopper_05/@nat_weight

WHERE me38_cycle_data_ndx=8000[/code]

export.txt (4.45 KB)