时间戳

首页 > 解决方案 > 来自另一个表的列计算

sql - 来自另一个表的列计算

问题描述

我有两张桌子。首先是 HumanResources.Employees，另一个是 ProjectDetails.TimeCards b 我希望自动计算 ProjectDetailsTimeCards 上的 TotalCost 列，即 TotalCost=Billable Hours b * BillingRate a。它们可以与employee_ID 一起加入。但是我无法将其转换为表格格式。下表如下

Create table HumanResources.Employees (
Employee_ID int primary key identity (99, 11),
First_Name char (20) not null,
Phone_Number varchar (30) not null
CONSTRAINT chk_Phone_Number CHECK (Phone_Number like '[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'),
Title char (35),  
Billing_Rate int constraint chk_Billing_Rate check (Billing_Rate > '0')
); -- would be uses to multiply billing hours

create table ProjectDetails.TimeCards(
Time_Card_ID int identity (55,15) primary key,
Employee_ID int foreign key references HumanResources.Employees(Employee_ID), -- references primary key on the human_resources table
Date_Issued date,
Days_Worked int constraint chk_Days_Worked check(Days_Worked > '0'),
Project_ID int foreign key references ProjectDetails.Projects(Project_ID), -- foreign key reference
Billable_hours int constraint chk_Billable_Hours check (Billable_Hours > '0'),-- would be used to multiply billing rate
Total_Cost money, -- should automatically take TotalCost=Billable Hours b * BillingRate a
Work_Code_ID int foreign key references ProjectDetails.WorkCodes(Work_Code_ID)
);-- references primary key on another table

CREATE VIEW HumanResources.vwEmployeeData AS SELECT e.EmployeeID、e.Date_Issued、e.Days_Worked、e.Project_ID、e.Billable_Hours、e.Work_Code_ID——从第一个表中查看 FROM HumanResources.Employees e JOIN ProjectDetails.TimeCards d ON e。 Employee_ID = d.Employee_ID -- 我创建的视图

标签： sqlsql-server

解决方案

您可以通过定义类似这样的函数来实现这一点

CREATE FUNCTION GetValue(@billablehours INT, @empid INT)
RETURNS INT
AS 
   SELECT @billablehours * e.Billing_Rate 
   FROM HumanResources.Employees e
   WHERE e.Employee_ID = @empid

之后，您创建一个计算列

ALTER TABLE ProjectDetails.TimeCards
   ADD Total_Cost AS GetValue(Billable_hours, Employee_ID)

推荐阅读