Solved

SQL Syntax update totals

Posted on 2013-06-13
5
379 Views
Last Modified: 2013-06-19
I have a situation where I need to update header records based on values in detail records.
In this case there are  PO header and detail tables.
POHEADER.PONUMBER
POHEADER.TOTAL

The detail table has
PODETAIL.PONUMBER
PODETAIL.POLINE
PODETAIL.QUANTITY
PODETAIL.UNITCOST

I need to do a mass update of the POHEADER.TOTAL field based on the sum of the details lines on the corrresponding PO.  So if PO number 1000 has two lines with quantities of 2 and 4 and costs of $50 and $100, respectively. The total cost based on the detail is 2 x 50 and 4 x 100 for a total of $100 + $400 = $500. I need POHEADER.TOTAL to equal $500
0
Comment
Question by:rwheeler23
5 Comments
 
LVL 3

Assisted Solution

by:tsnirone
tsnirone earned 125 total points
Comment Utility
Been quite a few years since I've dwelved into sql but I think this should work:

select PONUMBER, sum(UNITCOST)*QUANTITY SUMA
from PODETAIL
into #tmp1
group by PONUMBER
go
update POHEADER
from POHEADER A, #tmp1 B
set A.TOTAL=B.SUMA
where A.PONUMBER = B.PONUMBER
go

HTH :)
0
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 250 total points
Comment Utility
You can also try:

update POHEADER
set TOTAL = PD.TOTAL 
from
	POHEADER as PH
	inner join
	(
		select
			PONUMBER,
			sum( quantity * unitCost ) as TOTAL
		from
			PODETAIL
		group by
		  PONUMBER
	) as PD on PH.PONUMBER = PD.PONUMBER;

Open in new window


I like this question =)  Hope it helps!

If anyone wants to play with this: http://sqlfiddle.com/#!3/d5f8d/1/0
0
 

Author Comment

by:rwheeler23
Comment Utility
That SQL Fiddle thing is cool. I did find that this works too! Thanks for the tips.

update POHEADER
set SUBTOTAL= (select sum(extdcost) from PODETAIL where ponumber=p2.ponumber)
from POHEADER p2
where (select sum(extdcost) from PODETAIL where ponumber=p2.ponumber)  is not null

Apparently there were some PO Header line that had no matching PO Detail lines hence the check for NULL.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
Comment Utility
from a query efficiency standpoint, doing one summing subquery as proposed by didnthaveaname, would be my recommendation. This tweak is another way to deal with the nulls:
/* zero if no details found */
UPDATE POHEADER
SET TOTAL = isnull(PD.TOTAL, 0) -- changed here, assumes you want zero if no details
FROM POHEADER AS PH
LEFT JOIN -- changed here
          (
          SELECT
               PONUMBER
             , sum(quantity * unitCost) AS TOTAL
          FROM PODETAIL
          GROUP BY PONUMBER
          ) AS PD ON PH.PONUMBER = PD.PONUMBER; 

Open in new window

{+ sqlfiddle now up (it was down), added a non-matching poheader (9999) see http://sqlfiddle.com/#!3/dbb12/1 }
0
 

Author Closing Comment

by:rwheeler23
Comment Utility
Thanks for the tips
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now