SQL查询根据收费日期选择有效的成本费率

发布时间：2020-12-12 06:33:52 所属栏目：MsSql教程来源：网络整理

导读：SQL新手在这里.我正在尝试生成一个成本核算查询,该查询输出员工时间卡信息并根据有效的员工成本核算率计算成本. 我的问题类似于这里提出的问题：Retro-active effective date changes with overlapping dates但我不处理复古活动或重叠日期范围. 表示例(速率表

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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!