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

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
>