SQL Server - 月份总小时数

I have the case when i need to calculate the total contracted hours of an employee in a given 4 week period. The employee may change hours mid period and hence the total contracted hours would need to adjust based on when the hours changed

我有这种情况,我需要计算一个给定的4周时间内员工的总合同工时。员工可能会在中期更改小时数,因此总合约小时数需要根据小时数的变化进行调整

My SQL is

我的SQL是

declare @start datetime = '2017-06-20'
declare @end datetime = '2017-07-20'

select j.EMPLOY_REF, 
j.ACT_HOURS * 4 as ContractedHoursPer4WeekPeriod, 
j.ACT_HOURS as ContractedHoursPerWeek, 
(j.ACT_HOURS / 7) as ContractedHoursPerDay,
j.COSTCENTRE, 
j.FROMDATE, 
j.UNTILDATE 

from jobholdr j

where EMPLOY_REF ='000033504'

and the result being

结果是

If the period ran in this example from 20th June 2017 until 20 July 2017 then they should have 10 days at 39 hours per week and then 20 days at 30 hours per week.

如果在2017年6月20日至2017年7月20日这个例子中,他们应该每周39小时10天,然后每周30小时20天。

How can this be calculated to give one result for total contracted hours in the period given the difference of contracted hours over the period. I am guessing it needs to be done by breaking it down into days but I am not sure where to go next...

如何计算在给定期间合同工时的差异的情况下,给出一段时间内总合同工时的结果。我猜它需要通过分解成几天来完成,但我不知道下一步该去哪里...

1 个解决方案

#1

You can include in your SELECT the following

您可以在SELECT中包含以下内容

DATEDIFF(day, CASE WHEN @start > j.fromdate 
                   THEN @start 
                   WHEN @start = j.fromdate 
                   THEN j.fromdate 
                   ELSE DATEADD(day,-1,j.fromdate)
               END, 
              CASE WHEN @end > j.untildate 
                   THEN j.untildate 
                   ELSE @end
               END) * (j.act_hours / 7) TotalContractedHours,