Creating a Computed column as a percent of two colums

• tedo

  SSCrazy

  Points: 2283

  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

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 29, 2012 at 8:05 pm

  #1453439

  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: 2283

  More actions

  February 29, 2012 at 9:24 pm

  #1453443

  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