Automatic calculation problem

OK. Computed Column doesn't appear to do what you need (a sub-query).

Other options:

1. Trigger. When details are inserted / updated into the TimeCardHours, the trigger calculates the TotalCost value
2. Control access using a stored procedure, and have that calculate the TotalCost.

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

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.

CREATE TABLE TimeCardsHours
    (TimeCardDetailID int IDENTITY(1, 1)
                          CONSTRAINT pkTimeCardDetailID PRIMARY KEY,
     TimeCardID char(10),
     DateWorked datetime NOT NULL,
     ProjectID char(10),
     WorkDescription varchar(100),
     BillableHours int CONSTRAINT chkBillableHours CHECK (BillableHours > 0),
     BillingRate int CONSTRAINT chkbillingrate CHECK (billingrate > 0),
     TotalCost AS (BillableHours * BillingRate),
     TotalCode int,
     WorldCodeID char(10))

CREATE TABLE Employee
    (EmployeeID int IDENTITY(1, 1)
                    CONSTRAINT pkemployeeid PRIMARY KEY,
     FirstName char(10) NOT NULL,
     LastName char(10),
     Title char(10) CONSTRAINT chktitle CHECK (title IN ('Trainee', 'Team Member', 'Team Leader', 'Project Manager', 'Senior Project Manager')),
     Phone char(20) NOT NULL
                    CONSTRAINT chkemployeephone CHECK (phone LIKE ('([0-9],[0-9],[0-9])-([0-9],[0-9],[0-9])-([0-9],[0-9],[0-9],[0-9])')))

SELECT  *
FROM    TimeCardsHours t
INNER JOIN Employee e ON t.EmployeeID = e.EmployeeID

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.

/*
 * Scalar Function: fn_ExampleScalarLookupFunction 
 * Created By: Matthew.Whitfield
 */
CREATE FUNCTION [dbo].[fn_ExampleScalarLookupFunction ](@value int)
  RETURNS int
AS
BEGIN
  RETURN (SELECT SUM([TestColumn2]) FROM test2 WHERE [TestColumn] = @value)
END

you can create a view for this

select <specify the required column>, total_cost = t.BillableHours * e.BillingRate
from   TimeCardsHours t
       inner join Employee e on t.EmployeeID = e.EmployeeID