My syntax below works (may not be the most efficient syntax, but it almost serves the purpose). The issue that I have with it, is that when the UPDATE
statement is run, due to the JOIN
condition each row that fits the criteria is updated. I would like to only UPDATE
1 row, per empName
. Maybe I could use the field AcctIDWorked
to update either the MAX()
or the MIN()
of this field? Doesn't matter either way honestly as I just need ONE row per empName
updated.
How can I accomplish this?
DDL Below and current syntax
IF OBJECT_ID('dbo.__SalesDetailInfo', 'U') IS NOT NULL
DROP TABLE dbo.__SalesDetailInfo;
IF OBJECT_ID('dbo.__Sales', 'U') IS NOT NULL
DROP TABLE dbo.__Sales;
Create Table __Sales
(
EmpName varchar(max)
,TotalSaleAmount decimal(10,2)
)
Insert Into __Sales (EmpName, TotalSaleAmount) Values
('OWT', '167.30'),('Fit D', '512.22'),('Mike', '4147.60')
,('Curtis', '66.96'),('Curtis', '1165.58'),('Curtis', '1507.64')
,('Curtis', '1551.79'),('Curtis', '8132.13'),('Curtis', '28.62')
,('Sam', '499.00'),('Curtis', '1159.76'),('Sam', '2011.98')
,('Mike', '450.00'),('Mike', '1453.60'),('Sam', '762.45')
,('Curtis', '1056.00'),('Mike', '1596.50'),('OWT', '1768.80')
,('Mike', '1200.00'),('Mike', '1500.00'),('Fit D', '17921.30')
,('Curtis', '456.73'),('Curtis', '944.23'),('Curtis', '2242.00')
,('Mike', '437.50'),('Sam', '3258.07'),('Sam', '3258.07')
,('OWT', '7263.57'),('OWT', '7225.57'),('OWT', '224.63')
,('Curtis', '569.89')
Create Table __SalesDetailInfo
(
EmpName varchar(max)
,TotalSales decimal(10,2)
,AcctIDWorked int
)
Insert Into __SalesDetailInfo (EmpName, AcctIDWorked) Values
('Curtis', 1), ('Curtis', 2), ('Curtis', 3), ('Curtis', 4)
,('Fit D', 22), ('Fit D', 11), ('Fit D', 44)
,('Mike', 110), ('Mike', 111), ('Mike', 113), ('Mike', 114)
,('Sam', 300), ('Sam', 310), ('Sam', 340)
,('OWT', 500), ('OWT', 550), ('OWT', 580), ('OWT', 590)
;WITH UPDATECTE AS
(Select EmpName, sumTSA=SUM(TotalSaleAmount)
FROM __Sales
GROUP BY EmpName)
UPDATE __SalesDetailInfo
SET TotalSales = sumTSA
FROM UPDATECTE cte
INNER JOIN __SalesDetailInfo t
ON RTRIM(LTRIM(cte.EmpName)) = RTRIM(LTRIM(t.EmpName))
Best Answer
You could use that to create a view.