SQL Server 2014中的条件求和

Question

我使用的是SQL Server 2014。当我测试我的代码时，我注意到一个问题。假设最大个人小时数为80小时。

SELECT    
    lsm.EmployeeName,
    pd.absenceDate,
    pd.amountInDays * 8 AS [HoursReported],
    pd.status,
    (SUM(CASE WHEN pd.[status]='App' THEN (pd.amountInDays * 8)
              ELSE 0 END) OVER (partition by lsm.[EmployeeName] order by pd.absenceDate)) AS [TotalUsedHours]
          ( @maxPSHours ) - (sum(
              CASE WHEN pd.[status]='App' THEN (pd.amountInDays * 8)
                   ELSE 0 END)
          over (
              partition by  lsm.[EmployeeName] order by pd.absenceDate)) AS [TotalRemainingHours]
FROM    
    [LocationStaffMembers] lsm 
INNER JOIN 
    [PersonalDays] pd ON lsm.staffMemberId = pd.staffMemberId 

此查询返回以下结果：

EmployeeName AbsenceDate HoursReported   Status     TotalUsdHrs  TotalRemingHrs
 X            11/11/2015       4         approved     4             76
 X            11/15/2015       8         approved     12            68
 X            11/20/2015       2         decline      14            66
 X            11/20/2015       2         approved     14            66

因此，查询在不同的状态下工作得很好。前2行就可以了。

这是预期的结果。

EmployeeName AbsenceDate HoursReported   Status     TotalUsdHrs  TotalRemingHrs
 X            11/11/2015       4         approved     4             76
 X            11/15/2015       8         approved     12            68
 X            11/20/2015       2         decline      12            68
 X            11/20/2015       2         approved     14            66

Answer 1

您正在进行累积求和，该求和将根据AbsenceDate (sum(...) over (partition by ... order by pd.absenceDate)的顺序返回结果。但是您的最后两条记录具有完全相同的日期(11/20/2015) --至少，根据您向我们展示的内容。这造成了歧义。

因此，在计算累积和时，SQL Server在处理2 declined hours行之前处理2 approved hours行是完全可以想象的，这可以解释您当前的结果，尽管行本身是以不同的顺序返回给您的(顺便说一句，请考虑在查询中添加一个order by子句，否则不能保证行本身的顺序)。

如果这两行实际上共享完全相同的日期，则必须找到第二列来消除歧义，并将其添加到累积sum窗口函数中的order by子句中。也许你可以添加一个你可以排序的时间戳字段。

或者，当AbsenceDate相同时，您可能总是希望declined状态优先于approved状态。下面是执行此操作的查询示例(请注意order by子句中的更改)：

SELECT    
    lsm.EmployeeName,
    pd.absenceDate,
    pd.amountInDays * 8 AS [HoursReported],
    pd.status,
    (SUM(CASE WHEN pd.[status]='App' THEN (pd.amountInDays * 8)
              ELSE 0 END) OVER (partition by lsm.[EmployeeName] order by pd.absenceDate,
                                                                         case when pd.[status] = 'App' then 1 else 0 end)) AS [TotalUsedHours]
          ( @maxPSHours ) - (sum(
              CASE WHEN pd.[status]='App' THEN (pd.amountInDays * 8)
                   ELSE 0 END)
          over (
              partition by  lsm.[EmployeeName] order by pd.absenceDate,
                                                        case when pd.[status] = 'App' then 1 else 0 end)) AS [TotalRemainingHours]
FROM    
    [LocationStaffMembers] lsm 
INNER JOIN 
    [PersonalDays] pd ON lsm.staffMemberId = pd.staffMemberId
ORDER BY lsm.[EmployeeName],
         pd.absenceDate,
         case when pd.[status] = 'App' then 1 else 0 end