row-by-row process

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 byitemno 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 WongtrakulOn 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 byitemno 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)|||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:<uf34i0liee1jt26l41sg492taiclnmgfft@.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 byitemno 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|||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:<uf34i0liee1jt26l41sg492taiclnmgfft@.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 byitemno 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|||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)|||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:<i4bbi09sv7udlbb01j2n3dopmu6gu2hlnj@.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

Row compare failure and Process 3

Hello:
We are on SQL Server 7.00.1063 (SP4) and Windows 2000 5.00.2195 (SP4).
I had to reboot our server this AM as SQL Server was not responding. It
seems to be fine, now.
Afterward, I reviewed the Windows 2000 Server Log in Event Viewer and ran
across a couple of things. Could someone please let me know if there is
something that I should do based on these statements, in hopes of preventing
this problem from happening again?
For last Friday, a message says Process 3 generated fatal exception
(EXCEPTION_ACCESS_VIOLATION). For last Sunday, a message says "Row compare
failure".
I'm not sure on these, but if someone could shed some insight, that would be
great!
Thanks!!!
childofthe1980s
There could be a memory leak issue if you use BULK INSERT.
If this is the case use BCP instead to avoid it.
There is more information:
http://support.microsoft.com/default...b;en-us;246824
Regards.
"childofthe1980s" wrote:

> Hello:
> We are on SQL Server 7.00.1063 (SP4) and Windows 2000 5.00.2195 (SP4).
> I had to reboot our server this AM as SQL Server was not responding. It
> seems to be fine, now.
> Afterward, I reviewed the Windows 2000 Server Log in Event Viewer and ran
> across a couple of things. Could someone please let me know if there is
> something that I should do based on these statements, in hopes of preventing
> this problem from happening again?
> For last Friday, a message says Process 3 generated fatal exception
> (EXCEPTION_ACCESS_VIOLATION). For last Sunday, a message says "Row compare
> failure".
> I'm not sure on these, but if someone could shed some insight, that would be
> great!
> Thanks!!!
> childofthe1980s

Row compare failure and Process 3

Hello:
We are on SQL Server 7.00.1063 (SP4) and Windows 2000 5.00.2195 (SP4).
I had to reboot our server this AM as SQL Server was not responding. It
seems to be fine, now.
Afterward, I reviewed the Windows 2000 Server Log in Event Viewer and ran
across a couple of things. Could someone please let me know if there is
something that I should do based on these statements, in hopes of preventing
this problem from happening again?
For last Friday, a message says Process 3 generated fatal exception
(EXCEPTION_ACCESS_VIOLATION). For last Sunday, a message says "Row compare
failure".
I'm not sure on these, but if someone could shed some insight, that would be
great!
Thanks!!!
childofthe1980sThere could be a memory leak issue if you use BULK INSERT.
If this is the case use BCP instead to avoid it.
There is more information:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246824
Regards.
"childofthe1980s" wrote:
> Hello:
> We are on SQL Server 7.00.1063 (SP4) and Windows 2000 5.00.2195 (SP4).
> I had to reboot our server this AM as SQL Server was not responding. It
> seems to be fine, now.
> Afterward, I reviewed the Windows 2000 Server Log in Event Viewer and ran
> across a couple of things. Could someone please let me know if there is
> something that I should do based on these statements, in hopes of preventing
> this problem from happening again?
> For last Friday, a message says Process 3 generated fatal exception
> (EXCEPTION_ACCESS_VIOLATION). For last Sunday, a message says "Row compare
> failure".
> I'm not sure on these, but if someone could shed some insight, that would be
> great!
> Thanks!!!
> childofthe1980s

Row compare failure and Process 3

Hello:
We are on SQL Server 7.00.1063 (SP4) and Windows 2000 5.00.2195 (SP4).
I had to reboot our server this AM as SQL Server was not responding. It
seems to be fine, now.
Afterward, I reviewed the Windows 2000 Server Log in Event Viewer and ran
across a couple of things. Could someone please let me know if there is
something that I should do based on these statements, in hopes of preventing
this problem from happening again?
For last Friday, a message says Process 3 generated fatal exception
(EXCEPTION_ACCESS_VIOLATION). For last Sunday, a message says "Row compare
failure".
I'm not sure on these, but if someone could shed some insight, that would be
great!
Thanks!!!
childofthe1980sThere could be a memory leak issue if you use BULK INSERT.
If this is the case use BCP instead to avoid it.
There is more information:
http://support.microsoft.com/defaul...kb;en-us;246824
Regards.
"childofthe1980s" wrote:

