Posted by Hugo Kornelis on 01/25/06 22:01

On 25 Jan 2006 07:50:57 -0800, lvpaul@gmx.net wrote:

>Hello !
>
>I habe 2 Tables
>
>Table1: Orders
>Fields: Ordernr, Opieces
>
>Table2: Calloffs
>Ordernr, Cpieces
>
>In Table1 ordernr is primary key.
>In Table2 the same ordernr can exist often
>
>My problem
>If the sum(Cpieces) < Opieces:
>I have to create a new virtual calloff
>with Cpieces = opieces - sum(cpieces)
>
>Its too high for me.
>
>Please help
>
>Best regards
>aaapaul

Hi aaapaul,

Maybe something like this?

INSERT INTO Calloffs (Ordernr, Cpieces)
SELECT o.Ordernr, o.Opieces - COALESCE(SUM(c.CPieces), 0)
FROM Orders AS o
LEFT JOIN Calloffs AS c
ON c.Ordernr = o.Ordernr
GROUP BY o.Ordernr, o.Opieces
HAVING o.Opieces > COALESCE(SUM(c.CPieces), 0)

(untested - see www.aspfaq.com/5006 if you prefer a tested reply or if
this doesn't do what you want)

--
Hugo Kornelis, SQL Server MVP

[Back to original message]