Solved

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

Posted on 2014-10-13
16
59 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 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 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 65

Expert Comment

by:Jim Horn
ID: 40379088
btw small typo, lose the comma after the last value in the IN ( ).
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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 …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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