CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2012
Scott Ross
Scott Ross

Reputation: 1

How would I go about reducing the price of a service by 10% in SQL using an update statement?

This is the table with the relevant values.

More specifically I need a 5% reduction on both Job_man_hours and Labour_Cost_Per_Hour to equal out to 10%. Just not sure how to do this and havent found much online. 

INSERT INTO Work_Summary 
(
    Invoice_No,Invoice_Date,Order_Quantity,Job_man_hours,Job_Desc
    ,Labour_Cost_Per_Hour,Job_Number,Product_Code
 )
VALUES 
    (1,'2017/05/18',2,'1:20:00','Description',100.00,1,'PC_1001'),
    (2,'2017/05/18',6,'2:30:00','Description',75.00,2,'PC_1002'),
    (3,'2017/05/18',7,'3:10:00','Description',50.00,3,'PC_1003'),
    (4,'2017/05/18',1,'4:35:00','Description',20.00,4,'PC_1004'),
    (5,'2017/05/18',9,'5:50:00','Description',15.00,5,'PC_1005'),
    (6,'2017/05/18',11,'6:30:00','Description',10.00,6,'PC_1006'),
    (7,'2017/05/18',1,'2:00:10','Description',18.95,7,'PC_1007'),
    (8,'2017/05/18',6,'2:30:50','Description',19.99,8,'PC_1008'),
    (9,'2017/05/18',8,'3:00:25','Description',40.00,9,'PC_1009'),
    (10,'2017/05/18',9,'1:30:18','Description',30.00,10,'PC_1010'),
    (11,'2017/05/18',14,'2:00:10','Description',20.95,11,'PC_1011'),
    (12,'2017/05/18',3,'2:30:11','Description',9.99,12,'PC_1012'),
    (13,'2017/05/18',6,'3:00:45','Desription',199.99,13,'PC_1013'),
    (14,'2017/05/18',8,'3:30:34','Description',200.00,14,'PC_1014'),
    (15,'2017/05/18',9,'4:00:54','Description',500.00,15,'PC_1015')

Upvotes: 0

Views: 44

Answers (2)

SqlZim
SqlZim

Reputation: 38063

If your job_man_hours is a time data type you can get the total seconds, multiple that by 0.95, and convert it back to time using dateadd() and convert():

update work_summary
  set Labour_Cost_per_hour = (Labour_Cost_per_hour*0.95)
    , Job_man_hours = (convert(time,dateadd(second,datediff(second,0,job_man_hours)*0.95,0)))

rextester demo: http://rextester.com/EQRD5100

returns:

+------------+--------------+----------------+---------------+-------------+----------------------+------------+--------------+
| Invoice_No | Invoice_Date | Order_Quantity | Job_man_hours |  Job_Desc   | Labour_Cost_Per_Hour | Job_Number | Product_Code |
+------------+--------------+----------------+---------------+-------------+----------------------+------------+--------------+
|          1 | 2017-05-18   |              2 | 01:16:00      | Description | 95,0000              |          1 | PC_1001      |
|          2 | 2017-05-18   |              6 | 02:22:30      | Description | 71,2500              |          2 | PC_1002      |
|          3 | 2017-05-18   |              7 | 03:00:30      | Description | 47,5000              |          3 | PC_1003      |
|          4 | 2017-05-18   |              1 | 04:21:15      | Description | 19,0000              |          4 | PC_1004      |
|          5 | 2017-05-18   |              9 | 05:32:30      | Description | 14,2500              |          5 | PC_1005      |
|          6 | 2017-05-18   |             11 | 06:10:30      | Description | 9,5000               |          6 | PC_1006      |
|          7 | 2017-05-18   |              1 | 01:54:09      | Description | 18,0025              |          7 | PC_1007      |
|          8 | 2017-05-18   |              6 | 02:23:17      | Description | 18,9905              |          8 | PC_1008      |
|          9 | 2017-05-18   |              8 | 02:51:23      | Description | 38,0000              |          9 | PC_1009      |
|         10 | 2017-05-18   |              9 | 01:25:47      | Description | 28,5000              |         10 | PC_1010      |
|         11 | 2017-05-18   |             14 | 01:54:09      | Description | 19,9025              |         11 | PC_1011      |
|         12 | 2017-05-18   |              3 | 02:22:40      | Description | 9,4905               |         12 | PC_1012      |
|         13 | 2017-05-18   |              6 | 02:51:42      | Desription  | 189,9905             |         13 | PC_1013      |
|         14 | 2017-05-18   |              8 | 03:20:02      | Description | 190,0000             |         14 | PC_1014      |
|         15 | 2017-05-18   |              9 | 03:48:51      | Description | 475,0000             |         15 | PC_1015      |
+------------+--------------+----------------+---------------+-------------+----------------------+------------+--------------+

Upvotes: 0

daZza
daZza

Reputation: 1689

To reduce all values in both columns by 5% each (which seems to be your requirement - if it's not, please be more specific about your expected results), you can use these statements:

UPDATE Work_Summary SET job_man_hours  = (job_man_hours*0.95);
UPDATE Work_Summary SET labour_cost_per_hour = (labour_cost_per_hour *0.95);

EDIT: I just realized that I looked at the wrong argument in your INSERT-statment for the value of job_man_hours. Applying the update above to a value like 01:20:00 will not work.

In order to find a solution for casting your value to something we can calculate on, we'd need to know which datatype you are using for the job_man_hours column.

Upvotes: 1

Related Questions