Solved

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

Posted on 2014-10-13
16
53 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 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

17 Experts available now in Live!

Get 1:1 Help Now