Update Statment Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Statment Help

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-04-28 : 06:40:20

I have a field in my report that has letters and number in it, with the update below it takes my numbers and subtracts them by 100, which works, but I need the number to read as whole numbers and not decimals (20.00). The only thing is some of the data in that column is decimals, numberic and some is alhpa. How can I revise this
update statement to just effect the ones that are decimals.

update Providers_with_Products
SET Providers_with_Products.[Fee Schedule] =
100.00 - [Fee Schedule]
WHERE ISnUMERIC([Fee Schedule]) = 1 and LEFT(prop_prc, 6) NOT IN('IMW115' ,'IMW120','IMW125','IMW130','IMWFEE')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 06:45:19

just add
AND FLOOR([Fee Schedule]) <> [Fee Schedule]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-04-28 : 06:52:46

Like this?

update Providers_with_Products
SET Providers_with_Products.[Fee Schedule] =
100.00 - [Fee Schedule]
WHERE ISnUMERIC([Fee Schedule]) = 1 and([Fee Schedule]) <> [Fee Schedule]and LEFT(prop_prc, 6) NOT IN('IMW115' ,'IMW120','IMW125','IMW130','IMWFEE')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 10:25:33

quote:
Originally posted by werhardt

Like this?

update Providers_with_Products
SET Providers_with_Products.[Fee Schedule] =
100.00 - [Fee Schedule]
WHERE ISnUMERIC([Fee Schedule]) = 1 and FLOOR([Fee Schedule]) <> [Fee Schedule]and LEFT(prop_prc, 6) NOT IN('IMW115' ,'IMW120','IMW125','IMW130','IMWFEE')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources