使用下一行中的值计算时间和成本

Question

请考虑以下数据，这些数据显示了工程师出差到工作所需的时间。ChargeBand列显示了在一天中的不同时间收取的各种费率。许多工程师可以同时处理一项工作。

​

​

我希望能够将每个工程师的旅行时间限制在一个小时内。因此，即使旅行时间超过一个小时，最高支付的是一个小时的旅行费用，不能超过一个小时。这很好，当旅行时间包含在一个电荷带内时，我可以使用以下CASE语句执行此操作：

CASE 
WHEN NumberOfHours > 1.0 AND CallIDStartDate >= '2019-11-01' Then cast((1 * isnull(x.Rate,1)) as 
decimal(20,7))
ELSE cast((NumberOfHours * isnull(x.Rate,1)) as decimal(20,7))
END as LabourCost

然而，问题是当一个小时的旅行跨越两个电荷带时。这是我无法解决的问题，我希望得到帮助。例如，在周一的前两行中，工程师从07.46到09.41出差，但他的出差成本应限制在8.46。因此，前14分钟按46.62收费，其余44分钟按37.67收费。我该怎么做呢？

多个星期二的条目表示有多个工程师参加。挑战还包括为每个工程师确定两行为07.46/44和08:00 StartTimes，并根据星期一的示例设置一个小时的旅行费用上限。

我想按天对表进行分区，这样很明显，任何小于前一行中的值的StartTime都属于负责相同工作的不同工程师，但这对计算本身没有帮助。

我还考虑使用LEAD()和LAG()函数从下面的行值计算时间或费用，也许答案就是它们，但我不知道如何在代码中应用。

Answer 1

您可以尝试使用CTE和LEAD函数。如果每个工作日每个员工有两条记录，则此示例有效

with cte as(
select employeeid, weekday, starttime, finishtime, chargeband, rate firstrate,
lead(starttime,1) over(partition by employeeid, weekday order by starttime) nextstarttime,
lead(finishtime,1) over(partition by  employeeid, weekday order by starttime) nextfinishtime,
lead(rate,1) over(partition by  employeeid, weekday order by starttime) nextrate
from ratetable) 
select employeeid, weekday, case when firsttime>= 1 then firstrate
       else firsttime*firstrate+ (case when secondtime>1 then 1-firsttime else secondtime-firsttime end)*nextrate end
from(
select employeeid, weekday, DATEDIFF(second, starttime, finishtime) / 3600.0 firsttime ,firstrate,
       DATEDIFF(second, nextstarttime, nextfinishtime) / 3600.0 secondtime,nextrate
from cte where nextstarttime is not null) x