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)