Использование оконной функции SQL Server для подсчета итогов за финансовый год

Question

Я использую SQL Server 2014. У меня есть таблица утверждений, содержащая итоги заявлений, поданных в моей системе за месяц:

+-----------+-------------+------------+
| Claim_ID  | Claim_Date  | Nett_Total |
+-----------+-------------+------------+
| 1         | 31 Jan 2012 |  321454.67 |
| 2         | 29 Feb 2012 |  523542.34 |
| 3         | 31 Mar 2012 |   35344.33 |
| 4         | 30 Apr 2012 |  142355.63 |
| etc.      | etc.        | etc.       |
+-----------+-------------+------------+

Для отчета, который я пишу, мне нужно иметь возможность составить итоговую промежуточную сумму, которая сбрасывается в ноль в начале каждого финансового года (в моей стране это с 1 марта по 28/29 февраля следующего года).

Отчет будет выглядеть аналогично таблице с дополнительным столбцом промежуточных итогов, что-то вроде:

+-----------+-------------+------------+---------------+
| Claim_ID  | Claim_Date  | Nett_Total | Running Total |
+-----------+-------------+------------+---------------+
| 1         | 31 Jan 2012 |  321454.67 |     321454.67 |
| 2         | 29 Feb 2012 |  523542.34 |     844997.01 |
| 3         | 31 Mar 2012 |   35344.33 |      35344.33 | (restart at 0
| 4         | 30 Apr 2012 |  142355.63 |     177699.96 |  for new yr) 
| etc.      | etc.        | etc.       |               |
+-----------+-------------+------------+---------------+

Я знаю, что оконные функции очень мощные, и в прошлом я использовал их элементарными способами, чтобы получить общие суммы и средние значения, избегая при этом необходимости группировать строки набора результатов. У меня есть интуиция, что мне нужно будет использовать ключевое слово "предыдущий", чтобы получить промежуточную сумму за текущий финансовый год, в который попадает каждая строка, но я не совсем понимаю, как выразить финансовый год как концепцию, которую можно использовать в пункт "предыдущий" (или, если возможно, таким образом можно использовать диапазон дат).

Любая помощь на пути "формулировки" финансового года для "предыдущей" статьи будет мне очень полезна, пожалуйста.

Answer 1

Я думаю, вы должны попробовать это:

/* Create Table*/
CREATE TABLE dbo.Claims (
 Claim_ID int
,Claim_Date datetime
,Nett_Total decimal(10,2)
);

/* Insert Testrows*/
INSERT INTO dbo.Claims VALUES 
 (1, '20120101', 10000)
,(2, '20120202', 10000)
,(3, '20120303', 10000)
,(4, '20120404', 10000)
,(5, '20120505', 10000)
,(6, '20120606', 10000)
,(7, '20120707', 10000)
,(8, '20120808', 10000)

Запрос данных:

SELECT  Claim_ID, Claim_Date, Nett_Total, SUM(Nett_Total) OVER
(PARTITION BY YEAR(DATEADD(month,-2,Claim_Date)) ORDER BY Claim_ID) AS
[Running Total] FROM dbo.Claims

Трюк: PARTITION BY YEAR(DATEADD(month,-2,Claim_Date))

Новый раздел по годам, но я меняю дату, чтобы она соответствовала вашему финансовому году.

Выход:

Claim_ID |Claim_Date                 |Nett_Total  |Running Total
---------+---------------------------+------------+-------------
1        |2012-01-01 00:00:00.000    |10000.00    |10000.00
2        |2012-02-02 00:00:00.000    |10000.00    |20000.00
3        |2012-03-03 00:00:00.000    |10000.00    |10000.00   <- New partition
4        |2012-04-04 00:00:00.000    |10000.00    |20000.00
5        |2012-05-05 00:00:00.000    |10000.00    |30000.00
6        |2012-06-06 00:00:00.000    |10000.00    |40000.00
7        |2012-07-07 00:00:00.000    |10000.00    |50000.00
8        |2012-08-08 00:00:00.000    |10000.00    |60000.00