> Hello:
> We are on SQL Server 7.00.1063 (SP4) and Windows 2000 5.00.2195 (SP4).
> I had to reboot our server this AM as SQL Server was not responding. It
> seems to be fine, now.
> Afterward, I reviewed the Windows 2000 Server Log in Event Viewer and ran
> across a couple of things. Could someone please let me know if there is
> something that I should do based on these statements, in hopes of preventi
ng
> this problem from happening again?
> For last Friday, a message says Process 3 generated fatal exception
> (EXCEPTION_ACCESS_VIOLATION). For last Sunday, a message says "Row compar
e
> failure".
> I'm not sure on these, but if someone could shed some insight, that would
be
> great!
> Thanks!!!
> childofthe1980s

Row by Row Processing

I am trying to create a procedure within SQL Server 2000 that will update a
table for viewing over our intranet(nightly process). The final output will
show current balances within accounts.
The way the logic works for calculating the accounts is that the accounts
are allocated by years.
Example:
2001 ACCOUNT1
2002 ACCOUNT1
2001 ACCOUNT2
2003 ACCOUNT2
etc.....
Expenses occur over multiple years and must be applied to the earliest
year/account that exists. If the balance of the year/account is zero, then
look at the next year (if it exists) to apply the expense (expenses could be
split between 2 years).
What I have in the SQL server is a table with the accounts and balances, and
a seperate view with the expenditures. I want to take the view and "walk"
through record by record updating the table with current balances. I have
never done this before in SQL server and wonder if it can be done. We would
like to do it in the SQL Server because then the SQL server handles all the
data updating, etc. on it's own and we don't have to worry about an external
process to update this table.
I'm looking for an example, or guidance on what is the best way to perform
this task.
Thanks.You should go for a rowbased solution. YOu didnt post soe DDL, so we
cansee wheter the new data will be stored in existing tables (then you
should use an update) or in a new table (Then you should use ainsert or
select into). perhaps you can give some more information about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lyners" <Lyners@.discussions.microsoft.com> schrieb im Newsbeitrag
news:742DBEF6-85F1-47B8-8F6C-3F8B668ECDE9@.microsoft.com...
>I am trying to create a procedure within SQL Server 2000 that will update a
> table for viewing over our intranet(nightly process). The final output
> will
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
> be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances,
> and
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We
> would
> like to do it in the SQL Server because then the SQL server handles all
> the
> data updating, etc. on it's own and we don't have to worry about an
> external
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>|||hi
just look at CURSORs in SQL Server Books Online. U can traverse row-by-row
was this the one u are looking for?
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Lyners" wrote:

> I am trying to create a procedure within SQL Server 2000 that will update
a
> table for viewing over our intranet(nightly process). The final output wil
l
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances, a
nd
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We woul
d
> like to do it in the SQL Server because then the SQL server handles all th
e
> data updating, etc. on it's own and we don't have to worry about an extern
al
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>|||@.OG: But rather using cursor you should always prefer using rowbased
statements. In common you can say that cursor are slower than rowbased
statements.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chandra" <chandra@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C9B5793C-2D15-48DA-96DC-8CB91DFD4338@.microsoft.com...
> hi
> just look at CURSORs in SQL Server Books Online. U can traverse row-by-row
> was this the one u are looking for?
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Lyners" wrote:
>|||You could use a cursor for this, but cursors on large rowsets can be slow.
It sounds like what you are trying to do could be better implemented by
joining to a sub-query.
"Lyners" <Lyners@.discussions.microsoft.com> wrote in message
news:742DBEF6-85F1-47B8-8F6C-3F8B668ECDE9@.microsoft.com...
> I am trying to create a procedure within SQL Server 2000 that will update
a
> table for viewing over our intranet(nightly process). The final output
will
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances,
and
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We
would
> like to do it in the SQL Server because then the SQL server handles all
the
> data updating, etc. on it's own and we don't have to worry about an
external
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.|||The best way to get help with your problem is to post DDL, sample data
and required results. See:
http://www.aspfaq.com/etiquett=ADe.asp?id=3D5006
I doubt that row-by-row processing is the best solution. Pobably you
can do this with an UPDATE or SELECT statement.
--=20
David Portas=20
SQL Server MVP=20
--|||Thanks Jens,
for the update. I was suposed to mention that but clicked send before
mentioning that. I normally suggest people not to use cursors as they consum
e
a lotof time and memory.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Jens Sü?meyer" wrote:

> @.OG: But rather using cursor you should always prefer using rowbased
> statements. In common you can say that cursor are slower than rowbased
> statements.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Chandra" <chandra@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:C9B5793C-2D15-48DA-96DC-8CB91DFD4338@.microsoft.com...
>
>|||It's OK to admit that we all use cursors on occasion. They can be convenient
so long as the rowset is small. A lot of the system stored procedures in
MASTER are implemented using cursors, so we can't avoid them.
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:1101D390-347A-45C3-AA4B-78577F8A4080@.microsoft.com...
> Thanks Jens,
> for the update. I was suposed to mention that but clicked send before
> mentioning that. I normally suggest people not to use cursors as they
consume
> a lotof time and memory.
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Jens Smeyer" wrote:
>
row-by-row
update
output
accounts
earliest
could
balances,
"walk"
have
all|||I got it working... kind of. My problem is the select at the end. it appears
that the @.vchNextExpenseFiscalYearID are being reset. I set the
@.vchNextExpenseFiscalYearID by a select statement, but there is another quer
y
with the same field name (FiscalYear) that pulls into another varable. Does
SQL Server 2000 set a varable so that it is static so it is set to the year
(i.e. 2003), or is my varable (@.vchNextExpenseFiscalYearID) dynamic and set
to the field FiscalYear, so it changes with the field?
Please note that I did not use Cursors or Fetch next. I didn't use cursors
because of the resource use, and I didn't use Fetch Next because I did not
realize there was such a command until I was close to the end.
here is my code:
CREATE PROCEDURE dbo.loadunspentbonds AS
delete from unspentbonds
insert into unspentbonds(fiscalyear, project, subproject, bondamount,
UnspentBond) select fiscalyear, project, subproject, bondamount, bondamount
from vwUnspentBondsbudgetprior2005
insert into unspentbonds(fiscalyear, project, subproject, bondamount,
UnspentBond) select fiscalyear, project, subproject, bondamount, bondamount
from vwUnspentBondsbudgetafter2004
/* Update the Unspent Bond table with Expenditures and the current bond amou
nt
** We use a row by row processing to achieve our results because expenditure
s
** go back to the first year of the bonds
*/
SET NOCOUNT ON
-- declare all variables!
DECLARE @.iReturnCode int,
@.vchNextProjectID nvarchar(5),
@.vchNextSubProjectID nvarchar(2),
@.vchNextFiscalYearID nvarchar(4),
@.vchCurrentProjectID nvarchar(5),
@.vchCurrentSubProjectID nvarchar(2),
@.vchCurrentFiscalYearID nvarchar(4),
@.iExpenseLoopControl int,
@.vchNextExpenseProjectID nvarchar(5),
@.vchNextExpenseSubProjectID nvarchar(2),
@.vchNextExpenseFiscalYearID nvarchar(4),
@.vchCurrentExpenseProjectID nvarchar(5),
@.vchCurrentExpenseSubProjectID nvarchar(2),
@.vchCurrentExpenseFiscalYearID nvarchar(4),
@.fltGLExpendedAmount float,
@.fltUnspentBondAmount float
-- Initialize variables
SELECT @.iExpenseLoopControl = 1
SELECT TOP 1 @.vchNextExpenseProjectID = Project,
@.vchNextExpenseSubProjectID = SubProject,
@.vchNextExpenseFiscalYearID = FiscalYear
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
-- Make sure the table has data
if isnull(@.vchNextExpenseProjectID,'') = ''
BEGIN
RETURN
END
-- Retrieve the first Unspent Bond Row
SELECT TOP 1 @.vchCurrentExpenseProjectID = Project,
@.vchCurrentExpenseSubProjectID = SubProject,
@.vchCurrentExpenseFiscalYearID =
FiscalYear,
@.fltGLExpendedAmount = GLExpended
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
WHERE Project = @.vchNextExpenseProjectID and
SubProject = @.vchNextExpenseSubProjectID and
FiscalYear = @.vchNextExpenseFiscalYearID
WHILE @.iExpenseLoopControl = 1
BEGIN
-- Begin the nested(inner) loop.
-- Get the first Unspent Bond for the current Expense Record
SELECT @.vchNextProjectID = Project,
@.vchNextSubProjectID = SubProject,
@.vchNextFiscalYearID = FiscalYear
FROM [CapitalfINANCE].[dbo].[UnspentBonds]
WHERE Project = @.vchCurrentExpenseProjectID and
SubProject = @.vchCurrentExpenseSubProjectID
--make sure that the Unspent Bond exists
if isnull(@.vchNextProjectID,"") <> ""
BEGIN
WHILE @.vchNextProjectID = @.vchCurrentExpenseProjectID and
@.vchNextSubProjectID = @.vchCurrentExpenseSubProjectID
BEGIN
-- Get the first Unspent Bond for the current Expense Record
SELECT @.vchCurrentProjectID = Project,
@.vchCurrentSubProjectID = SubProject,
@.vchCurrentFiscalYearID = FiscalYear,
@.fltUnspentBondAmount = UnspentBond
FROM [CapitalfINANCE].[dbo].[UnspentBonds]
WHERE Project = @.vchNextProjectID and
SubProject = @.vchNextSubProjectID and
FiscalYear = @.vchNextFiscalYearID
IF @.fltGLExpendedAmount < @.fltUnspentBondAmount
BEGIN
UPDATE [CapitalfINANCE].[dbo].[UnspentBonds]
SET unspentBond = @.fltUnspentBondAmount - @.fltGLExpendedAmount,
ExpenditureAmount = ExpenditureAmount +
@.fltGLExpendedAmount
WHERE Project = @.vchCurrentProjectID and
SubProject = @.vchCurrentSubProjectID and
FiscalYear = @.vchCurrentFiscalYearID
SELECT @.fltGLExpendedAmount = 0
END
ELSE
BEGIN
SELECT @.fltGLExpendedAmount = @.fltGLExpendedAmount -
@.fltUnspentBondAmount
UPDATE [CapitalfINANCE].[dbo].[UnspentBonds]
SET unspentBond = 0,
ExpenditureAmount = ExpenditureAmount + @.fltUnspentBondAmount
WHERE Project = @.vchCurrentProjectID and
SubProject = @.vchCurrentSubProjectID and
FiscalYear = @.vchCurrentFiscalYearID
END
SELECT @.vchNextProjectID = Project,
@.vchNextSubProjectID = SubProject,
@.vchNextFiscalYearID = FiscalYear
FROM [CapitalfINANCE].[dbo].[UnspentBonds]
WHERE Project + SubProject + FiscalYear > @.vchNextProjectID +
@.vchNextSubProjectID + @.vchNextFiscalYearID
END
END
SELECT TOP 1 @.vchNextExpenseProjectID = Project,
@.vchNextExpenseSubProjectID = SubProject,
@.vchNextExpenseFiscalYearID = FiscalYear
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
WHERE Project + SubProject + FiscalYear >
@.vchCurrentExpenseProjectID + @.vchCurrentExpenseSubProjectID +
@.vchCurrentExpenseFiscalYearID
BEGIN
-- Make sure the table has data
if isnull(@.vchnextExpenseProjectID,"") = ""
BEGIN
BREAK
END
SELECT @.vchCurrentExpenseProjectID = Project,
@.vchCurrentExpenseSubProjectID = SubProject,
@.vchCurrentExpenseFiscalYearID = FiscalYear,
@.fltGLExpendedAmount = GLExpended
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
WHERE Project = @.vchNextExpenseProjectID and
SubProject = @.vchNextExpenseSubProjectID and
FiscalYear = @.vchNextExpenseFiscalYearID
END
RETURN
GO
"Lyners" wrote:

> I am trying to create a procedure within SQL Server 2000 that will update
a
> table for viewing over our intranet(nightly process). The final output wil
l
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances, a
nd
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We woul
d
> like to do it in the SQL Server because then the SQL server handles all th
e
> data updating, etc. on it's own and we don't have to worry about an extern
al
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>

Routing file to printer

Is there a way to route a file directly to a printer? I have a process that creates PDF file in a known location and I would like to route them directly to the printer after creation.

Did one of your earlier threads not answer this question?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1259522&SiteID=1|||

I did not see the earlier answer. I have posted an additional question there.

Thanks for the answer.

Routing file to printer

Is there a way to route a file directly to a printer? I have a process that creates PDF file in a known location and I would like to route them directly to the printer after creation.

Did one of your earlier threads not answer this question?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1259522&SiteID=1|||

I did not see the earlier answer. I have posted an additional question there.

Thanks for the answer.