Select / calculate multple rows then update single row

[craigward]

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.

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

 

[TamarGranor]

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

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