Solved

SQL Syntax update totals

Posted on 2013-06-13
5
382 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
ID: 39245392
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
ID: 39245771
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
ID: 39245941
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
ID: 39246498
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
ID: 39260424
Thanks for the tips
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

776 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