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

因此，查询在不同状态下工作正常。前两行没问题。但是当员工在一天内执行多个操作（拒绝、批准等）时，我的查询仅显示该天使用和剩余的总数。

以下是期望的结果。

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

- NCooper

Answer 1

您正在进行一个累加总和，该总和基于AbsenceDate的顺序返回结果（sum(...) over (partition by ... order by pd.absenceDate）。但是，根据您所展示的内容，您最后的两条记录具有完全相同的日期（11/20/2015），这会产生歧义。

因此，SQL Server在计算累积总和时，在处理2 approved hours行之前可能会先处理2 declined hours行——这可以解释您当前的结果——尽管行本身以不同的顺序返回给您（顺便说一句，请考虑向查询添加order by子句，否则行本身的顺序不能保证）。

如果这2行确实共享完全相同的日期，则必须找到第2列以消除歧义，并将其添加到累积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

- sstan

---

我并没有完全阅读整个问题...但是用 RANGE 来框定窗口不就可以解决吗？ - shawnt00

@shawnt00：不，我很确定这只是一个排序问题。 - sstan