INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Select / calculate multple rows then update single row

Select / calculate multple rows then update single row

(OP)
Hi,
Looking for help with a bit of a complex query that is going over my head.

In my sample data there are two salesorderid’s, 1 and 2, which both have products associated to them, order 1 has 2 products and order 2 has 3 products. Each sales order also has a product row ‘card fee’ with a null total. I need to do a calculation per salesorderid which is SUM(total) for all products * (0.02) and then set the total column with the result of this calculation. Of course there could be thousands of salesorderid’s in this scenario each with varying products so it needs to be dynamically done.
So I need help with the update query and the nested select that does the maths. I'm not sure really where to start with this one. Any ideas would be much appreciated.

Thanks for looking.


CODE

CREATE TABLE [dbo].[test](
      [SalesOrderID] [int] NULL,
      [Qty] [int] NULL,
      [Price] [numeric](18, 2) NULL,
      [Total] [numeric](18, 2) NULL,
      [Product_Code] [varchar](75) NULL,

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

 

-- delete from test 

  insert into test (salesorderid, qty, price, total, product_code) values (1,1,'100','100','prod 1')
  insert into test (salesorderid, qty, price, total, product_code) values (1,2,'100','200','prod 2')
  insert into test (salesorderid, qty, price, total, product_code) values (1,1,'0','0','card fee') 

  insert into test (salesorderid, qty, price, total, product_code) values (2,1,'30','30','prod 1')
  insert into test (salesorderid, qty, price, total, product_code) values (2,2,'30','60','prod 2')  
  insert into test (salesorderid, qty, price, total, product_code) values (2,3,'28.50','85.50','prod 3')  
  insert into test (salesorderid, qty, price, total, product_code) values (2,1,'0','0','card fee')  
  

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT [SalesOrderID]
      ,[Qty]
      ,[Price]
      ,[Total]
      ,[Product_Code]
  FROM [wce_site_test].[dbo].[test]

-- Sum to work out card fee for sales order 1 

 SELECT SUM(total) * (0.02) AS creditcard_fee FROM test where (salesorderid ='1' and not Product_Code = 'card fee')

-- Sum to work out card fee for sales order 2

 SELECT SUM(total) * (0.02) AS creditcard_fee FROM test where (salesorderid ='2' and not Product_Code = 'card fee') 

RE: Select / calculate multple rows then update single row

If I understand what you're asking for, this should be close:

CODE

WITH Fee (SalesOrderID, creditcard_fee) AS
(SELECT SalesOrderID, SUM(total) * 0.02 
  FROM Test
  WHERE Product_Code <> 'card fee'
  GROUP BY SalesOrderID)

UPDATE Test
  SET Total = creditcard_fee
  WHERE Test.SalesOrderID = Fee.SalesOrderID
    AND Test.Product_Code = 'card fee' 

Tamar

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close