Modifying each row based on attribute in the row

• Rasmus Remmer Bielidt

  SSCommitted

  Points: 1767

  More actions

  November 19, 2013 at 2:53 am

  #300281

  I have created a stored procedure that will return a list of test values for a SPC (Statistical Process Control) application.

  Each measurement is made up as an average of multiple single values (this is already done). However, each specific test has it's own requirement for rounding to a number of decimals. This is illustrated by a simplified SQL snippet.

  IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL

  DROP TABLE #MyTable

  ;

  CREATE TABLE #MyTable (

  [TEST] VARCHAR(20)

  , [MEASUREMENT] DECIMAL(24,8)

  , [NOOFDECIMALS] INTEGER

  , [ROUNDEDMEASURE] DECIMAL(24,8)

  )

  ;

  INSERT INTO #MyTable([TEST],[MEASUREMENT],[NOOFDECIMALS])

  SELECT 'TEST1', 23.456, 2

  UNION ALL

  SELECT 'TEST2', 4500.2, 0

  UNION ALL

  SELECT 'TEST3', 45.456457, 4

  UNION ALL

  SELECT 'TEST1', 23.456, 2

  UNION ALL

  SELECT 'TEST2', 4900.6, 0

  UNION ALL

  SELECT 'TEST3', 57.47894567, 4

  UNION ALL

  SELECT 'TEST1', 23.456, 2

  UNION ALL

  SELECT 'TEST2', 4400.22, 0

  UNION ALL

  SELECT 'TEST3', 79.789243742, 4

  ;

  SELECT * FROM #MyTable

  leading to this result set indicated in the attachment to this post.

  Now the tricky part is filling out the "ROUNDEDMEASURE" field for each row based on it's own "NOOFDECIMALS" content and I don't really know where to start. The table in question is relatively small (less than 500 records) as the stored procedure extracts per production batch number (parameter).

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  November 19, 2013 at 3:14 am

  #1667330

  Hi,

  You have duplicate rows and you cannot do a correct update.

  Ok,

  You can introduce a row id column (i.e. id with identity property), and then you'll have a better control over your update statement

  Create the temp table in the following way

  IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL

  DROP TABLE #MyTable

  ;

  CREATE TABLE #MyTable (

  [ID] int identity(1,1) primary key

  , [TEST] VARCHAR(20)

  , [MEASUREMENT] DECIMAL(24,8)

  , [NOOFDECIMALS] INTEGER

  , [ROUNDEDMEASURE] DECIMAL(24,8)

  );

  and re-populate it. Then you can use the [ID] in your update statement.

  Regards,

  IgorMi

  Igor Micev,My blog: www.igormicev.com

• Richard Warr

  SSCertifiable

  Points: 6963

  More actions

  November 19, 2013 at 3:19 am

  #1667332

  But having done that, isn't it just

  UPDATE #MyTable

  SET ROUNDEDMEASURE = ROUND(MEASUREMENT,NOOFDECIMALS);

• Rasmus Remmer Bielidt

  SSCommitted

  Points: 1767

  More actions

  November 19, 2013 at 3:38 am

  #1667337

  Richard Warr (11/19/2013)

  ---

  But having done that, isn't it just

  UPDATE #MyTable

  SET ROUNDEDMEASURE = ROUND(MEASUREMENT,NOOFDECIMALS);

  One of those moments before morning coffee, I guess....

  Of course it is, thank you.