May 11, 2017 at 11:35 am
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')
May 11, 2017 at 12:48 pm
sc-w - Thursday, May 11, 2017 11:35 AMHi,
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')
Here are 2 options which might perform different. Choose the one best suited for you or use them to get a better option.
UPDATE tc SET
price = creditcard_fee,
Total = creditcard_fee
FROM dbo.test tc
CROSS APPLY( SELECT SUM(total) * (0.02) AS creditcard_fee
FROM test oth
WHERE Product_Code <> 'card fee'
AND oth.SalesOrderID = tc.SalesOrderID) tot
WHERE tc.Product_Code = 'card fee';
WITH cteTotals AS(
SELECT SalesOrderID,
SUM(total) * (0.02) AS creditcard_fee
FROM test
WHERE Product_Code <> 'card fee'
GROUP BY SalesOrderID)
UPDATE tc SET
price = creditcard_fee,
Total = creditcard_fee
FROM dbo.test tc
JOIN cteTotals tot ON tc.SalesOrderID = tot.SalesOrderID
WHERE tc.Product_Code = 'card fee';
May 11, 2017 at 1:09 pm
Thank you very much Luis, two great answers. I can see how they are working so will know where I am at next time. Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply