Creating a Computed column as a percent of two colums

Question

Creating a Computed column as a percent of two colums

tedo

SSCrazy

Points: 2229
More actions
February 29, 2012 at 7:31 pm

#391689

Please can you see my below code for some reason it does not work I want a % of column [NumberAffected] and [batchQuantity] it seems to always show up as 0 any help will be greatly apprciated.
CREATE TABLE [dbo].[product1](
[QIR] [int] IDENTITY(100000,1) NOT NULL,
[Employee] [nvarchar](50) NULL,
[Category] [nvarchar](50) NULL,
[AQL] [nvarchar](50) NOT NULL,
[Description] [nvarchar](250) NULL,
[samplesID] [nvarchar](50) NULL,
[costs] [smallmoney] NULL,
[costs1] [smallmoney] NULL,
[costs2] [smallmoney] NULL,
[costs3] [smallmoney] NULL,
[costs4] [smallmoney] NULL,
[total] AS (((([costs]+[costs2])+[costs3])+[costs1])+[costs4]),
[date] [datetime] NULL,
[Supplier] [nvarchar](50) NULL,
[projectcode] [int] NULL,
[NumberAffected][int] NOT NULL,
[batchQuantity] [int] NOT NULL,
[%affected] AS ([NumberAffected]/nullif([batchQuantity],(0))*100),
[Investigation] [nvarchar](50) NULL,
[Followup] [nvarchar](max) NULL,
[Status] [nvarchar](5) NULL,
[supcat] [nvarchar](50) NULL,
CONSTRAINT [PK_product1] PRIMARY KEY CLUSTERED
(
[QIR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
select NumberAffected, [batchQuantity], [%affected] from [product1]
NumberAffectedbatchQuantity%affected
500010000000

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

your issue is integer division;

in sql server, if you divide an integer by an integer, you get an integer...bot a decimal that you might get in a programming language.

so 99/100 = 0 as an integer, for example...no rounding.

the fix is to simply multiply one of the values by 1.0

[%affected] AS (([NumberAffected] * 1.00 / nullif([batchQuantity],(0))*100),

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tedo SSCrazy Points: 2229 More actions · Answer 2

cheers i tried to code below it still did not work however, I turned the columns into decimal and it seems fine now. Cheers again