I am working in our Land Management Software which includes a custom field called ESFT_BILLINGEDU, this field is used to combine several other custom fields in the database and then divide the total by 20 to get our billing EDU's for sewer calculations (I work for a public agency). The answer displayed is always off by .2 even if whole numbers divisible by 20 are used.
Our software does not support use of DECIMAL, so I cannot convert to that (which would likely solve the issue). I have tried using CONVERT to INTEGER, not using CONVERT at all and simply dividing, and using CAST and ROUND to see if I can get the correct result. All have failed.
CONVERT([float],CONVERT([float](2),((((((((((((((((((((((((((isnull([EXT1_BAR_SINK],(0))*(2)+isnull([EXT1_BATHTUB],(0))*(4))+isnull([EXT1_CLWASH],(0))*(4))+isnull([EXT1_DEN_LAV],(0))*(1))+isnull([EXT1_DISHWASH],(0))*(4))+isnull([EXT1_FLOOR_DRN],(0))*(1))+isnull([EXT2_KIT_SINK],(0))*(4))+isnull([EXT2_LAUN_TUB],(0))*(4))+isnull([EXT2_LAVY_SNGL],(0))*(2))+isnull([EXT2_LAVY_DBL],(0))*(4))+isnull([EXT2_MOP_SINK],(0))*(4))+isnull([EXT2_SHOW],(0))*(4))+isnull([EXT2_URIN_WALL],(0))*(5))+isnull([EXT2_WATER_CLOSET],(0))*(6))+isnull([EXT2_WCL_FLUSH],(0))*(10))+isnull([ext1_cir_sink],(0))*(4))+isnull([ext1_cup_sink],(0))*(1))+isnull([ext1_den_unit],(0))*(1))+isnull([ext1_drink_foun],(0))*(1))+isnull([ext1_flushing],(0))*(10))+isnull([ext2_rv_dump],(0))*(20))+isnull([ext2_rv_spaces],(0))*(8))+isnull([ext2_urin_flsh],(0))*(3))+isnull([ext2_urin_step_on],(0))*(10))+isnull([ext2_wash_sink_set],(0))*(3))+isnull([EXT1_FLOOR_SINK],(0))*(4))+((((((((((((((((((((((((isnull([ENEW_SINK],(0))*(2)+isnull([ENEW_BATH],(0))*(4))+isnull([ENEW_CLTHWASH],(0))*(4))+isnull([ENEW_DEN_LAV],(0))*(1))+isnull([ENEW_DISHWASH],(0))*(4))+isnull([ENEW_FLR_DRN],(0))*(1))+isnull([ENEW2_KIT_SINK],(0))*(4))+isnull([ENEW2_LAUN_TUB],(0))*(4))+isnull([ENEW2_LAVY_SNGL],(0))*(2))+isnull([ENEW2_LAVY_DBL],(0))*(4))+isnull([ENEW2_MOP_SINK],(0))*(4))+isnull([ENEW2_SHOWER],(0))*(4))+isnull([ENEW2_URIN_Wall],(0))*(5))+isnull([ENEW2_WCL_Home],(0))*(6))+isnull([ENEW2_WCL_FLUSH],(0))*(10))+isnull([enew2_urin_flush],(0))*(3))+isnull([enew_cir_spray],(0))*(4))+isnull([enew_cup_sink],(0))*(1))+isnull([enew_den_unit],(0))*(1))+isnull([enew_drink_foun],(0))*(1))+isnull([enew_flushing],(0))*(10))+isnull([enew2_rv_dump],(0))*(20))+isnull([enew2_rv_space],(0))*(8))+isnull([enew2_urin_step],(0))*(10))+isnull([ENEW_FLR_SINK],(0))*(4)))+isnull([enew2_wash_sink],(0))*(3)))/CONVERT([float],(20),(0)),(0)),(0)))
the fields within the [] are the custom screen fields. The last line of code divides these totals by 20 to get a number. I cannot get the field to correctly divide, it is always off by .2 For example, in one of our permits the number of Existing Fixtures (the first part of the code is 230, the number of the new fixtures (second part of code is 223 for a total of 453. This divided by 20, last line of code..equals 22.65, the current code gives the answer of 22.4500007629395)
