I have two tables called TimeCardHours and Employee. Below are the structure of those.
Now I have a question like below:
The total cost in TimeCardHourse should be automatically calculated using the formula (Billable hours * Billing rate for the employee to whom the time card is issued.)I gave a big try to do this, but I failed. please help me..Please..
OK. Computed Column doesn't appear to do what you need (a sub-query).
I would probably go for the Trigger in this situation, just to ensure that the calculations are performed as they should be during normal operations (assuming normal operations doesn't include bulk loading this table).
You should also consider putting a "billed" field on the TimeCardHours table, so that the trigger knows not to update the costs on that one...: http://msdn.microsoft.com/en-us/library/ms187926.aspx
I would suggest moving the BillingRate into a separate table with ValidFrom and ValidTo to cover the possibility of a changing BillingRate.
I prefer the view to a trigger/sproc, as a calculated value like this can change so often.
Another possibility is storing the billing rate in the TimCardsHours table directly with the BillableHours and then creating a computed column for that.
answered Jul 23, 2010 at 01:41 AM
As an evil way to do this, you could put the calculation in a user defined function and call that as part of your computed column. However, this would be entirely evil when used in a large data scenario. Functions are bad enough for performance, let alone ones which do data lookups.
But, it is an option.
answered Jul 23, 2010 at 06:50 AM
Matt Whitfield ♦♦