row-by-row process

[Nipon]

Hi,
Please help.

I have 2 tables as followings:

CREATE TABLE [dbo].[Master] (
[masitemno] [char] (10) NOT NULL ,
[masqty] [decimal](10, 3) NOT NULL ,
[masunitcost] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Transaction] (
[transeqno] [int] NOT NULL ,
[tranitemno] [char] (10) NOT NULL ,
[tranqty] [decimal](10, 3) NOT NULL ,
[tranamount] [decimal](10, 2) NOT NULL ,
[tranunitcost] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Master] WITH NOCHECK ADD
CONSTRAINT [PK_Master] PRIMARY KEY NONCLUSTERED
(
[masitemno]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Transaction] WITH NOCHECK ADD
CONSTRAINT [PK_Transaction] PRIMARY KEY NONCLUSTERED
(
[transeqno]
) ON [PRIMARY]
GO

Table "Transaction" has about 1,000,000 (one million rows) and Table
"Master" has about 500,000 rows.
I have to update "MASTER" table with "TRANSACTION" table with
row-by-row processing basis sorting by
primary key TRNSEQNO column.

Sometimes TRANSACTION can explicitly SET "MASQTY" and "MASUNITCOST"
columns (TRANUNITCOST<>0) of MASTER
which linked by itemno and after that AMOUNT column of next row of
TRANSACTION will used this
new UNITCOST of MASTER as followed statements.

