遍历Credible工时明细按时间卡阈值生成EncounterData记录
Credible工时超总额度场景的EncounterData生成方案
核心实现思路
不使用逐行游标遍历,通过窗口函数完成每个人员下的明细累计计算,自动识别需要截断的边界记录,一次性生成符合总额度要求的插入数据,性能远高于循环逻辑。
逻辑实现步骤
- 对每个需要处理的人员(即CredibleHours > TotalHours、TotalHours>0、匹配对应PayPeriodID的人员),取出其所有Credible工时明细,严格按照要求的
MergedDuration从小到大的顺序排序 - 计算每一条明细在排序后的累计基线:包括当前行之前的累计分钟数、包含当前行的累计分钟数
- 逐行计算每条明细可计入的实际时长:
- 如果上一行的累计时长已经达到允许的TotalHours额度,当前行计0
- 如果上一行累计还没到额度,但加上当前行的时长会超过额度,就只计入「允许总时长 - 上一行累计时长」的截断部分
- 其余情况全额计入当前行的时长
- 过滤掉计入时长为0的无效明细,关联CredibleXRef表获取对应的LocationNum、CostCenterNum,按照原有Insert的分组规则聚合后写入EncounterData表
完整可运行代码
DECLARE @PayPeriodID INT = 2116; WITH CredibleDetailWithCum AS ( SELECT tci.Credible_ID, tci.MergedDuration, tci.CredibleLocation, tci.Program, tci.CurPayerCode, hwi.TotalHours, -- 当前行上一步的累计分钟数 SUM(tci.MergedDuration) OVER ( PARTITION BY tci.Credible_ID ORDER BY tci.MergedDuration ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) AS PriorCumMinutes, -- 包含当前行的累计分钟数 SUM(tci.MergedDuration) OVER ( PARTITION BY tci.Credible_ID ORDER BY tci.MergedDuration ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS CurrentCumMinutes FROM HoursWorkedInformation hwi INNER JOIN TempCredibleImport tci ON hwi.Credible_ID = tci.Credible_ID AND hwi.PayPeriodID = tci.PayPeriodID -- 修正跨期关联潜在问题 WHERE hwi.PayPeriodID = @PayPeriodID AND hwi.TotalHours > 0 AND hwi.Credible_Hours > hwi.TotalHours -- 仅处理Credible超总工时的场景,避免与现有逻辑重复 ), CredibleDetailWithEligible AS ( SELECT Credible_ID, CredibleLocation, Program, CurPayerCode, TotalHours, -- 计算每条明细实际可计入的分钟数 CASE WHEN ISNULL(PriorCumMinutes, 0) >= TotalHours * 60 THEN 0 WHEN CurrentCumMinutes > TotalHours * 60 THEN TotalHours * 60 - ISNULL(PriorCumMinutes, 0) ELSE MergedDuration END AS EligibleMinutes FROM CredibleDetailWithCum ) INSERT INTO EncounterData ( Credible_ID, TotalHoursWorked, PayPeriodID, LocationNum, CostCenterNum, RecordActive, CreatedBy, CreateDate, ModifiedBy, ModifiedDate ) SELECT cdwe.Credible_ID, ROUND(SUM(cdwe.EligibleMinutes) / 60, 2) AS TotalHoursWorked, @PayPeriodID, cxr.LocationNum, cxr.CostCenterNum, 1, 'import', GETDATE(), 'import', GETDATE() FROM CredibleDetailWithEligible cdwe LEFT JOIN CredibleXRef cxr ON cxr.CredibleLocation = cdwe.CredibleLocation AND cxr.Program = cdwe.Program AND cxr.CurPayer_Code = cdwe.CurPayerCode WHERE cdwe.EligibleMinutes > 0 GROUP BY cdwe.Credible_ID, cxr.LocationNum, cxr.CostCenterNum;
关键对齐说明
- 所有计算全程以分钟为单位,避免小时小数换算带来的精度偏差,最终输出小时值时和原有逻辑保持一致,保留两位小数
- 完全复用现有CredibleHours≤TotalHours场景的字段映射、关联规则、分组逻辑,生成的EncounterData记录结构和日常存储习惯完全一致
- 排序规则严格和原有取数逻辑匹配,确保“从小到大遍历明细累加”的业务规则不被打乱,不会出现随机分配截断时长的情况
- 过滤条件完全匹配业务边界,只处理CredibleHours超过TotalHours的人员,不会和现有的常规插入逻辑产生重复写入
校验提醒:正式执行写入前,可以先把最后的INSERT语句替换为SELECT语句跑一遍,确认每个Credible_ID对应的累计写入总工时和HoursWorkedInformation里的TotalHours完全匹配,避免精度或逻辑偏差。
内容的提问来源于stack exchange,提问作者Darrell
相关产品推荐
相关产品推荐

