?
Solved

Need to update the cost field by 3% for a number of fields

Posted on 2014-10-13
16
Medium Priority
?
61 Views
Last Modified: 2015-02-13
I need to update the cost and actual cost field by 3% for a number of products.

These are some of the products that needs to beupdated:


Select TypePrefix,Cost,ActualCost
From Setup_Tissue_Prefix
Where TypePrefix In ('0527','0527','1149','0023')
0
Comment
Question by:Sanjeet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
16 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40378512
<air code>
UPDATE Setup_Tissue_Prefix
SET Cost = Cost * 1.03, ActualCost = ActualCost * 1.03
Where TypePrefix In ('0527','0527','1149','0023') 

Open in new window

0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40378678
To check before you do the update:

Select TypePrefix, Cost, Cost*1.03 CostNew, ActualCost, ActualCost*1.03 ActualCostNew
From Setup_Tissue_Prefix
Where TypePrefix In ('0527','0527','1149','0023');

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40379088
btw small typo, lose the comma after the last value in the IN ( ).
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40379231
:) I just copied that (I'll remove it ...)
0
 

Author Comment

by:Sanjeet
ID: 40380241
I also need to round to the nearest whole number. I already ran the script.

Do I just use the round function?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40380284
Round does the rounding, but still shows .0000's after the rounding based on the data types.
Just to be sure..
UPDATE Setup_Tissue_Prefix
SET Cost = CAST(ROUND(Cost * 1.03,0) as int), ActualCost = CAST(ROUND(ActualCost * 1.03,0) as int) 
Where TypePrefix In ('0527','0527','1149','0023') 

Open in new window


Disclaimer:  There might  be a better SQL 2012 answer, but I'm knee-deep in studying for the 2008 development exam, so that's what I have in my head right now.
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40380464
Note: if you already ran the script then the last comment of Jim Horn is multiplying again by a factor of 1.03

Try to see what you would get:

select CAST(ROUND(Cost,0) as int),  CAST(ROUND(ActualCost,0) as int)
from Setup_Tissue_Prefix
Where TypePrefix In ('0527','0527','1149','0023') ;

If it looks OK, then do the update:

UPDATE Setup_Tissue_Prefix
SET Cost = CAST(ROUND(Cost,0) as int), ActualCost = CAST(ROUND(ActualCost,0) as int)
Where TypePrefix In ('0527','0527','1149','0023') ;
0
 

Author Comment

by:Sanjeet
ID: 40420375
Ok I need to run in production now how would I re-write the scipt  by increasing price by 3% and then round to the nearest whole number?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40420379
If the only difference between comments already given and the last one is the price column, then just add price to the query:
UPDATE Setup_Tissue_Prefix
SET Price= CAST(ROUND(Price,0) as int)
Where TypePrefix In ('0527','0527','1149','0023') ;

Open in new window

0
 

Author Comment

by:Sanjeet
ID: 40420432
I have not applied the price increase in production yet or the rounding and I need to do both.

What would the SQL be for that?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40420438
Please explain to us in great detail how you believe the UPDATE statements provided you so far in this thread do not do exactly that.
0
 

Author Comment

by:Sanjeet
ID: 40420443
This appears and please correct me to only round up to the nearest whole number and not increase the price by 3%

I would like to increase by 3% and round to the whole # in one sql:

UPDATE Setup_Tissue_Prefix
SET Price= CAST(ROUND(Price,0) as int)
Where TypePrefix In ('0527','0527','1149','0023') ;
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40420484
Ah.  Going back to the comment before that with T-SQL:

UPDATE Setup_Tissue_Prefix
SET 
   Price = CAST(ROUND(Price* 1.03,0) as int), 
   Cost = CAST(ROUND(Cost * 1.03,0) as int), 
   ActualCost = CAST(ROUND(ActualCost * 1.03,0) as int) 
Where TypePrefix In ('0527','0527','1149','0023') 

Open in new window


If you no longer need to perform this action on Cost and ActualCost, just delete those two lines, and the comma at the end of the Price line.
0
 

Author Comment

by:Sanjeet
ID: 40420498
ok thanks will give that a shot thanks sir!
0
 

Author Comment

by:Sanjeet
ID: 40474347
STill testing
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question