-------------------------------------------------
declare @count int, @max int
set @count=1
set @max = (select max(seqno) from transaction(nolock)

while @count<=@max
begin
update TRANSACTION
set TRANAMOUNT = TRANQTY * (select MASUNITCOST from MASTER
where MASITEMNO=TRANITEMNO)
where TRANSEQNO = @count
and TRANUNITCOST = 0

update MASTER
set MASQTY = MASQTY + TRANQTY
from TRANSACTION
where TRANSEQNO = @count
and TRANUNITCOST = 0
and MASITEMNO=TRANITEMNO

update TRANSACTION
set TRANAMOUNT = TRANQTY * TRANUNITCOST
where TRANSEQNO = @count
and TRANUNITCOST <> 0

update MASTER
set MASQTY = MASQTY + TRANQTY,
MASUNITCOST = TRANUNITCOST
from TRANSACTION
where TRANSEQNO = @count
and TRANUNITCOST <> 0
and MASITEMNO=TRANITEMNO

set @count = @count +1
end
-------------------------------------------------

The above sample statements take me more than 10 hrs. (I quit before
actually done) with MS SQL SERVER 7.5 SP4.
on WIN2K SERVER (2 XEON PROCESSORS, 1GB MEM.). I tried to use trigger
but result is not correct.

Please advise on shorten running time (in minutes , maybe) and better
performance.

Thank you and appreciate any suggestions

Nipon Wongtrakul

[Hugo Kornelis]

On 17 Aug 2004 06:25:30 -0700, Nipon wrote:

Hi,
Please help.

I have 2 tables as followings:

CREATE TABLE [dbo].[Master] (
[masitemno] [char] (10) NOT NULL ,
[masqty] [decimal](10, 3) NOT NULL ,
[masunitcost] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Transaction] (
[transeqno] [int] NOT NULL ,
[tranitemno] [char] (10) NOT NULL ,
[tranqty] [decimal](10, 3) NOT NULL ,
[tranamount] [decimal](10, 2) NOT NULL ,
[tranunitcost] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Master] WITH NOCHECK ADD
CONSTRAINT [PK_Master] PRIMARY KEY NONCLUSTERED
(
[masitemno]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Transaction] WITH NOCHECK ADD
CONSTRAINT [PK_Transaction] PRIMARY KEY NONCLUSTERED
(
[transeqno]
) ON [PRIMARY]
GO

Table "Transaction" has about 1,000,000 (one million rows) and Table
"Master" has about 500,000 rows.
I have to update "MASTER" table with "TRANSACTION" table with
row-by-row processing basis sorting by
primary key TRNSEQNO column.

Sometimes TRANSACTION can explicitly SET "MASQTY" and "MASUNITCOST"
columns (TRANUNITCOST<>0) of MASTER
which linked by itemno and after that AMOUNT column of next row of
TRANSACTION will used this
new UNITCOST of MASTER as followed statements.

-------------------------------------------------
declare @count int, @max int
set @count=1
set @max = (select max(seqno) from transaction(nolock)

while @count<=@max
begin
update TRANSACTION
set TRANAMOUNT = TRANQTY * (select MASUNITCOST from MASTER
where MASITEMNO=TRANITEMNO)
where TRANSEQNO = @count
and TRANUNITCOST = 0

update MASTER
set MASQTY = MASQTY + TRANQTY
from TRANSACTION
where TRANSEQNO = @count
and TRANUNITCOST = 0
and MASITEMNO=TRANITEMNO

update TRANSACTION
set TRANAMOUNT = TRANQTY * TRANUNITCOST
where TRANSEQNO = @count
and TRANUNITCOST <> 0

update MASTER
set MASQTY = MASQTY + TRANQTY,
MASUNITCOST = TRANUNITCOST
from TRANSACTION
where TRANSEQNO = @count
and TRANUNITCOST <> 0
and MASITEMNO=TRANITEMNO

set @count = @count +1
end
-------------------------------------------------

The above sample statements take me more than 10 hrs. (I quit before
actually done) with MS SQL SERVER 7.5 SP4.
on WIN2K SERVER (2 XEON PROCESSORS, 1GB MEM.). I tried to use trigger
but result is not correct.

Please advise on shorten running time (in minutes , maybe) and better
performance.

Thank you and appreciate any suggestions

Nipon Wongtrakul

Hi Nipon,

Wow. You seem to have gotten yourself in a whole lot of trouble by
choosing this design. I'm trying to figure out what the dependencies in
your situation actually are and how a normalized version of your tables
would look like, but I have to give, due to lack of knowledge of the real
needs of your employer.

I've tried to come up with a set-based approach to what you're doing. You
didn't provide sample data that I could use to test it on, so I'm not sure
if it will really do the same as your procedural code. However, I'm quite
sure that it'll run lots quicker :-)

It might be even more quicker if you make your primary keys clustered.
Another possible improvement is creating an additional (nonunique) index
on transaction.tranitemno, but I'm not sure; your execution plan should
show if it's used or not. If you do, then you might also try if making
that index clustered instead of the primary key is better.

I did test my query to check that it will execute okay, but since I didn't
have sample data, the check was done on empty tables. I had to rename the
table Transaction to Trans, since transaction is a reserved word. If you
change the table names on posting your problem, please do check that the
code still executes okay (there were some other minor issues as well, like
a misspelled column name in the code you supplied).

Anyway, here is the code. Sorry for the lousy formatting; that's my news
software cutting long lines into pieces <g>

-- Step 1: Recalculate tranamount.
-- Use qty and cost from transaction;
-- if no cost in transaction, use cost from "last" previous
transaction
-- with cost, or cost from master if no cost exists in previous
transactions.
UPDATE Trans
SET tranamount = tranamount *
CASE
WHEN tranunitcost <> 0 THEN tranunitcost
ELSE COALESCE((SELECT T1.tranunitcost
FROM Trans AS T1
WHERE T1.tranitemno = (SELECT
MAX(T2.tranitemno)
FROM Trans AS T2
WHERE T2.tranitemno =
Trans.tranitemno
AND T2.transeqno <
Trans.transeqno
AND T2.tranunitcost
<> 0)),
(SELECT masunitcost
FROM Master
WHERE Master.masitemno = Trans.tranitemno))
END

-- Step 2: Recalculate masqty and possibly masunitcost.
-- * masqty is simply increased by sum of all tranqty
-- * masunitcost is set to "last" tranunitcost,
-- or left unchanged if no transaction has tranunitcost.
UPDATE Master
SET masqty = masqty + (SELECT SUM(T0.tranqty)
FROM Trans AS T0
WHERE T0.tranitemno = Master.masitemno),
masunitcost = COALESCE((SELECT T1.tranunitcost
FROM Trans AS T1
WHERE T1.tranitemno = (SELECT
MAX(T2.tranitemno)
FROM Trans AS T2
WHERE
T2.tranitemno = Master.masitemno
AND
T2.tranunitcost <> 0)), Master.masunitcost)
FROM Master

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Nipon]

Hi, Hugo

I test your statements, but the result is still not correct.

INSERT INTO MAS
SELECT 'AAAAA',100.000,10.00
INSERT INTO MAS
SELECT 'BBBBB',200.000,15.00

INSERT INTO TRANS
SELECT 1,'AAAAA',.000,.00,20.00
INSERT INTO TRANS
SELECT 2,'BBBBB',30.000,.00,.00
INSERT INTO TRANS
SELECT 3,'AAAAA',20.000,.00,.00
As you can see the new unitcost of itemno 'AAAAA' must be
MAS.UNITCOST + TRANS.UNITCOST = 30.00 (10.00+20.00) not 20.00
after pass the 1st transaction. So your subquery

COALESCE((SELECT T1.tranunitcost FROM Trans AS T1
WHERE T1.tranitemno =
(SELECT MAX(T2.tranitemno) FROM Trans AS T2
WHERE T2.tranitemno = Trans.tranitemno
AND T2.transeqno < Trans.transeqno
AND T2.tranunitcost <> 0)),
(SELECT masunitcost FROM Mas
WHERE Mas.masitemno = Trans.tranitemno))

will get 20.00 (not 30.00 from MAS.UNITCOST)while in the 3rd transaction.

However, thank you so much for your kindess

Best Regards
Nipon

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<uf********************************@4ax.com>. ..

On 17 Aug 2004 06:25:30 -0700, Nipon wrote:

Hi,
Please help.

I have 2 tables as followings:

CREATE TABLE [dbo].[Master] (
[masitemno] [char] (10) NOT NULL ,
[masqty] [decimal](10, 3) NOT NULL ,
[masunitcost] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Transaction] (
[transeqno] [int] NOT NULL ,
[tranitemno] [char] (10) NOT NULL ,
[tranqty] [decimal](10, 3) NOT NULL ,
[tranamount] [decimal](10, 2) NOT NULL ,
[tranunitcost] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Master] WITH NOCHECK ADD
CONSTRAINT [PK_Master] PRIMARY KEY NONCLUSTERED
(
[masitemno]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Transaction] WITH NOCHECK ADD
CONSTRAINT [PK_Transaction] PRIMARY KEY NONCLUSTERED
(
[transeqno]
) ON [PRIMARY]
GO

Table "Transaction" has about 1,000,000 (one million rows) and Table
"Master" has about 500,000 rows.
I have to update "MASTER" table with "TRANSACTION" table with
row-by-row processing basis sorting by
primary key TRNSEQNO column.

Sometimes TRANSACTION can explicitly SET "MASQTY" and "MASUNITCOST"
columns (TRANUNITCOST<>0) of MASTER
which linked by itemno and after that AMOUNT column of next row of
TRANSACTION will used this
new UNITCOST of MASTER as followed statements.

-------------------------------------------------
declare @count int, @max int
set @count=1
set @max = (select max(seqno) from transaction(nolock)

while @count<=@max
begin
update TRANSACTION
set TRANAMOUNT = TRANQTY * (select MASUNITCOST from MASTER
where MASITEMNO=TRANITEMNO)
where TRANSEQNO = @count
and TRANUNITCOST = 0

update MASTER
set MASQTY = MASQTY + TRANQTY
from TRANSACTION
where TRANSEQNO = @count
and TRANUNITCOST = 0
and MASITEMNO=TRANITEMNO

update TRANSACTION
set TRANAMOUNT = TRANQTY * TRANUNITCOST
where TRANSEQNO = @count
and TRANUNITCOST <> 0

update MASTER
set MASQTY = MASQTY + TRANQTY,
MASUNITCOST = TRANUNITCOST
from TRANSACTION
where TRANSEQNO = @count
and TRANUNITCOST <> 0
and MASITEMNO=TRANITEMNO

set @count = @count +1
end
-------------------------------------------------

The above sample statements take me more than 10 hrs. (I quit before
actually done) with MS SQL SERVER 7.5 SP4.
on WIN2K SERVER (2 XEON PROCESSORS, 1GB MEM.). I tried to use trigger
but result is not correct.

Please advise on shorten running time (in minutes , maybe) and better
performance.

Thank you and appreciate any suggestions

Nipon Wongtrakul

Hi Nipon,

Wow. You seem to have gotten yourself in a whole lot of trouble by
choosing this design. I'm trying to figure out what the dependencies in
your situation actually are and how a normalized version of your tables
would look like, but I have to give, due to lack of knowledge of the real
needs of your employer.

I've tried to come up with a set-based approach to what you're doing. You
didn't provide sample data that I could use to test it on, so I'm not sure
if it will really do the same as your procedural code. However, I'm quite
sure that it'll run lots quicker :-)

It might be even more quicker if you make your primary keys clustered.
Another possible improvement is creating an additional (nonunique) index
on transaction.tranitemno, but I'm not sure; your execution plan should
show if it's used or not. If you do, then you might also try if making
that index clustered instead of the primary key is better.

I did test my query to check that it will execute okay, but since I didn't
have sample data, the check was done on empty tables. I had to rename the
table Transaction to Trans, since transaction is a reserved word. If you
change the table names on posting your problem, please do check that the
code still executes okay (there were some other minor issues as well, like
a misspelled column name in the code you supplied).

Anyway, here is the code. Sorry for the lousy formatting; that's my news
software cutting long lines into pieces <g>

-- Step 1: Recalculate tranamount.
-- Use qty and cost from transaction;
-- if no cost in transaction, use cost from "last" previous
transaction
-- with cost, or cost from master if no cost exists in previous
transactions.
UPDATE Trans
SET tranamount = tranamount *
CASE
WHEN tranunitcost <> 0 THEN tranunitcost
ELSE COALESCE((SELECT T1.tranunitcost
FROM Trans AS T1
WHERE T1.tranitemno = (SELECT
MAX(T2.tranitemno)
FROM Trans AS T2
WHERE T2.tranitemno =
Trans.tranitemno
AND T2.transeqno <
Trans.transeqno
AND T2.tranunitcost
<> 0)),
(SELECT masunitcost
FROM Master
WHERE Master.masitemno = Trans.tranitemno))
END

-- Step 2: Recalculate masqty and possibly masunitcost.
-- * masqty is simply increased by sum of all tranqty
-- * masunitcost is set to "last" tranunitcost,
-- or left unchanged if no transaction has tranunitcost.
UPDATE Master
SET masqty = masqty + (SELECT SUM(T0.tranqty)
FROM Trans AS T0
WHERE T0.tranitemno = Master.masitemno),
masunitcost = COALESCE((SELECT T1.tranunitcost
FROM Trans AS T1
WHERE T1.tranitemno = (SELECT
MAX(T2.tranitemno)
FROM Trans AS T2
WHERE
T2.tranitemno = Master.masitemno
AND
T2.tranunitcost <> 0)), Master.masunitcost)
FROM Master

Best, Hugo

[Nipon]

Hi, Hugo

I test your statements, but the result is still not correct.

INSERT INTO MAS
SELECT 'AAAAA',100.000,10.00
INSERT INTO MAS
SELECT 'BBBBB',200.000,15.00

INSERT INTO TRANS
SELECT 1,'AAAAA',.000,.00,20.00
INSERT INTO TRANS
SELECT 2,'BBBBB',30.000,.00,.00
INSERT INTO TRANS
SELECT 3,'AAAAA',20.000,.00,.00
As you can see the new unitcost of itemno 'AAAAA' must be
MAS.UNITCOST + TRANS.UNITCOST = 30.00 (10.00+20.00) not 20.00
after pass the 1st transaction. So your subquery

COALESCE((SELECT T1.tranunitcost FROM Trans AS T1
WHERE T1.tranitemno =
(SELECT MAX(T2.tranitemno) FROM Trans AS T2
WHERE T2.tranitemno = Trans.tranitemno
AND T2.transeqno < Trans.transeqno
AND T2.tranunitcost <> 0)),
(SELECT masunitcost FROM Mas
WHERE Mas.masitemno = Trans.tranitemno))

will get 20.00 (not 30.00 from MAS.UNITCOST)while in the 3rd transaction.

However, thank you so much for your kindess

Best Regards
Nipon
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<uf********************************@4ax.com>. ..

On 17 Aug 2004 06:25:30 -0700, Nipon wrote:

Hi,
Please help.

I have 2 tables as followings:

CREATE TABLE [dbo].[Master] (
[masitemno] [char] (10) NOT NULL ,
[masqty] [decimal](10, 3) NOT NULL ,
[masunitcost] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Transaction] (
[transeqno] [int] NOT NULL ,
[tranitemno] [char] (10) NOT NULL ,
[tranqty] [decimal](10, 3) NOT NULL ,
[tranamount] [decimal](10, 2) NOT NULL ,
[tranunitcost] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Master] WITH NOCHECK ADD
CONSTRAINT [PK_Master] PRIMARY KEY NONCLUSTERED
(
[masitemno]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Transaction] WITH NOCHECK ADD
CONSTRAINT [PK_Transaction] PRIMARY KEY NONCLUSTERED
(
[transeqno]
) ON [PRIMARY]
GO

Table "Transaction" has about 1,000,000 (one million rows) and Table
"Master" has about 500,000 rows.
I have to update "MASTER" table with "TRANSACTION" table with
row-by-row processing basis sorting by
primary key TRNSEQNO column.

Sometimes TRANSACTION can explicitly SET "MASQTY" and "MASUNITCOST"
columns (TRANUNITCOST<>0) of MASTER
which linked by itemno and after that AMOUNT column of next row of
TRANSACTION will used this
new UNITCOST of MASTER as followed statements.

-------------------------------------------------
declare @count int, @max int
set @count=1
set @max = (select max(seqno) from transaction(nolock)

while @count<=@max
begin
update TRANSACTION
set TRANAMOUNT = TRANQTY * (select MASUNITCOST from MASTER
where MASITEMNO=TRANITEMNO)
where TRANSEQNO = @count
and TRANUNITCOST = 0

update MASTER
set MASQTY = MASQTY + TRANQTY
from TRANSACTION
where TRANSEQNO = @count
and TRANUNITCOST = 0
and MASITEMNO=TRANITEMNO

update TRANSACTION
set TRANAMOUNT = TRANQTY * TRANUNITCOST
where TRANSEQNO = @count
and TRANUNITCOST <> 0

update MASTER
set MASQTY = MASQTY + TRANQTY,
MASUNITCOST = TRANUNITCOST
from TRANSACTION
where TRANSEQNO = @count
and TRANUNITCOST <> 0
and MASITEMNO=TRANITEMNO

set @count = @count +1
end
-------------------------------------------------

The above sample statements take me more than 10 hrs. (I quit before
actually done) with MS SQL SERVER 7.5 SP4.
on WIN2K SERVER (2 XEON PROCESSORS, 1GB MEM.). I tried to use trigger
but result is not correct.

Please advise on shorten running time (in minutes , maybe) and better
performance.

Thank you and appreciate any suggestions

Nipon Wongtrakul

Hi Nipon,

Wow. You seem to have gotten yourself in a whole lot of trouble by
choosing this design. I'm trying to figure out what the dependencies in
your situation actually are and how a normalized version of your tables
would look like, but I have to give, due to lack of knowledge of the real
needs of your employer.

I've tried to come up with a set-based approach to what you're doing. You
didn't provide sample data that I could use to test it on, so I'm not sure
if it will really do the same as your procedural code. However, I'm quite
sure that it'll run lots quicker :-)

It might be even more quicker if you make your primary keys clustered.
Another possible improvement is creating an additional (nonunique) index
on transaction.tranitemno, but I'm not sure; your execution plan should
show if it's used or not. If you do, then you might also try if making
that index clustered instead of the primary key is better.

I did test my query to check that it will execute okay, but since I didn't
have sample data, the check was done on empty tables. I had to rename the
table Transaction to Trans, since transaction is a reserved word. If you
change the table names on posting your problem, please do check that the
code still executes okay (there were some other minor issues as well, like
a misspelled column name in the code you supplied).

Anyway, here is the code. Sorry for the lousy formatting; that's my news
software cutting long lines into pieces <g>

-- Step 1: Recalculate tranamount.
-- Use qty and cost from transaction;
-- if no cost in transaction, use cost from "last" previous
transaction
-- with cost, or cost from master if no cost exists in previous
transactions.
UPDATE Trans
SET tranamount = tranamount *
CASE
WHEN tranunitcost <> 0 THEN tranunitcost
ELSE COALESCE((SELECT T1.tranunitcost
FROM Trans AS T1
WHERE T1.tranitemno = (SELECT
MAX(T2.tranitemno)
FROM Trans AS T2
WHERE T2.tranitemno =
Trans.tranitemno
AND T2.transeqno <
Trans.transeqno
AND T2.tranunitcost
<> 0)),
(SELECT masunitcost
FROM Master
WHERE Master.masitemno = Trans.tranitemno))
END

-- Step 2: Recalculate masqty and possibly masunitcost.
-- * masqty is simply increased by sum of all tranqty
-- * masunitcost is set to "last" tranunitcost,
-- or left unchanged if no transaction has tranunitcost.
UPDATE Master
SET masqty = masqty + (SELECT SUM(T0.tranqty)
FROM Trans AS T0
WHERE T0.tranitemno = Master.masitemno),
masunitcost = COALESCE((SELECT T1.tranunitcost
FROM Trans AS T1
WHERE T1.tranitemno = (SELECT
MAX(T2.tranitemno)
FROM Trans AS T2
WHERE
T2.tranitemno = Master.masitemno
AND
T2.tranunitcost <> 0)), Master.masunitcost)
FROM Master

Best, Hugo

[Hugo Kornelis]

On 19 Aug 2004 23:46:05 -0700, Nipon wrote:

Hi, Hugo

I test your statements, but the result is still not correct.

INSERT INTO MAS
SELECT 'AAAAA',100.000,10.00
INSERT INTO MAS
SELECT 'BBBBB',200.000,15.00

INSERT INTO TRANS
SELECT 1,'AAAAA',.000,.00,20.00
INSERT INTO TRANS
SELECT 2,'BBBBB',30.000,.00,.00
INSERT INTO TRANS
SELECT 3,'AAAAA',20.000,.00,.00
As you can see the new unitcost of itemno 'AAAAA' must be
MAS.UNITCOST + TRANS.UNITCOST = 30.00 (10.00+20.00) not 20.00
after pass the 1st transaction. So your subquery

COALESCE((SELECT T1.tranunitcost FROM Trans AS T1
WHERE T1.tranitemno =
(SELECT MAX(T2.tranitemno) FROM Trans AS T2
WHERE T2.tranitemno = Trans.tranitemno
AND T2.transeqno < Trans.transeqno
AND T2.tranunitcost <> 0)),
(SELECT masunitcost FROM Mas
WHERE Mas.masitemno = Trans.tranitemno))

will get 20.00 (not 30.00 from MAS.UNITCOST)while in the 3rd transaction.

However, thank you so much for your kindess

Hi Nipon,

I used your sample data to test my queries as well. There were some
corrections I had to make. I first got an error because the subquery
returned two many rows; to solve that, I had to change
WHERE T1.tranitemno = (SELECT MAX(T2.tranitemno)
to
WHERE T1.transeqno = (SELECT MAX(T2.transeqno)
in two places.

After that, I got no error but the tranamount was not calculated; I fixed
that by changing
SET tranamount = tranamount *
to
SET tranamount = tranqty *

The query now runs and returns the same results as the code you posted in
the start of this discussion.

I don't understand that you expect to get 30.00 from Master.unitcost in
the 3rd transaction. Both your code and my code set Master.unitcost for
item to 20.00 and both your and my code use this value of 20.00 to
cancluate the tranamount of the 3rd transaction. So if this is wrong, your
own code is wrong as well.

I can fix this. But before I take the time to change the code, I want a
clear confirmation from you that this is indeed what you want. I can
understand that you want the master QUANTITY to be equal to the sum of all
transaction quantities plus the starting master quantity, but I'd be very
surprised if you really want the master COST to be equal to the starting
cost plus all transaction costs! Setting the master cost equal to the
transaction cost of the last transaction makes a lot more sense (and is
what your row-by-row code actually does!). Of course, thhere are many more
things in your design that surprised me, so it's possible that this is
indeed what you want - but I want an explicit confirmation before I'll
spend time on changing the query.

So to recap: should the value in Master.unitCOST (not quantity!)
be equal to:
a) the tranunitcost of the last individual transaction that has a
tranunitcost not equal to 0, or
b) the sum of the "old" cost PLUS the sum of all tranunitcosts in the
individual transactions.

Let me know. Then, I'll work on your code some more.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Nipon]

Hi, Hugo

Glad to hear from you so quick.

a should be close to my answer to your question.

The value of MASTER.UNITCOST must be 30.00 , because when
TRANS.UNITCOST <> 0 means that I have to add (TRANS.UNITCOST is signed
and can be < 0) that value to the MASTER.UNITCOST so that the new
MAS.UNITCOST will be 30.00 after the 1st transaction , but the MAS.QTY
will not be increased because of value 0.00 of 1st TRANS.QTY. Then
when reach the 3rd transaction which is 'AAAAA' and the
TRANS.UNITCOST=0, TRANS.AMT will be updated with 20.00 (TRANS.QTY) X
30.00 (new MAS.UNITCOST) and MAS.QTY will be 100.000 (MAS.QTY) + 20.00
(3rd TRANS.QTY) = 120.
But if I have transaction#4 which looks like
'AAAAA',10.00,0.00,-15.00 ,
the MAS.QTY will be 120.000 + 10.00 (4th transaction QTY) and
MAS.UNITCOST will equal 30.00 +(-15) = 15. So, after the 4th
transaction, the next transaction that has itemno='AAAAA' and
UNITCOST=0 will use 15 (MAS.UNITCOST) ... so on

Now I'm trying to re-write my SQL statment by using a new table which
is joined table of MAS and TRANS tables.
Thank you
Best Regards
Nipon

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<i4********************************@4ax.com>. ..

On 19 Aug 2004 23:46:05 -0700, Nipon wrote:

Hi, Hugo

I test your statements, but the result is still not correct.

INSERT INTO MAS
SELECT 'AAAAA',100.000,10.00
INSERT INTO MAS
SELECT 'BBBBB',200.000,15.00

INSERT INTO TRANS
SELECT 1,'AAAAA',.000,.00,20.00
INSERT INTO TRANS
SELECT 2,'BBBBB',30.000,.00,.00
INSERT INTO TRANS
SELECT 3,'AAAAA',20.000,.00,.00
As you can see the new unitcost of itemno 'AAAAA' must be
MAS.UNITCOST + TRANS.UNITCOST = 30.00 (10.00+20.00) not 20.00
after pass the 1st transaction. So your subquery

COALESCE((SELECT T1.tranunitcost FROM Trans AS T1
WHERE T1.tranitemno =
(SELECT MAX(T2.tranitemno) FROM Trans AS T2
WHERE T2.tranitemno = Trans.tranitemno
AND T2.transeqno < Trans.transeqno
AND T2.tranunitcost <> 0)),
(SELECT masunitcost FROM Mas
WHERE Mas.masitemno = Trans.tranitemno))

will get 20.00 (not 30.00 from MAS.UNITCOST)while in the 3rd transaction.

However, thank you so much for your kindess

Hi Nipon,

I used your sample data to test my queries as well. There were some
corrections I had to make. I first got an error because the subquery
returned two many rows; to solve that, I had to change
WHERE T1.tranitemno = (SELECT MAX(T2.tranitemno)
to
WHERE T1.transeqno = (SELECT MAX(T2.transeqno)
in two places.

After that, I got no error but the tranamount was not calculated; I fixed
that by changing
SET tranamount = tranamount *
to
SET tranamount = tranqty *

The query now runs and returns the same results as the code you posted in
the start of this discussion.

I don't understand that you expect to get 30.00 from Master.unitcost in
the 3rd transaction. Both your code and my code set Master.unitcost for
item to 20.00 and both your and my code use this value of 20.00 to
cancluate the tranamount of the 3rd transaction. So if this is wrong, your
own code is wrong as well.

I can fix this. But before I take the time to change the code, I want a
clear confirmation from you that this is indeed what you want. I can
understand that you want the master QUANTITY to be equal to the sum of all
transaction quantities plus the starting master quantity, but I'd be very
surprised if you really want the master COST to be equal to the starting
cost plus all transaction costs! Setting the master cost equal to the
transaction cost of the last transaction makes a lot more sense (and is
what your row-by-row code actually does!). Of course, thhere are many more
things in your design that surprised me, so it's possible that this is
indeed what you want - but I want an explicit confirmation before I'll
spend time on changing the query.

So to recap: should the value in Master.unitCOST (not quantity!)
be equal to:
a) the tranunitcost of the last individual transaction that has a
tranunitcost not equal to 0, or
b) the sum of the "old" cost PLUS the sum of all tranunitcosts in the
individual transactions.

Let me know. Then, I'll work on your code some more.

Best, Hugo