Posted by Hugo Kornelis on 01/26/06 22:49

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

[Back to original message]