Перед транзакцией скользящий баланс SQL Server 2008

У меня есть пара относительно прямых таблиц (примеры ниже).

У одного есть детали счетов в: 

AccountNo     | CurrentBalance*         | ReferredBalance
12345         | £1254.25                | 1500.00

Текущий баланс обновляется ежечасно, поэтому не является статичным

Другой имеет платежи: 

Accountno     | TranasctionNo   | TransDate   | Amount |
123456        | 558745489       | 01/01/2015  | £25.99 |
123456        | 558745490       | 01/02/2015  | £25.99 |
123456        | 558745491       | 01/02/2015  | £25.99 |

Мне было поручено вести скользящий баланс на основе полученных платежей, включая сумму предварительной транзакции.

Так например мне нужно на выходе зеркально отразить: 

AccountNo | TransactionDate | PreTransactionBalance | Amount | Current Balance|
123456    | 01/01/2015      | 1254.25               | 25.99  | 1228.26|
123456    | 01/02/2015      | 1228.26               | 25.99  | 1202.27|
123456    | 01/03/2015      | 1202.27               | 25.99  | 1176.28|
123456    | 01/03/2015      | 1176.28               | -100   | 1276.28|

Я добавил минус, так как он должен будет вычислить дебет как ну и кредиты.

Не может спокойно решить, как заставить работать скользящие предварительные итоги транзакций. Надеюсь, это достаточно ясно!

1 2
sql-server-2008 common-table-expression

1 ответ:

Вы можете использовать SUM(amount) OVER(), чтобы получить прокатку amount на основе TransactionNo, а затем вычислить предварительную транзакцию и текущий баланс из этого значения.

Примеры Данных 

DECLARE @Account TABLE
(
AccountNo VARCHAR(10) ,
CurrentBalance MONEY,
ReferredBalance MONEY
)

DECLARE @AccountPayment TABLE
(
Accountno VARCHAR(10),TranasctionNo VARCHAR(10),TransDate DATE,Amount MONEY
)
insert into @Account VALUES
('12345','£1254.25','1500.00');

insert into @AccountPayment values
('12345','558745489','01/01/2015','£25.99'),
('12345','558745490','01/02/2015','£25.99'),
('12345','558745491','01/02/2015','£25.99'),
('12345','558745492','01/02/2015','-100');

Запрос 

SELECT AP.AccountNo,
    TransDate,
    A.CurrentBalance - SUM(amount) OVER(ORDER BY TranasctionNo) + amount as PreTransactionBalance ,
    amount,
    A.CurrentBalance - SUM(amount) OVER(ORDER BY TranasctionNo) Current_Balance
FROM @AccountPayment AP
INNER JOIN @Account A
ON AP.Accountno = A.Accountno

Edit

Похоже, что ORDER BY не поддерживается in SUM() OVER() в SQL Server 2008 / SQL Server 2008 R2. Согласно msdn

ORDER BY Clause не может использоваться с функциями aggregate window.

Мы можем использовать CROSS APPLY как этот.

SELECT AP.AccountNo,
    TransDate,
    A.CurrentBalance - pre_amount as PreTransactionBalance ,
    amount,
    A.CurrentBalance - pre_amount - amount Current_Balance
FROM @AccountPayment AP
INNER JOIN @Account A
ON AP.Accountno = A.Accountno
CROSS APPLY
(
     select ISNULL(sum(amount),0) as pre_amount 
     from @AccountPayment ap1 
     where ap1.Accountno = ap.Accountno and ap1.TranasctionNo < ap.TranasctionNo
 ) as b

Вывод 

AccountNo   TransDate   PreTransactionBalance   amount  Prev_Payments
12345   2015-01-01  1254.25 25.99   1228.26
12345   2015-01-02  1228.26 25.99   1202.27
12345   2015-01-02  1202.27 25.99   1176.28
12345   2015-01-02  1176.28 -100.00 1276.28

SQL Fiddle

1