# SQL Division

**URL:** <https://forum.inductiveautomation.com/t/sql-division/10671>\
**Category:** Ignition\
**Created:** [October 26, 2015, 2:11pm UTC](https://forum.inductiveautomation.com/t/sql-division/10671 "2015-10-26T14:11:25Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jado58](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jado58](https://forum.inductiveautomation.com/u/Jado58)\
**Post date:** [October 26, 2015, 2:11pm UTC](https://forum.inductiveautomation.com/t/sql-division/10671/1 "2015-10-26T14:11:25Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/41aea0292d2d54e08df314a832550d4d01c9a3c9.txt) (4.45 KB)

---

<div class="post-metadata">

**Author:** ![Jado58](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jado58](https://forum.inductiveautomation.com/u/Jado58)\
**Post date:** [October 26, 2015, 3:14pm UTC](https://forum.inductiveautomation.com/t/sql-division/10671/2 "2015-10-26T15:14:36Z")

</div>

I found my error, I need to cast each value to FLOAT type.

```auto
Set HPR_1_PRCT =CAST(CycleHopper_01 AS FLOAT)/CAST(@nat_weight AS FLOAT),
```