July 23, 2006 at 9:03 am
Hi all,
Here's my table:
WkID (INT(identity), primary)
WorkID (int, FK)
WeeklyTarget (int)
WeekCumulative (int)
Right now i have a sproc that updates the WeeklyTarget (based on WkID) and then calculates the sum of WeeklyTarget for all WkID that is equal or less than the current WkID for updating the WeekCumulative.
I could do it only for one week, since it's based on the value of the input WkID. My question is: would it be possible to update the WeekCumulative for other weeks as well?
Hope I'm clear with my question.
Thanks for any help.
July 23, 2006 at 9:51 am
July 23, 2006 at 7:25 pm
thanks for your help, however, it doesnt work.
July 24, 2006 at 5:20 am
July 24, 2006 at 6:06 am
No error returned, it ran fine, however the value added to the WeekCumulative is incorrect.
To give you a better idea, I'm using an asp.net gridview to update the data. So users can update the weekly target for each week (it passes the WkID and WeeklyTarget value to the SQL serv). using my previous sproc, it updates the week target and the cumulative for that week based on previous weeks.
something like this:
@WorkID int,
@WkID int,
@WeekTarget int
AS
BEGIN
UPDATE Wekk
SET WeekTarget = @WeekTarget
WHERE (WkID = @WkID)
declare @pweek int
set @pweek = (select sum(WeekCumulative) as cvb
FROM Wekk
WHERE Wekk.WorkID=@WorkID and Wekk.WkID<=@WkID)
UPDATE Wekk
SET WeekCumulative=@pweek
WHERE (WkID = @WkID)
END
Hope you get a better picture now.
July 24, 2006 at 2:36 pm
Mayhaps I am not understanding your requirement but I would think that once the WeekTargets are updated, to update the WeekCumalative would something like this.
update A
set WeekCumulative = (select sum(weekTargets) from wekk where WKID <= A.WKID)
from WeKK A
This would effectively sum all weektargets with a WKID of the current WKID and less into the weekCumulative field.
Am I missing something in the requirement??
July 24, 2006 at 4:21 pm
Yes, you are right.
sum(weekTarget)from wekk where WKID <= A.WKID is correct.
However what if the user entered a wrong value in the middle of the list? If he makes adjustment/correction to that value (let's say Week no 5 of 12), the cumulative for week 6,7,8..etc wouldnt be updated to reflect the change. This is what I'm trying to do.
Thanks.
July 25, 2006 at 3:21 am
This kind of denormalisatin is a bad idea. You should calulate it 'on-demand' rather than storing the value in your DB.
If you must store this as a column you should use a trigger to keep it updated rather than relying on an SP. Put the update logic of the previous post in an triiger and bear in mind that you will have to recalculate all weeks that come after an updated or deleted row.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply