Cumulative function or calculation

  • 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.

  • try this

    declare

    @value int

    set

    @value = 0

    update

    yourtable

    set

    @value = @value + isnull(WeekCumulative, WeeklyTarget ), weekCumulative = @value

    from

    yourtable

     

     

    www.sql-library.com[/url]

  • thanks for your help, however, it doesnt work.

  • whats wrong with it?

    Does it error or update the data incorrectly?

    Please post an example.

    www.sql-library.com[/url]

  • 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.

  • 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??

     

     

  • 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.

  • 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.

    www.sql-library.com[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply