Post reply

Use of a Computed Column for Running Total in small reference table

  • December 16, 2004 at 12:28 pm

    #162867

    I have a table containing a list of project milestones for various types of projects. Each milestone indicates the percent complete of a project and the sum of all of the milestones equals 100%. Currently we have an additional field indicating the cumulative percent complete for each milestone for each project type, i.e., Project Type A has 4 milestones, Milestone #1 is 10%, #2 is 20%, #3 is 40% and #4 is 30% so the running totals are 10, 30, 70 and 100% respectively. I am wondering if it would be a good idea to change these hard coded values to a computed column so that if we need to change the value of a milestone then the cummulative value updates automatically. One of my coworkers does not think that this would be a good idea because every time we wanted to get the cummulative value it would have to be recalculated which would be less efficient than just pulling the static value. Could anyone tell me if this would really be an issue or how I could determine if it would be?

    TIA

     

  • December 16, 2004 at 12:39 pm

    #534227

    If the milestones are individual records a computed column will not work.  If they are separate fields on the same record a computed column would work fine.  The only way I can think of automatically adjusting those records would be to do it in the stored procedure that is updated the table or as a trigger.

    Doing it in a trigger would probably be resource intensive for something that may not change that often.  Depending on how dynamic the data is you might be able to just write a nightly update routine that ensures all percentages total 100%.

    The best and most accurate method, though, is to have the stored procedure that updates the information handle these types of changes.

    If the phone doesn't ring...It's me.

  • December 17, 2004 at 3:24 am

    #534301

    Thanks, the milestones are individual records. I was thinking that there might be some way with a UDF to include data from other records in the computed column

Viewing 3 posts - 1 through 2 (of 2 total)

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