Add SQL Server Numeric Fields and Update Another Field?

I can do this in MS-Access (and it works) but need the equivalent in SQL Server 2000. All fields reside in the same table (tbl_Toll_Free_Final). Percent_Busy is a Decimal (18,5) and the others are Integers.

I have tried this and Percent_Busy is updated to 0. Total_Busy_Calls or Overflow_Calls can be 0 so perhaps its a div by zero error but SQL server does not report any error.

UPDATE dbo.tbl_Toll_Free_Final
SET Percent_Busy =
  (Total_Busy_Calls + Overflow_Calls) / (Total_Calls + Overflow_Calls)

For example:

• Total_Busy_Calls = 12, Overflow_Calls = 0 so first result is 12
• Total_Calls = 1000, Over开发者_运维问答flow_Calls = 12 so second result is 1012
• Calculation is 12/1012=0.0119

Hope you SQL Server gurus can help.

Try this:

UPDATE dbo.tbl_Toll_Free_Final 
SET Percent_Busy = 
  (Total_Busy_Calls + Overflow_Calls) / 
  CAST((Total_Calls + Overflow_Calls) AS DECIMAL(18,5))

You are dividing an integer by an integer which ignores decimals, remainders, fractions, etc. By casting the divisor to a decimal, you avoid this integer division.

There is no reason to use a manual update for this... also note the *1.0 to avoid integer math. Are you going to run your update statement every time a row is added or updated?

ALTER TABLE dbo.tbl_Toll_Free_Final DROP COLUMN Percent_Busy;
GO
ALTER TABLE dbo.tbl_Toll_Free_Final ADD Percent_Busy AS 
(
    CONVERT(DECIMAL(18,5), 
    (Total_Busy_Calls + Overflow_Calls)*1.0 / (Total_Calls + Overflow_Calls))
);

Now it will always be up to date... just may need some conditional logic for division by zero.

The problem you have is that your dividing integers by integers, which results in an integer and is then stored in your decimal field.

You need to convert one of the operands to decimal to force decimal division.

UPDATE dbo.tbl_Toll_Free_Final 
SET Percent_Busy = 
  convert(decimal(18,5), Total_Busy_Calls + Overflow_Calls) / 
  convert(decimal(18,5), Total_Calls + Overflow_Calls)

To add a check for divide-by-zero issues:

UPDATE dbo.tbl_Toll_Free_Final 
SET Percent_Busy = 
  case 
    when Total_Calls + Overflow_Calls = 0 then 0
    else
      convert(decimal(18,5), Total_Busy_Calls + Overflow_Calls) / 
      convert(decimal(18,5), Total_Calls + Overflow_Calls)
  end

An alternative to having this update statement is to set the column to be a calculated field so that it generates the column data "on the fly". (Drop the existing column first)

alter table dbo.tbl_Toll_Free_Final
add Percent_Busy AS (
      case 
        when Total_Calls + Overflow_Calls = 0 then 0
        else
          convert(decimal(18,5), Total_Busy_Calls + Overflow_Calls) / 
          convert(decimal(18,5), Total_Calls + Overflow_Calls)
      end
)

It's because you're doing integer division and not using floating points. Since the result is going to be a percent (range between 0.0 and 1.0) the integer division result will always be 0). Try this instead, changed to SELECT so you can view the results:

SELECT *,
  (Total_Busy_Calls + Overflow_Calls) / ((Total_Calls + Overflow_Calls) * 1.0)
FROM dbo.tbl_Toll_Free_Final;

The UPDATE statement would be:

UPDATE dbo.tbl_Toll_Free_Final
SET Percent_Busy =
  (Total_Busy_Calls + Overflow_Calls) / ((Total_Calls + Overflow_Calls) * 1.0);