Posted by lvpaul on 10/01/61 11:38

All right now. Thank I have learned new possibilities:

Paul

My code:

declare @dat1 as datetime

-- dat1 = 1. Tag vom nächsten Monat
set @dat1 =
dateadd(month,1,(CAST(CONVERT(char(8),CURRENT_TIMESTAMP,112) as
datetime) - Day(CURRENT_TIMESTAMP)+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 = 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
-- 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

• Next in forum: Re: New bie
• Prev in forum: Views vs Stored Procedures, whats the difference?
• Thread view: Re: Difficult SQL Statment

[Reply to this message]