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 8 1511
On 25 Jan 2006 07:50:57 -0800, lv****@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, lv****@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 = 1. Tag vom nächsten Monat
set @dat1 =
dateadd(month,1,(CAST(CONVERT(char(8),CURRENT_TIME STAMP,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
On 27 Jan 2006 00:55:43 -0800, lv****@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, lv****@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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: e_AK_05 |
last post by:
I have a question and I can't figure out how to do it.
I have a select statment :
SELECT table1.* AS table1.*, table2.* AS table2.* FROM table1, table2
WHERE 1
this does not work...for me at...
|
by: Joe Saliba |
last post by:
Hi,
would like to know how to write a crosstable select statment in sql
server2000 where having:
- ItemNumber, ItemDescription, ItemColor, ItemSize as rows
- Stores as columns
- Qty * Netttc...
|
by: Appel |
last post by:
Hallo
i have a problem to add to string with a SQL statement.
My Table : Tablename NAMES:
| Name1 | Name2 | Name3 |
John Meyer
I search for a SQL statment that update Name3.
|
by: Maria |
last post by:
Hello
I have not used db2 in a long time, and I have this very long insert
script, which is giving me an error of duplicated inserts.
My question is how can I put something in the statment so...
|
by: 73blazer |
last post by:
Hello,
I'm looking for a way to make some of my insert templates more readable
by placing comments in between the values. I cannot seem to find a way
to do this with DB2, is there a way? I'm...
|
by: Me, Myself, and I |
last post by:
First off, i apologize if my terminology is off...
I am currently in a project that is basically a front-end to a
database. In coding this, I am taking into account that it has the
*potential*...
|
by: vsteshenko |
last post by:
Hello,
This is my second post to the any usernet group and the first one was
posted to the wrong one.
I am currently working on creating an order form for sales associates
at my work to be used...
|
by: huzzaa |
last post by:
I am using a switch statement that will decide from a random number what message to display.
I want the random number to be between 0 and 100 and then if the number is say between 1 and 10 to...
|
by: zeebiggie |
last post by:
Good morning
I have a form with the controls in the insert statment below. table1 has an Auto increment primary key hence is omitted in the insert statment and form. Am getting the error It didnt...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |