Difficult SQL Statment

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

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

Hi Hugo !

Thanks - it works fine.

INSERT INTO  POOL_LIEFERDAT(aufnr
 ,werk,lstueck,ldatum
)
SELECT a.aufnr,a.werk,a.voffenstueck -
coalesce(sum(l.lstueck),0),'31.12.2006'
FROM FAKT_AUFTRAG a
LEFT OUTER JOIN POOL_LIEFERDAT l
ON a.aufnr = l.aufnr and a.werk = l.werk
GROUP BY a.aufnr,a.werk,a.voffenstueck
HAVING a.voffenstueck > coalesce(sum(l.lstueck),0)

At the moment I am writing one record with the difference.

But in the future  I want to write each time 4 records with

Record1: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY
Record2: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 1
months
Record3: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 2
months
Record4: pieces = coalesce(sum(l.lstueck),0),/4 date = dateXY + 3
months

I want to distribute the virtual call offs over the next 4 month !

Is it possible to make this with SQL ??

Paul

I think I have to use a temporary table !

How can I define 4 variables with the date of the first day of the next
4 month ?

var1=1.2.06
var2=1.3.06
var3=1.4.06
var5=1.5.06

Thanks
aaapaul

On 26 Jan 2006 01:36:09 -0800, lvpaul@gmx.net wrote:

>Hi Hugo !
>
>Thanks - it works fine.
>
>INSERT INTO  POOL_LIEFERDAT(aufnr
 ,werk,lstueck,ldatum
)
>SELECT a.aufnr,a.werk,a.voffenstueck -
>coalesce(sum(l.lstueck),0),'31.12.2006'
>FROM FAKT_AUFTRAG a
>LEFT OUTER JOIN POOL_LIEFERDAT l
>ON a.aufnr = l.aufnr and a.werk = l.werk
>GROUP BY a.aufnr,a.werk,a.voffenstueck
>HAVING a.voffenstueck > coalesce(sum(l.lstueck),0)

Hi Paul,

Don't use locale-dependent date formats in your code. It will cause
unexpected things to happen when SQL Server misinterprets the date
format you intended. Use yyyymmdd (20061231).

>
>At the moment I am writing one record with the difference.
>
>But in the future  I want to write each time 4 records with
>
>Record1: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY
>Record2: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 1
>months
>Record3: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 2
>months
>Record4: pieces = coalesce(sum(l.lstueck),0),/4 date = dateXY + 3
>months
>
>I want to distribute the virtual call offs over the next 4 month !
>
>Is it possible to make this with SQL ??
>
>Paul

Yes, it's possible - and you don't need a temp table for it.

I'm not sure where dateXY comes from. Is that the date constant
(20061231) in the query above? Will it be a constant in the final query,
or is it taken from some other table?

Also - do you really want pieces to be a quarter of SUM(l.lstueck), or
should it be a quarter of a.voffenstueck - SUM(l.lstueck)?

Assuming that dateXY lives in the Aufträge table:

INSERT INTO  POOL_LIEFERDAT(aufnr
 ,werk,lstueck,ldatum
)
SELECT a.aufnr, a.werk,
(a.voffenstueck - coalesce(sum(l.lstueck),0)) / 4,
DATEADD(month, Numbers.N, a.dateXY)
FROM FAKT_AUFTRAG a
LEFT OUTER JOIN POOL_LIEFERDAT l
ON a.aufnr = l.aufnr and a.werk = l.werk
CROSS JOIN (SELECT 0 AS N
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3) AS Numbers
GROUP BY a.aufnr, a.werk, a.voffenstueck, Numbers.N
HAVING a.voffenstueck > coalesce(sum(l.lstueck),0)

(If you already have a numbers table, you can use that insted of the
derived table).

If dateXY is fixed, you can simply use a derived table with the four
precalculated dates instead of a numbers table and the DATEADD function.

NOTE: Queries are untested. See www.aspfaq.com/5006 if you prefer a
tested solution.

--
Hugo Kornelis, SQL Server MVP

Hallo Hugo !

Thank you. Its fine.

I read a very interesting article about sql and datetime at
www.insidesql.de. I will use the unseparated format in the future.

I will check your SQL-Statment.

Paul

All right now. Thank I have learned new possibilities:

Paul

My code:

declare @dat1 as datetime

-- dat1 =3D 1. Tag vom n=E4chsten Monat
set @dat1 =3D
 dateadd(month,1,(CAS
 T(CONVERT(char(8),CU
 RRENT_TIMESTAMP,112)
 as
datetime) -  Day(CURRENT_TIMESTAM
P)+1))


INSERT INTO  POOL_LIEFERDAT(aufnr
 ,ldatum,lstueck,werk
)
SELECT a. aufnr,dateadd(month,
numbers.n,@dat1) as ldatum,(a.voffenstueck
- coalesce(sum(l.lstueck),0))/4 as lstueck,a.werk
FROM FAKT_AUFTRAG a
LEFT OUTER JOIN POOL_LIEFERDAT l
ON a.aufnr =3D l.aufnr and a.werk =3D l.werk
CROSS JOIN
(SELECT 0 AS N
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3) AS NUMBERS
-- Achtung verursacht FEHLER   WHERE not(l.werk is null)
GROUP BY a.aufnr,a.werk,a. voffenstueck,numbers
.n
HAVING (a.voffenstueck > coalesce(sum(l.lstueck),0))
Order by a.aufnr

On 27 Jan 2006 00:55:43 -0800, lvpaul@gmx.net wrote:

>Hallo Hugo !
>
>Thank you. Its fine.
>
>I read a very interesting article about sql and datetime at
>www.insidesql.de. I will use the unseparated format in the future.
>
>I will check your SQL-Statment.
>
>Paul

Hi Paul,

I was going to point you to Tibor Karaszi's ultimate guide to the
datetime datatype, but I see that a German translation of it is
available at the insidesql site - probably exactly the article that
you're refering to!

--
Hugo Kornelis, SQL Server MVP

On 27 Jan 2006 06:22:41 -0800, lvpaul@gmx.net wrote:

>All right now. Thank I have learned new possibilities:
 (snip)
>-- Achtung verursacht FEHLER   WHERE not(l.werk is null)

Hi Paul,

What do you mean with the comment? Do you mean that you get errors with
the WHERE clause included, or that you get errors if you exclude it?

With this where clause, the LEFT OUTER JOIN is in effect reduced to an
INNER JOIN. This means that you will get correct information for rows
with at least one corresponding row in POOL_LIEFERDAT, but if you have a
FAKT_AUFTRAG with no rows yet in POOL_LIEFTERDAT, you won't get any rows
for it in the INSERT.

Removing the WHERE clause means that you'll also get four rows for each
FAKT_AUFTRAG with no corresponding POOL_LIEFERDAT.

Tchüß!

--
Hugo Kornelis, SQL Server MVP