Re: Difficult SQL Statment

On 26 Jan 2006 01:36:09 -0800, lvpaul@xxxxxxx 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
.

Relevant Pages

  • Re: LEFT OUTER JOIN possible in DB2?
    ... i was trying to get a customer to create a view on their IBM thingy. ... Now, OS/400 comes with its own DBMS, which I assume is at a lower level that an RDBMS, like DB2 or SQL Server. ... > Turns out a LEFT OUTER join is sorta possible in IBM/DB2/AS/400. ...
    (microsoft.public.sqlserver.programming)
  • Re: Space required for an empty varchar field?
    ... >who don't understand how to deal with NULLS. ... as they'll be in the result set of an outer join. ... it gets unwieldy fast if you have multiplle NULLable columns. ... Thanks Hugo. ...
    (microsoft.public.sqlserver.programming)
  • Re: How best to meet these business requirements
    ... SET STATISTICS TIME ON ... LEFT OUTER JOIN #R ... "Hugo Kornelis" wrote: ... Is there a more elegent way to do this? ...
    (microsoft.public.sqlserver.server)
  • Re: Flattening Parent Child, an issue, please help
    ... AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN ... I am using SQL Server 2000 with SP4. ... For information about dyamic SQL from T-SQL see ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Flattening Parent Child, an issue, please help
    ... in future when all divisions would start using Project Module. ... AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN ... I am using SQL Server 2000 with SP4. ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)