Update a calculated field from another table 1

[CdnRissa]

I know there is a simple answer to this, but I can't seem to get to it today.

I have an Assets table that contains (among other things) the original cost, Accumulated depreciation, and net book value. I need to update the accumulated depreciation & NBV based on the sum of posted records in the DepnEntries table. AssetId is the primary key in the Assets table, and a foreign key in the DepnEntries table.

I thought to loop though the Asset records that need to be updated using an update cursor, but a little voice is telling me there is a better way.

If anyone could point me in the right direction, I would be ever so grateful.

This is running on a SQL Server 2005 database.

Cheers, Rissa

 

[markros]

You can use a calculated field with a function.

In other words, add computed column [Accumulated Deprecation] and set it to be dbo.ufnGetDeprecation(AssetID)

And the function can be something like

create function ufnGetDeprecation(@AssetID int)
returns decimal(20,2)
as
return select sum(Amount) from DepnEntries where
AssetID = @AssetID

PluralSight Learning Library

 

[CdnRissa]

Thank you, Markros!!

I was not aware of computed columns and this simplifies things in several other places as well.

Kudos!

 

[CdnRissa]

I have created the function and tested it. And I have changed the AccumDepn field from money to a computed column that calls the function. That seems to be working just fine - the values in the computed column are correct.

However, when I try to change the NBV field to a computed column using the formula "OrigCostCAD - AccumDepn" (the first is defined as a money column and the second returns a money value), I get a "Error validating the formula for column NetBookValue".

BooksOnline isn't very helpful. What am I missing?

Cheers, Rissa

 

[markros]

I don't think you can create a computed column based on another computed column. Also, try converting both values to decimal(12,2) before calculation.

PluralSight Learning Library

 

[CdnRissa]

You're right, you can't refer to a computed column in another computed column. Apparently, they're an end product.

Casting both values to decimal didn't work, so I simply called the same function in the NBV column which works beautifully. It means that there are two calls to the function every time the row is accessed, but it's a lot cleaner than the path I was heading down.

Thanks for all your help!