Dale Magnant (2/27/2014)
...(By the way, the last line of query assigned an alias of 'x', I assume you meant 'p' and changed the line accordingly.)
Yep, sorry, my mistake.
Dale Magnant (2/27/2014)
Chris,After some further experimentation I appear to have a working solution as shown below. Basically I adopted an inner query that I had tried from a previous attempt to the outer query of your suggestion. In the testing I've done so far it has calculated all the sums correctly. I'll do a bit of further testing tomorrow, but this seems to be a working solution.
Thanks again,
UPDATE m Set
POObligations = p.POTotal
FROM MDEP m
CROSS APPLY (
Select sum((D.LineItemTotal - M1.PaidToDate) * M2.SplitPercent) As POTotal from
(
(select PONumber, MDEP, PaymentPosted as PaidToDate from PO_MX) AS M1
inner join
(select PONumber, cast(PrimarySplit as decimal(5,2))/100 as SplitPercent from PO_MX) AS M2 on M1.PONumber = M2.PONumber
inner join
(select PONumber, SUM(isnull(QuantityOrdered,0) * isnull(Price,0)) as LineItemTotal from PO_DX group by PONumber) AS D on M2.PONumber = D.PONumber
)
Where M1.MDEP = m.MDEP
) p
I think you can simplify this to the following:
UPDATE m SET
POObligations = p.POTotal
FROM MDEP m
CROSS APPLY (
SELECT
POTotal = SUM((dx.LineItemTotal - mx.PaidToDate) * CAST(mx.PrimarySplit AS DECIMAL(5,2))/100)
FROM PO_MX mx
INNER JOIN (
SELECT
PONumber,
LineItemTotal = ISNULL(SUM(QuantityOrdered * Price),0)
FROM PO_DX
GROUP BY PONumber
) dx
ON mx.PONumber = dx.PONumber
WHERE mx.MDEP = m.MDEP
) p
- because neither of the two subselects of PO_MX perform any aggregation.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden