sql - SQL Server 2014 中的条件 SUM

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 approved hours 是完全可以想象和合法的。 2 declined hours 之前的行计算累积总和时的行——这将解释你当前的结果——尽管行本身以不同的顺序返回给你(顺便说一句，考虑在查询中添加 order by 子句，否则，顺序行本身不能保证)。

如果 2 行确实共享完全相同的日期，则您必须找到第二列以消除歧义并将其添加到 order by累计子句sum窗口功能。也许您可以添加一个可以订购的时间戳字段。

或者也许你总是想要 declined在 approved 之前考虑的状态AbsenceDate 时的状态是一样的。下面是一个查询的例子，可以做到这一点(注意 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