UPDATE using SUM
in [Programming SQL]
|
Prev: Sql server 2008 r2 hangs up
Next: Top 2 from count
From: DavidC on 10 Aug 2010 08:45 I have a stored procedure where I am trying to update a temp table in the flow of the sp. I have tried the code below but it gives me an error "Incorrect syntax near the keyword 'GROUP' and I understand the error but don't know how to get around it and still get sums. Can anyone help? Thanks. UPDATE #tempInventoryAnalysis SET [PTDSalesQty] = SUM(CASE WHEN MGB.dbo.tblArHistHeader.InvcDate BETWEEN @StartDate AND @EndDate THEN MGB.dbo.tblArHistDetail.QtyShipSell ELSE 0 END), [YTDSalesQty] = SUM(MGB.dbo.tblArHistDetail.QtyShipSell), [PTDSalesAmt] = SUM(CASE WHEN MGB.dbo.tblArHistHeader.InvcDate BETWEEN @StartDate AND @EndDate THEN MGB.dbo.tblArHistDetail.PriceExt ELSE 0 END), [YTDSalesAmt] = SUM(MGB.dbo.tblArHistDetail.PriceExt), [YTDCostAmt] = SUM(MGB.dbo.tblArHistDetail.CostExt) FROM MGB.dbo.tblArHistHeader INNER JOIN MGB.dbo.tblArHistDetail ON MGB.dbo.tblArHistHeader.PostRun = MGB.dbo.tblArHistDetail.PostRun AND MGB.dbo.tblArHistHeader.TransId = MGB.dbo.tblArHistDetail.TransID INNER JOIN #tempInventoryAnalysis.ItemId ON MGB.dbo.tblArHistDetail.PartId = #tempInventoryAnalysis.ItemId WHERE (MGB.dbo.tblArHistHeader.InvcDate BETWEEN @YearStart AND @EndDate) AND (MGB.dbo.tblArHistDetail.PartId IS NOT NULL) GROUP BY MGB.dbo.tblArHistDetail.PartId -- David
From: Tom Cooper on 10 Aug 2010 12:12 If I understand what you want done by your update, you do not need a GROUP BY. For each row in #tempInventoryAnalysis, the result of the select only contains the rows for one MGB.dbo.tblArHistDetail.PartId (because your query has INNER JOIN #tempInventoryAnalysis.ItemId ON MGB.dbo.tblArHistDetail.PartId = #tempInventoryAnalysis.ItemId so for each row in #tempInventoryAnalysis, you just want the sum with no GROUP BY. So just remove the GROUP BY from the update and it should work. If that doesn't work, please provide sample tables and data (in the form of CREATE TABLE and INSERT statements, please) and then the results you want in #tempInventoryAnalysis after the update and we should be able to provide you with what you want. Tom "DavidC" <dlchase(a)lifetimeinc.com> wrote in message news:B3B91C54-65D3-4F2B-9109-79FDCD03FD51(a)microsoft.com... >I have a stored procedure where I am trying to update a temp table in the > flow of the sp. I have tried the code below but it gives me an error > "Incorrect syntax near the keyword 'GROUP' and I understand the error but > don't know how to get around it and still get sums. Can anyone help? > Thanks. > > UPDATE #tempInventoryAnalysis > SET [PTDSalesQty] = SUM(CASE WHEN MGB.dbo.tblArHistHeader.InvcDate > BETWEEN @StartDate AND @EndDate THEN > MGB.dbo.tblArHistDetail.QtyShipSell > ELSE 0 > END), > [YTDSalesQty] = SUM(MGB.dbo.tblArHistDetail.QtyShipSell), > [PTDSalesAmt] = SUM(CASE WHEN MGB.dbo.tblArHistHeader.InvcDate > BETWEEN @StartDate AND @EndDate THEN > MGB.dbo.tblArHistDetail.PriceExt > ELSE 0 > END), > [YTDSalesAmt] = SUM(MGB.dbo.tblArHistDetail.PriceExt), > [YTDCostAmt] = SUM(MGB.dbo.tblArHistDetail.CostExt) > FROM MGB.dbo.tblArHistHeader INNER JOIN > MGB.dbo.tblArHistDetail ON MGB.dbo.tblArHistHeader.PostRun = > MGB.dbo.tblArHistDetail.PostRun AND > MGB.dbo.tblArHistHeader.TransId = MGB.dbo.tblArHistDetail.TransID > INNER JOIN > #tempInventoryAnalysis.ItemId ON MGB.dbo.tblArHistDetail.PartId = > #tempInventoryAnalysis.ItemId > WHERE (MGB.dbo.tblArHistHeader.InvcDate BETWEEN @YearStart AND @EndDate) > AND (MGB.dbo.tblArHistDetail.PartId IS NOT NULL) > GROUP BY MGB.dbo.tblArHistDetail.PartId > > > -- > David
From: --CELKO-- on 10 Aug 2010 12:39 >> I have a stored procedure where I am trying to update a temp table in the flow of the stored procedure. << What you have described is how we programmed in the 1950's with mag tape systems. Hang a scratch tape, then do procedural steps in a procedural process. The idea of SQL is declarative programming, without any concept of flow. We want to avoid redundancy and flow ISO-11179 naming rules, so silly redundancies like "#temp" or "tbl-" just are not used. UPDATE.. FROM.. is both proprietary and flawed by itself. I have no idea how it will work when you have a self-join in an UPDATE, as you do. Instead of thinking of step-by-step scratch tape programming, why not build a VIEW or a procedure that returns a query that is always up to date? Without DDL, I can only make a guess, but here is a skeleton: CREATE PROCEDURE AnalysisByParts (@in_start_date DATE, @in_end_date DATE) AS SELECT D.part_id, SUM(CASE WHEN H.invoice_date BETWEEN @in_start_date AND @in_end_date THEN D.ship_sell_qty ELSE 0 END) AS ptd_sales_qty, SUM(D.ship_sell_qty) AS ytd_sales_qty SUM(CASE WHEN H.invoice_date BETWEEN @in_start_date AND @in_end_date THEN D.price_ext ELSE 0 END) AS ptd_sales_amt SUM(D.price_ext) AS ytd_sales_amt, SUM(D.cost_ext) AS ytd_cost_amt FROM MGB.dbo.ArHistHeaders AS H, MGB.dbo.ArHistDetails AS D WHERE H.invc_date BETWEEN @in_start_date AND @in_end_date AND H.post_run = D.post_run AND H.trans_id = D.trans_id GROUP BY D.part_id; We can probably rearrange this to move the Headers. I am also trying to figure out how a part_id could be NULL in the details table.
From: DavidC on 10 Aug 2010 14:41 "--CELKO--" wrote: > >> I have a stored procedure where I am trying to update a temp table in the flow of the stored procedure. << > > What you have described is how we programmed in the 1950's with mag > tape systems. Hang a scratch tape, then do procedural steps in a > procedural process. The idea of SQL is declarative programming, > without any concept of flow. We want to avoid redundancy and flow > ISO-11179 naming rules, so silly redundancies like "#temp" or "tbl-" > just are not used. > > UPDATE.. FROM.. is both proprietary and flawed by itself. I have no > idea how it will work when you have a self-join in an UPDATE, as you > do. Instead of thinking of step-by-step scratch tape programming, why > not build a VIEW or a procedure that returns a query that is always > up to date? Without DDL, I can only make a guess, but here is a > skeleton: > > CREATE PROCEDURE AnalysisByParts > (@in_start_date DATE, @in_end_date DATE) > AS > SELECT D.part_id, > SUM(CASE WHEN H.invoice_date > BETWEEN @in_start_date AND @in_end_date > THEN D.ship_sell_qty ELSE 0 END) AS ptd_sales_qty, > SUM(D.ship_sell_qty) AS ytd_sales_qty > SUM(CASE WHEN H.invoice_date > BETWEEN @in_start_date AND @in_end_date > THEN D.price_ext ELSE 0 END) AS ptd_sales_amt > SUM(D.price_ext) AS ytd_sales_amt, > SUM(D.cost_ext) AS ytd_cost_amt > FROM MGB.dbo.ArHistHeaders AS H, > MGB.dbo.ArHistDetails AS D > WHERE H.invc_date BETWEEN @in_start_date AND @in_end_date > AND H.post_run = D.post_run > AND H.trans_id = D.trans_id > GROUP BY D.part_id; > > We can probably rearrange this to move the Headers. I am also trying > to figure out how a part_id could be NULL in the details table. > . > I agree with the 'tbl' prefix. And please don't jump to conclusions as we did not create this database we are just pulling some data from it for our customer. Those of us not in the "ivory tower" world have many weird things we have to deal with, including designs from other vendors. That said, I agree with using a SELECT and SUM's with a GROUP BY. However, this is only 1/3 of the answer. We have to SUM detail from 4 other header/detail tables. If I join them here also then I will get X times more total than I want. Any ideas on the best way to proceed? Should I build 3 summary views and then JOIN them on the ItemId to get a single summary by ItemId (named PartId in the one table)? Thanks. -- David
From: Erland Sommarskog on 10 Aug 2010 16:01
DavidC (dlchase(a)lifetimeinc.com) writes: > I have a stored procedure where I am trying to update a temp table in > the flow of the sp. I have tried the code below but it gives me an > error "Incorrect syntax near the keyword 'GROUP' and I understand the > error but don't know how to get around it and still get sums. Can anyone > help? You need to produce the sums in a derived table (or a Common Table Expression). Below is a rewrite of the query. It's fairly mechanical, and I had to make some assumptions. Be sure to test it well. I also introduced aliases to make it possible to see the forest through the trees. I also should add that I have not checked the query for syntax, but I trust that you can fix any syntax errors on your own. UPDATE #tempInventoryAnalysis SET PTDSalesQty = AH.PTDSalesQty, YTDSalesQty = AH.YTDSalesQty, PTDSalesAmt = AH.PTDSalesAmt, YTDSalesAmt = AH.YTDSalesAmt, YTDCostAmt = AH.YTDCostAmt FROM #tempInventoryAnalysis IA JOIN (SELECT D.PartID, PTDSalesQty = SUM(CASE WHEN H.InvcDate BETWEEN @StartDate AND @EndDate THEN D.QtyShipSell ELSE 0 END), YTDSalesQty = SUM(D.QtyShipSell), PTDSalesAmt = SUM(CASE WHEN H.InvcDate BETWEEN @StartDate AND @EndDate THEN D.PriceExt ELSE 0 END), YTDSalesAmt = SUM(D.PriceExt), YTDCostAmt = SUM(D.CostExt) FROM MGB.dbo.tblArHistDetail D ON ON d JOIN MGB.dbo.tblArHistHeader H ON H.PostRun = D.PostRun AND H.TransId = D.TransID WHERE H.InvcDate BETWEEN @YearStart AND @EndDate AND D.PartId IS NOT NULL GROUP BY D.PartID) AS AH ON AN.PartId = IA.ItemI -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |