Running Total cursor

I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.

You shouldn't need a cursor. Try this instead,
first create a view to give the totals up to
the current date.

CREATE VIEW AmountTotals
AS
SELECT a.Code_id,
a.Amount,
a.Date,
(SELECT SUM(b.Amount)
FROM Amount b
WHERE a.Code_id=b.Code_id
AND b.Date <= a.Date) AS RunningTotal
FROM Amount a
GO

Now you can update all of your ReachedDates
using this view

UPDATE ID
SET ReachedDate=(SELECT MIN(Date)
FROM AmountTotals
WHERE RunningTotal >= 100
AND AmountTotals.Code_id=ID.Code_ID)

|||

you have to create a for update, insert, delete trigger on

table amount which shall automatically mark the flag when the amount is reached. trigger fires automatically

|||Hey Mark. Works like a charm. Thank you so much.|||

Yeah, right. And freeze the server like magic! The best and faster solution its using cursors. I can say because I use "the magic" in some points of my app, and Im having performance problems with it.

Its really sad that microsoft dont release real solutions for simple problems like that.

Running Total cursor

I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.

You shouldn't need a cursor. Try this instead,
first create a view to give the totals up to
the current date.

CREATE VIEW AmountTotals
AS
SELECT a.Code_id,
a.Amount,
a.Date,
(SELECT SUM(b.Amount)
FROM Amount b
WHERE a.Code_id=b.Code_id
AND b.Date <= a.Date) AS RunningTotal
FROM Amount a
GO

Now you can update all of your ReachedDates
using this view

UPDATE ID
SET ReachedDate=(SELECT MIN(Date)
FROM AmountTotals
WHERE RunningTotal >= 100
AND AmountTotals.Code_id=ID.Code_ID)

|||

you have to create a for update, insert, delete trigger on

table amount which shall automatically mark the flag when the amount is reached. trigger fires automatically

|||Hey Mark. Works like a charm. Thank you so much.|||

Yeah, right. And freeze the server like magic! The best and faster solution its using cursors. I can say because I use "the magic" in some points of my app, and Im having performance problems with it.

Its really sad that microsoft dont release real solutions for simple problems like that.

Running total count in stored procedure

in my procedure, I want to count the number of rows that have errored
during an insert statement - each row is evaluated using a cursor, so
I am processing one row at a time for the insert. My total count to
be displayed is inside the cursor, but after the last fetch is called.
Wouldn't this display the last count? The problem is that the count is
always 1. Can anyone help?

here is my code,

... cursor fetch
begin ... cursor
if error then:
begin

INSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,
CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,
STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,
PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,
USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,
CUSTINDICATOR, DT_LOADED)
VALUES(@.ERRORNUM, @.ERRORDESC,
@.CUSTOMERNUMBER, @.CUSTOMERNAME, @.ADDRESS1, @.ADDRESS2, @.CITY,
@.STATE, @.POSTALCODE, @.CONTACT, @.PHONE, @.SALESREPCODE,
@.PRICELEVEL, @.TERMSCODE, @.DISCPERCENT, @.TAXCODE,
@.USERCOMMENT, @.CURRENCY, @.EMAILADDRESS, @.CUSTOMERGROUP,
@.CUSTINDICATOR, @.DTLOADED)

SET @.ERRORCNT = @.ERRORCNT + 1

END --error

--
FETCH NEXT FROM CERNO_US INTO
@.CUSTOMERNUMBER, @.CUSTOMERNAME, @.ADDRESS1, @.ADDRESS2, @.CITY, @.STATE,
@.POSTALCODE, @.CONTACT,@.PHONE,@.SALESREPCODE, @.PRICELEVEL,@.TERMSCODE,
@.DISCPERCENT, @.TAXCODE, @.USERCOMMENT, @.CURRENCY,@.EMAILADDRESS,
@.CUSTOMERGROUP, @.CUSTINDICATOR, @.DTLOADED
--
IF @.ERRORCNT > 0
INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,
STATUS)
VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @.ERRORCNT, 'FAILED
INSERT/UPDATE')

END -- cursor
CLOSE CERNO_US
DEALLOCATE CERNO_USTracey (tracey.lemer@.itsservices.com) writes:
> in my procedure, I want to count the number of rows that have errored
> during an insert statement - each row is evaluated using a cursor, so
> I am processing one row at a time for the insert. My total count to
> be displayed is inside the cursor, but after the last fetch is called.
> Wouldn't this display the last count? The problem is that the count is
> always 1. Can anyone help?

As I read your code, you insert a row into PROCEDURE_RESULTS as soon as
@.ERRORCNT is > 0, that is 1.

I don't know what you mean with "be displayed is inside the cursor, but
after the last fetch is called" - that makes no sense to me. You don't
really display the data what I can see, and you should just as well
insert into PROCEDURE_RESULTS after you have dealloacated the cursor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Running Total

I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
What I need to do is write a cursor that when the Amount adds up to $100.00
it Updates the ReachedDate to the YearPeriod fields value of that record. Any
help would be greatly appreciated.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1Likely, you don't need a cursor. However, you do need to provide DDL +
INSERT statements of sample data + expected results, so that we can better
analyze the problem.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"anaylor01 via SQLMonster.com" <u11795@.uwe> wrote in message
news:5eddecafb3c79@.uwe...
I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
What I need to do is write a cursor that when the Amount adds up to $100.00
it Updates the ReachedDate to the YearPeriod fields value of that record.
Any
help would be greatly appreciated.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Sorry. I have two tables. ID table that has these fields
Code_id, YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
Tom Moreau wrote:
>Likely, you don't need a cursor. However, you do need to provide DDL +
>INSERT statements of sample data + expected results, so that we can better
>analyze the problem.
>I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
>What I need to do is write a cursor that when the Amount adds up to $100.00
>it Updates the ReachedDate to the YearPeriod fields value of that record.
>Any
>help would be greatly appreciated.
--
Message posted via http://www.sqlmonster.com|||Let's try this again:
http://www.aspfaq.com/etiquette.asp?id=5006
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"anaylor01 via SQLMonster.com" <u11795@.uwe> wrote in message
news:5ede547a908d8@.uwe...
Sorry. I have two tables. ID table that has these fields
Code_id, YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
Tom Moreau wrote:
>Likely, you don't need a cursor. However, you do need to provide DDL +
>INSERT statements of sample data + expected results, so that we can better
>analyze the problem.
>I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
>What I need to do is write a cursor that when the Amount adds up to
>$100.00
>it Updates the ReachedDate to the YearPeriod fields value of that record.
>Any
>help would be greatly appreciated.
--
Message posted via http://www.sqlmonster.com|||See the article:
http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp
"anaylor01 via SQLMonster.com" wrote:
> I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
> What I need to do is write a cursor that when the Amount adds up to $100.00
> it Updates the ReachedDate to the YearPeriod fields value of that record. Any
> help would be greatly appreciated.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
>

Running Total

I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
What I need to do is write a cursor that when the Amount adds up to $100.00
it Updates the ReachedDate to the YearPeriod fields value of that record. An
y
help would be greatly appreciated.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1Likely, you don't need a cursor. However, you do need to provide DDL +
INSERT statements of sample data + expected results, so that we can better
analyze the problem.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"anaylor01 via droptable.com" <u11795@.uwe> wrote in message
news:5eddecafb3c79@.uwe...
I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
What I need to do is write a cursor that when the Amount adds up to $100.00
it Updates the ReachedDate to the YearPeriod fields value of that record.
Any
help would be greatly appreciated.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1|||Sorry. I have two tables. ID table that has these fields
Code_id, YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
Tom Moreau wrote:
>Likely, you don't need a cursor. However, you do need to provide DDL +
>INSERT statements of sample data + expected results, so that we can better
>analyze the problem.
>I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
>What I need to do is write a cursor that when the Amount adds up to $100.0
0
>it Updates the ReachedDate to the YearPeriod fields value of that record.
>Any
>help would be greatly appreciated.
Message posted via http://www.droptable.com|||Let's try this again:
http://www.aspfaq.com/etiquette.asp?id=5006
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"anaylor01 via droptable.com" <u11795@.uwe> wrote in message
news:5ede547a908d8@.uwe...
Sorry. I have two tables. ID table that has these fields
Code_id, YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
Tom Moreau wrote:
>Likely, you don't need a cursor. However, you do need to provide DDL +
>INSERT statements of sample data + expected results, so that we can better
>analyze the problem.
>I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
>What I need to do is write a cursor that when the Amount adds up to
>$100.00
>it Updates the ReachedDate to the YearPeriod fields value of that record.
>Any
>help would be greatly appreciated.
Message posted via http://www.droptable.com|||See the article:
http://www.sql-server-performance.c...dly_problem.asp
"anaylor01 via droptable.com" wrote:

> I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
> What I need to do is write a cursor that when the Amount adds up to $100.
00
> it Updates the ReachedDate to the YearPeriod fields value of that record.
Any
> help would be greatly appreciated.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1
>