David Gerler
asked on
Update a summary table with values from detail records
I have a transaction table with rows for each item in every transaction (detail) including item id (sku), qty (Amount2) and cost (Amount1).
I run a SQL job to update a daily summary table with the count (numItems) and $ value (Amount) of each sku sold.
The problem I have is the summary table is getting the exact same value for numItems and Amount in every row.
My original update query was:
I believe I was essentially updating every row in the summary table (more than 11M) with the last row of the sub query.
After searching I found this solution https://www.experts-exchange.com/questions/28963260/Update-table-field-based-on-results-from-a-query.html
I updated my query to:
I'm getting an error: Incorrect syntax near the keyword 'group'.
I'm hoping the run time will be sub-second after I figure our the syntax error.
Also, I'm currently using MS SQL 2005, but will be moving to 2014. I'd like syntax that will work with both.
Any help is appreciated
I run a SQL job to update a daily summary table with the count (numItems) and $ value (Amount) of each sku sold.
The problem I have is the summary table is getting the exact same value for numItems and Amount in every row.
My original update query was:
update PA_Product_Mix_Summary set numItems = a.num, amount = a.amount
from (Select d.yyyymmdd, d.store, dt.Typeid as ItemType, d.sku as Item, sum(amount2) as num, sum(amount1) as Amount
from detail d full outer join @Excluded e on d.id = e.id full outer join distinctType dt on d.type = dt.type
left outer join dbo.PA_Product_Mix_Summary pms
on d.store = pms.store and d.yyyymmdd = pms.yyyymmdd and d.type = dt.Type and d.sku = pms.item
where /*d.store = @STORE and */
d.yyyymmdd = @YYYYMMDD
and class = 'sale'
and subclass = 'item'
and d.[type] in ('sku','dept')
and e.id is null
and pms.item is not null
group by d.store, d.yyyymmdd, dt.typeid, d.sku) a
This query was running 2.5 Minutes. Way too long!I believe I was essentially updating every row in the summary table (more than 11M) with the last row of the sub query.
After searching I found this solution https://www.experts-exchange.com/questions/28963260/Update-table-field-based-on-results-from-a-query.html
I updated my query to:
update PA_Product_Mix_Summary set numItems = sum(amount2), amount = sum(amount1)
from detail d full outer join @Excluded e on d.yyyymmdd = @YYYYMMDD and d.id = e.id full outer join distinctType dt on d.type = dt.type
left outer join dbo.PA_Product_Mix_Summary pms
on d.store = pms.store and d.yyyymmdd = pms.yyyymmdd and d.type = dt.Type and d.sku = pms.item
where class = 'sale'
and subclass = 'item'
and d.[type] in ('sku','dept')
and e.id is null
and pms.item is not null
group by d.store, d.yyyymmdd, dt.typeid, d.sku
I'm getting an error: Incorrect syntax near the keyword 'group'.
I'm hoping the run time will be sub-second after I figure our the syntax error.
Also, I'm currently using MS SQL 2005, but will be moving to 2014. I'd like syntax that will work with both.
Any help is appreciated
Can you provide PA_Product_Mix_Summary structure?
ASKER
The full outer join is used to exclude certain kinds of transaction that will not have any items on them.
Create Query for PA_Product_Mix_Summary attached
create-PA_Product_Mix_Summary.sql
PA_full-job.txt
Create Query for PA_Product_Mix_Summary attached
create-PA_Product_Mix_Summary.sql
PA_full-job.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had to add " and PA_Product_Mix_Summary.Ite m = a.Item" at the end
It is producing the results I wanted now.
Statistics are:
SQL Server parse and compile time:
CPU time = 375 ms, elapsed time = 376 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(0 row(s) affected)
(5 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.
SQL Server Execution Times:
CPU time = 173 ms, elapsed time = 24 ms.
(6 row(s) affected)
It is producing the results I wanted now.
Statistics are:
SQL Server parse and compile time:
CPU time = 375 ms, elapsed time = 376 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(0 row(s) affected)
(5 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.
SQL Server Execution Times:
CPU time = 173 ms, elapsed time = 24 ms.
(6 row(s) affected)
I had to add " and PA_Product_Mix_Summary.IteOh, I forgot about that field. Luckily that you could figured it out.m = a.Item" at the end
So if the performance is also acceptable then I think you have no issues now, right?
Hope that you know what that means.