使用DATEDIFF和记录VALUE UPDATE的递归SUM

Question

我正在致力于实现一个用于监控性能的积分跟踪系统。员工的目标是尽可能少地获得分数。有一个积分系统，允许员工通过在不同时间段表现更好来减少积分。在30天的表现中，分数最多减少1分，如果达到60天没有发生事故，则减少2分，如果超过90天没有发生事件，则减少4分。它们不能累积负值，积分会从最早的日期到最新的日期递减。下表示例是已导入SQL Server 2012数据库的现有表的表示形式。

Employee    Points    Date  Previous    Note
Smith, Joe  0.25    3/21/2013       
Smith, Joe  1       4/1/2013        
Smith, Joe  0.25    5/6/2013        
Smith, Joe  0.5     5/8/2013        
Smith, Joe  1       7/10/2013       
Jones, Tom  1       4/10/2013       
Jones, Tom  1       4/18/2013       
Jones, Tom  0.5     4/22/2013       
Jones, Tom  2       6/25/2013       
Jones, Tom  0.25    7/26/2013       
Jones, Tom  0.25    7/28/2013       

由于数据的动态使用，涉及C#、Excel、电子邮件等多个源，因此需要构建确定性函数，并生成更新后的表或视图，以显示类似于以下内容的内容。下面的例子是2013年5月18日发布的报告。每位员工将减1分。因为乔的第一个点是.25，所以它将被设置为0，下一个点是1.00，将减去剩余的.75，值为.25。因为汤姆的第一分是满1分，所以会从这1分中减去，得出当天的0分。在更新"点"值之前，函数还应标记"点"列的上一个值。函数应该提供一个关于更改点的原因的注释，日期在注释字段中会很好，但NoteDate的单独列可以包含日期。该函数可以在INSERT/UPDATE触发器上运行，因为每天的使用量非常低。它应该检查当前日期和最后一点日期。

Employee    Points  Date    Previous    Note
Smith, Joe  0.00    3/21/2013   0.25    Rolled Off by System - 30 Day Policy 05/01/2013
Smith, Joe  0.25    4/1/2013    1.00    Rolled Off by System - 30 Day Policy 05/01/2013
Smith, Joe  0.25    5/6/2013        
Smith, Joe  0.50    5/8/2013        
Jones, Tom  0.00    4/10/2013   1.00    Rolled Off by System - 30 Day Policy 05/22/2013
Jones, Tom  1.00    4/18/2013       
Jones, Tom  0.50    4/22/2013       

截至今天2013年8月24日，结果应反映以下内容。乔总共得0分，汤姆总共得2分，到28日时只有1分。大约有2000条记录，因此很难手动更改这些记录。根据这个系统的性质及其实现，可以运行一个脚本来协调当前记录，并有一个单独的脚本来管理正在进行的条目。

Employee    Points  Date    Previous    Note
Smith, Joe  0.00    3/21/2013   0.25    Rolled Off by System - 30 Day Policy 05/01/2013
Smith, Joe  0.00    4/1/2013    0.25    Rolled Off by System - 30 Day Policy 06/07/2013
Smith, Joe  0.00    5/6/2013    0.25    Rolled Off by System - 30 Day Policy 06/07/2013
Smith, Joe  0.00    5/8/2013    0.50    Rolled Off by System - 30 Day Policy 06/07/2013
Smith, Joe  0.00    7/10/2013   1.00    Rolled Off by System - 30 Day Policy 08/10/2013
Jones, Tom  0.00    4/10/2013   1.00    Rolled Off by System - 30 Day Policy 05/22/2013
Jones, Tom  0.00    4/18/2013   1.00    Rolled Off by System - 60 Day Policy 06/21/2013
Jones, Tom  0.00    4/22/2013   0.50    Rolled Off by System - 30 Day Policy 07/25/2013
Jones, Tom  1.50    6/25/2013   2.00    Rolled Off by System - 30 Day Policy 07/25/2013
Jones, Tom  0.25    7/26/2013       
Jones, Tom  0.25    7/28/2013       

我开始在EmployeePerformance TABLE中使用以下内容，创建一个视图，根据每个员工的ID显示每个记录之间的天数。然后，我使用DENSE_RANK（）为每个员工编号，其中包含考勤视图中的所有信息。我最初的想法是使用递增的VARIABLE为每个员工循环WHILE循环，然后检查DATE_DIFFERENCE，看看是否有大于30的值。如果没有，我已经计划将该员工的所有记录插入到最终表格中，并将其从Attendand2表格中删除。比我认为需要的脚本多得多。相反，我考虑使用CURSOR来创建一个一次性传递，然后再协调另一个函数。不过，我对CURSOR没有什么经验。

CREATE VIEW Attendance AS
SELECT A.ID, A.FullName, A.EmployeeID, A.AttendanceDate, A.OccurrenceAmount, A.Comments, A.RecordCreatedDate, A.RecordCreatedUser, (DATEDIFF(DAY, A.AttendanceDate, B.AttendanceDate))*-1 AS DATE_DIFFERENCE
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY AttendanceDate) AS Row_Num, * 
FROM dbo.EmployeePerformance) AS A
LEFT JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY AttendanceDate) AS Row_Num, * 
FROM dbo.EmployeePerformance) AS B
ON A.EmployeeID=B.EmployeeID AND A.Row_Num=B.Row_Num+1  

SELECT *, DENSE_RANK() OVER (ORDER BY Attendance.EmployeeID) AS Row_Num
INTO dbo.Attend2
FROM dbo.Attendance

我有一些想法可以解决这个问题，但我希望有人以前可能遇到过类似的情况或遇到过这样的问题。我更愿意将其构建成一段可以触发的简洁代码，由于SQL数据库在低容量环境中使用，我并不担心性能，但我希望找到一个同时考虑性能的好解决方案。感谢您的任何帮助或反馈。

Answer 1

我不确定我是否完全遵循您的方法/期望的结果，但对于初学者来说，您可以使用LEAD而不是JOIN/ROW_NUMBER()来获得DATEDIFF:

SELECT ID
     , FullName
     , EmployeeID
     , AttendanceDate
     , OccurrenceAmount
     , Comments
     , RecordCreatedDate
     , RecordCreatedUser
     , DATEDIFF(DAY, AttendanceDate, LEAD(AttendanceDate) OVER(PARTITION BY EmployeeID ORDER BY AttendanceDate))*-1 AS DATE_DIFFERENCE
FROM dbo.EmployeePerformance)