UPDATE using SUM

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
0
Utf
8/10/2010 12:45:03 PM
sqlserver.programming 1873 articles. 0 followers. Follow

5 Replies
732 Views

Similar Articles

[PageSpeed] 47

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@lifetimeinc.com> wrote in message 
news:B3B91C54-65D3-4F2B-9109-79FDCD03FD51@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 

0
Tom
8/10/2010 4:12:25 PM
>> 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.
0
CELKO
8/10/2010 4:39:40 PM
"--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


0
Utf
8/10/2010 6:41:03 PM
DavidC (dlchase@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@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

0
Erland
8/10/2010 8:01:46 PM
>> 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 o=
ur customer. Those of us not in the "ivory tower" world have many weird thi=
ngs we have to deal with, including designs from other vendors. <<

LOL!  Ever work with SAP? The "Teutonic numonics" will drive you
nuts.

>> ..  We have to SUM detail from 4 other header/detail tables. If I join t=
hem 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 the=
m on the item_id to get a single summary by ItemId (named part_id in the on=
e table)? <<

Changing data element names can be worse than silly "tbl-" prefixes.
What about a UNION? Specifically an outer union? It gives you a wide
table but puts the data together. Here is a skeleton.

WITH Monster (item_id, ..)
AS
(
SELECT (item_id, <source A columns>, <NULLs for B>, <NULLs for C>,
<NULLs for D>
  FROM Source_A
UNION ALL
SELECT (item_id, <NULLs for A>, <source B columns>, <NULLs for C>,
<NULLs for D>
  FROM Source_B
UNION ALL
SELECT (item_id, <NULLs for A>, <NULLs for B>, <source C coumns>,
<NULLs for D>
  FROM Source_C
UNION ALL
SELECT (item_id,  <NULLs for A>, <NULLs for B>, <NULLs for C>, <source
D columns>
  FROM Source_D
),

ConsolidatedMonster (item_id, ..)
AS
(
SELECT item_id, MAX(A.c1),MAX(A.c2), ..MAX(B.c1), ..MAX(<C columns>),
MAX(<D columns>)
  FROM Monster
 GROUP BY item_id)

<< insert base select here >> ;

 The idea is to use MAX (which works on all data types) to keep data
from each of the four sources and put them next to each other in a
single row.  I would also use CAST (NULL AS <data type>) in the dummy
columns to be safe and to help the compiler.

I am assuming since they are diffrent tables, they model different
data elements.

FYI, OUTER UNION is part of the SQL Standards but nobody has
implemented it as far as I know.

0
CELKO
8/11/2010 6:25:49 PM
Reply:

Similar Artilces: