1

I am designing a database and I have a table which contains a calculated column. The tables are illustrated below, the first table's name is ProjectDetails.TimeCard, the second table is HumanResources.Employee

ProjectDetails.TimeCard
[TimeCardID | EmployeeID(Foreignkey) | BillableHours | **TotalCost** ]

HumanResources.Employee
[EmployeeID(Primarykey) | Fname | Lname | **BillingRate** ]

Now TotalCost is the calculated column, to be automatically computed as BillableHours*BillingRate

This requires that I set the calculated column of table A as the result of a calculation between another column in the same table and a column on a different table B.

Can I do this?

peterm
  • 91,357
  • 15
  • 148
  • 157
Mitchel4real
  • 13
  • 4
  • Can you give us a schema and an example of what you're trying to do? – Liath Jan 15 '14 at 09:42
  • Yes, but you'll need to be much more specific if you want any help. Provide table structures, sample data and expected result. – StevieG Jan 15 '14 at 09:42
  • Computed Columns are limited to columns from the same table. A View will give you what you're asking for here. Or you can use an `UPDATE` statement. You need to ask yourself if you want the `TotalCost` to change later if the `BillingRate` for an Employee changes. It is possible you want to copy the `BillingRate` to the `TimeCard` table at the time of creation/update to avoid this issue, which then means you can use a normal Computed Column too. – Timothy Walters Jan 15 '14 at 23:29
  • Thanks A lot. I thought so too, but I needed other people's opinion. Thankyou – Mitchel4real Jan 16 '14 at 05:54

1 Answers1

0

i would go about it like this:

UPDATE table1 
SET    calculated_column = <calculation> 
FROM   table1 
       JOIN table2 
         ON table1.column1 = table2.column2

if you want to use a trigger to do it automatically, here is a template:

CREATE TRIGGER calc_col_trigger
ON table1
 AFTER INSERT,UPDATE
AS

DECLARE @id int;
SET @id = (SELECT id FROM inserted)
-- get a unique identifier from the inserted record

UPDATE table1 
set    calculated_column = <calculation>
FROM   table1 
       JOIN table2 
       ON   table1.column1 = table2.column2 
where  table1.id = @id;
Brett Schneider
  • 3,993
  • 2
  • 16
  • 33
  • I did this and it worked. But is there a way †☺ make it compute automatically, as data is inserted into the other columns? – Mitchel4real Jan 16 '14 at 05:57
  • yes, use an insert-trigger. it will be fired each time an insert is made to the table. i will just put an edit to my answer. another option is to simply put a view over the table with a computed column like `select table1.*, as calculated_column from table1 join table2 on table1.column1 = table2.column2` – Brett Schneider Jan 16 '14 at 09:30