Rounded Derived Column Using Division

• Triality

  SSCommitted

  Points: 1958

  More actions

  July 21, 2011 at 4:09 pm

  #241866

  I am creating a derived column with the following code:

  (DT_NUMERIC,18,3)(DER_Channel == "M" ? ((DAYS_SUPPLY / 30) > 1 ? (DAYS_SUPPLY / 30) : 1) : 1)

  Everything works well except the result of the derived column is being rounded. The results are all like this:

  1.000

  1.000

  3.000

  2.000

  2.000

  Instead of:

  .899

  1.222

  3.444

  2.253

  1.998

  The field the derived column is being put into is Numeric(18,3).

  I created a data viewer and the derived column is being rounded before being put into the database.

• Triality

  SSCommitted

  Points: 1958

  More actions

  July 21, 2011 at 4:25 pm

  #1356806

  I just found the issue. Apparently if you divide an integer by an integer the result is an integer. Here is a good explanation and fix:

  http://social.msdn.microsoft.com/Forums/en/transactsql/thread/f608a688-0f48-4e81-b183-57e8dfaec641