TSQL not computing correctly with variables

• Schalk Lubbe

  SSC Veteran

  Points: 236

  More actions

  June 10, 2009 at 12:05 am

  #210526

  Hi there

  Something very simple has completely thrown me today. I have two variables @BeginCount & @EndCount, both declared as INT.

  I do a simple count into each - one showing the number of items at the beginning of the book year and the other at the end.

  Then I try to determine what % of those are still with us, doing (@EndCount/@BeginCount*100). This result I put into a variable declared as numeric(8,3). But if I check this last variable the result is 0. The variables @BeginCount = 1700 and @EndCount = 1100, and I absulutely don't know why I get a zero result.

  I even put in the TSQL: SELECT (1100 / 1700 * 100) and it returns zero!

  Thanks in advance for your expert advice.

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  June 10, 2009 at 12:46 am

  #1007205

  This is because you are working with integers. You need to work with types that work with decimal point. See the example bellow:

  --using numbers that are treated as integers

  SELECT (1100 / 1700 * 100)

  --Modifying at least one of the numbers to work with decimal point

  select (cast (1100 as numeric(8,3)) / 1700 * 100),

  1100/1700.0 * 100,

  1100/convert(numeric(8,3),1700) * 100

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Schalk Lubbe

  SSC Veteran

  Points: 236

  More actions

  June 10, 2009 at 1:19 am

  #1007211

  Thanks, Adi, I'll fix that tomorrow.

  Schalk

  Auckland, New Zealand