sql新手在这里.我正在尝试生成一个成本核算查询,该查询输出员工时间卡信息并根据有效的员工成本核算率计算成本.
我的问题类似于这里提出的问题：Retro-active effective date changes with overlapping dates但我不处理复古活动或重叠日期范围.
表示例(速率表中的空值表示当前速率)：
CREATE TABLE Emp_Rate
(
Emp int,Rate money,Rate_Start datetime,Rate_Exp datetime
)
CREATE TABLE Emp_Time
(
Emp int,Chrg_Date datetime,Chrg_Code varchar(10),Chrg_Hrs decimal(8,2)
)
Insert into Emp_Rate (Emp,Rate,Rate_Start,Rate_Exp) Values ('1','20','5/1/09','4/30/10')
Insert into Emp_Rate (Emp,'21','5/1/10','4/30/11')
Insert into Emp_Rate (Emp,'22','5/1/11',NULL)
Insert into Emp_Time (Emp,Chrg_Date,Chrg_Code,Chrg_Hrs) Values ('1','5/10/09','B','8')
Insert into Emp_Time (Emp,'5/10/10','5/10/11','8')
查询(返回由多个速率条目引起的欺骗(显然))：
Select Emp_Time.Emp,Cast(Emp_Time.Chrg_Date as DATE) as 'Chrg_Date',Emp_Time.Chrg_Code,Emp_Time.Chrg_Hrs,Emp_Rate.Rate,Emp_Time.Chrg_Hrs * Emp_Rate.Rate as 'Cost'
From Emp_Time inner join
Emp_Rate on Emp_Rate.Emp = Emp_Time.Emp
Order By [Emp],[Chrg_Date]
期望的输出：
Emp Chrg_Date Chrg_Code Chrg_Hrs Rate Cost 1 2009-05-10 B 8.00 20.00 160.00 1 2010-05-10 B 8.00 21.00 168.00 1 2011-05-10 B 8.00 22.00 176.00
我在子查询中使用Between运算符在圈子中四处走动,根据收费日期隔离正确的费率,但没有任何运气.
我感谢任何帮助！
解决方法
您没有指定DBMS类型,下面的答案是针对sql-server的.我确信还有其他方法可以做到这一点,但这种方式将使用当前日期替换null Rate_Exp日期.
Select et.Emp,Cast(et.Chrg_Date as DATEtime) as 'Chrg_Date',et.Chrg_Code,et.Chrg_Hrs,er.Rate,et.Chrg_Hrs * er.Rate as 'Cost'
From Emp_Time et
inner join
(
SELECT Emp,CASE
WHEN Rate_Exp is Null
THEN Convert(varchar(10),getdate(),101)
ELSE Rate_Exp
END as Rate_Exp
FROM Emp_Rate
)er
on er.Emp = et.Emp
WHERE (et.Chrg_Date BETWEEN er.Rate_Start AND er.Rate_Exp)
Order By et.Emp,et.Chrg_Date
或者在WHERE子句中使用CASE语句：
Select et.Emp,et.Chrg_Hrs * er.Rate as 'Cost'
From Emp_Time et
inner join Emp_Rate er
on er.Emp = et.Emp
WHERE (et.Chrg_Date
BETWEEN er.Rate_Start
AND CASE WHEN er.Rate_Exp Is Null
THEN Convert(varchar(10),101)
ELSE er.Rate_Exp END)
