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.
