Solved

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

Posted on 2014-10-13
16
55 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
  • 6
  • 6
  • 3
16 Comments
 
LVL 65

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 37

Expert Comment

by:Gerwin Jansen
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 65

Expert Comment

by:Jim Horn
ID: 40379088
btw small typo, lose the comma after the last value in the IN ( ).
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
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 65

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 37

Expert Comment

by:Gerwin Jansen
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 65

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 65

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 65

Accepted Solution

by:
Jim Horn earned 500 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
count vs exists 9 60
Trigger for audit 26 70
SQL Select - Finding chars in a column 2 56
Anyway to make these 2 SQL statements into one? 13 0
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now