Query to update header record with line totals

[crystalreporting]

I have two sales order tables; a header table that contains customer information for each order (one per order) and a line table that contains item information (one or more per order).

I'm trying to update a field in the header table that contains the total value of any active orders from the order line table (active lines). My query is;

UPDATE oeheader SET oeheader.total_sales =
(SELECT SUM(Round(oelines.qty * oelines.price)))FROM oelines inner JOIN oeheader on oeheader.type = oelines.type AND oeheader.order = oelines.order
and oelines.active = 'Y') where oeheader.active = 'Y'

This works fine for one order - however if two or more orders are active, then the total_sales amount in each order becomes the total for ALL active lines. In other words my SUM line is adding up all active order lines, not just those for each specific active order header.

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.erpessentials.com

 

[bborissov]

What is your Header file PK name and Detail table FK?
You could try this (just replace PK and FK with real field names):

UPDATE oeheader 
      SET oeheader.total_sales = Tbl1.Sum
FROM oeheader
INNER JOIN (SELECT SUM(Round(oelines.qty * oelines.price))) AS Sum,
                   FK
            FROM oelines
            GROUP BY FK) Tbl1
       ON oeheader.PK = Tbl1.FK
WHERE oeheader.active = 'Y'

NOT TESTED!!!!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[crystalreporting]

Many thanks - that gave me the structure I needed and I was able to get it to work.

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.erpessentials.com