sql-server - 使用具有滚动总和的 SQL Over 语句

Question

我正在尝试跟踪使用前一年的数据计算得出的滚动总和。

目前我将其作为我的 SQL:

SELECT lngTIMEID as Id,
        lngEMPID as EmployeeId,
        dtmdateapp AS [Date], 
        stroccur AS [Value],
        (SELECT Sum(stroccur) 
         FROM   [Attendance].[dbo].timeuse a 
         WHERE  a.dtmdateapp between DateAdd(d, -366, d.dtmdateapp) 
                AND d.dtmDATEAPP
                AND a.lngempid = d.lngempid) AS Total
 FROM   [Attendance].[dbo].[timeuse] d
         WHERE lngEMPID =  1844140 AND absence = 'Unscheduled' AND lngRID IN (1,2,3,4,5,7,8,9,10,11,12,13,14)

这会返回这样的结果:

+--------+------------+-----------+-------+-------+
|   Id   | EmployeeId |   Date    | Value | Total |
+--------+------------+-----------+-------+-------+
| 330435 |    1844140 | 4/17/2017 |  0.25 |  0.25 |
| 330849 |    1844140 | 4/19/2017 |  0.25 |   0.5 |
| 331108 |    1844140 | 4/20/2017 |  0.25 |  0.75 |
| 331641 |    1844140 | 4/24/2017 |  0.25 |     1 |
| 331736 |    1844140 | 4/25/2017 |  0.25 |  1.25 |
| 333761 |    1844140 | 5/5/2017  |  0.25 |   1.5 |
| 336080 |    1844140 | 5/17/2017 |     1 |   2.5 |
| 349752 |    1844140 | 8/2/2017  |  0.25 |     3 | <--- this should be 2.75
| 350994 |    1844140 | 8/9/2017  |     1 |     4 |
| 351426 |    1844140 | 8/11/2017 |  0.25 |  4.25 |
| 352132 |    1844140 | 8/15/2017 |   0.5 |  4.75 |
| 354236 |    1844140 | 8/25/2017 |  0.25 |     5 |
| 355580 |    1844140 | 8/29/2017 |  0.25 |  5.25 |
| 355650 |    1844140 | 9/5/2017  |  0.25 |   5.5 |
+--------+------------+-----------+-------+-------+

这个想法是，它将循环遍历所有发生的事件，并对当前日期一年内发生的所有先前发生的事件进行汇总。

使用我当前的代码，计算存在问题，总数达到 3。

我想使用 SQL Over 语句，因为它计算正确，但我不确定如何在滚动 1 年时间范围内使用它。这是我使用的:

SELECT lngTIMEID as Id,
        lngEMPID as EmployeeId,
        dtmdateapp AS [Date], 
        stroccur AS [Value],
        sum(strOCCUR) OVER(ORDER BY dtmdateapp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as [Total]
 FROM   [Attendance].[dbo].[timeuse] d
         WHERE lngEMPID =  1844140 AND absence = 'Unscheduled' AND lngRID IN (1,2,3,4,5,7,8,9,10,11,12,13,14)

并给了我正确的结果，因为这些数据都发生在同一年，但它不适用于滚动的 1 年时间范围:

+--------+------------+-----------+-------+-------+
|   Id   | EmployeeId |   Date    | Value | Total |
+--------+------------+-----------+-------+-------+
| 330435 |    1844140 | 4/17/2017 |  0.25 |  0.25 |
| 330849 |    1844140 | 4/19/2017 |  0.25 |   0.5 |
| 331108 |    1844140 | 4/20/2017 |  0.25 |  0.75 |
| 331641 |    1844140 | 4/24/2017 |  0.25 |     1 |
| 331736 |    1844140 | 4/25/2017 |  0.25 |  1.25 |
| 333761 |    1844140 | 5/5/2017  |  0.25 |   1.5 |
| 336080 |    1844140 | 5/17/2017 |     1 |   2.5 |
| 349752 |    1844140 | 8/2/2017  |  0.25 |  2.75 |
| 350994 |    1844140 | 8/9/2017  |     1 |  3.75 |
| 351426 |    1844140 | 8/11/2017 |  0.25 |     4 |
| 352132 |    1844140 | 8/15/2017 |   0.5 |   4.5 |
| 354236 |    1844140 | 8/25/2017 |  0.25 |  4.75 |
| 355580 |    1844140 | 8/29/2017 |  0.25 |     5 |
| 355650 |    1844140 | 9/5/2017  |  0.25 |  5.25 |
+--------+------------+-----------+-------+-------+

如何将 1 年滚动时间范围添加到 MS SQL 的 Over 语句中？

Answer 1

正如 James 提到的，您原来的子查询应该可以工作。我在我的环境中测试了它，它返回了正确的结果。

WITH tu AS ( 
   SELECT *
   FROM   [Attendance].[dbo].[timeuse] d
   WHERE lngEMPID =  1844140 AND absence = 'Unscheduled' AND lngRID IN (1,2,3,4,5,7,8,9,10,11,12,13,14)
)
SELECT lngTIMEID as Id,
        lngEMPID as EmployeeId,
        dtmdateapp AS [Date], 
        stroccur AS [Value],
        (SELECT Sum(stroccur) 
         FROM   tu a 
         WHERE  a.dtmdateapp between DateAdd(d, -366, d.dtmdateapp) 
                AND d.dtmDATEAPP
                AND a.lngempid = d.lngempid) AS Total
FROM   tu d