CTE的最终余额计算 [英] Final Balance Calculation with CTE

问题描述

大家好，
我目前面临一个问题，希望有人能帮助我.
这是场景.
我有一张简单的桌子.
会员表
================

会员编号名称Opening_Balance Balance_Type
1大卫5000借记卡


交易表
=================
Transaction_id Transaction_date会员ID说明借方贷方
1 10/01/2011 1现金500 0
2 10/02/2011 1现金0 1000
3 10/03/2011 1现金500 0
4 10/04/2011 1现金0 8000

现在，我需要在每个条目及其类型之后显示余额.最终结果将如下所示.

交易明细视图
=======================
Transaction_id Transaction_date会员ID说明借方贷方余额Balance_Type
1 10/01/2011 1 Cash 500 0 5500 Debit
2 10/02/2011 1现金0 1000 4500借方
3 10/03/2011 1现金500 0 5000借方
4 10/04/2011 1现金0 8000 3000贷方

如何创建此视图?通过使用成员表中的期初余额来准备最终余额，然后通过上一个记录到下一个记录的余额进行每次计算，并相应地更改余额类型.
我希望有一些专家会有所帮助.

Hi All,
i am currently facing an issue and hope that someone will help me.
here is the scenario.
I have a simple table.
Member Table
================

Member_id Name Opening_Balance Balance_Type
1 David 5000 Debit


Transaction Table
=================
Transaction_id Transaction_date Member_id Description Debit Credit
1 10/01/2011 1 Cash 500 0
2 10/02/2011 1 Cash 0 1000
3 10/03/2011 1 Cash 500 0
4 10/04/2011 1 Cash 0 8000

Now what i need to show the Balance after each entry and its Type. The final result will be look like this.

Transaction Details View
========================
Transaction_id Transaction_date Member_id Description Debit Credit Balance Balance_Type
1 10/01/2011 1 Cash 500 0 5500 Debit
2 10/02/2011 1 Cash 0 1000 4500 Debit
3 10/03/2011 1 Cash 500 0 5000 Debit
4 10/04/2011 1 Cash 0 8000 3000 Credit

How can i create this view? the final balance is prepared by using the opening balance from the members table and then each calculation is made by the balance of previous record to the next and balance type is changed accordingly.
I hope that some expert will help.

推荐答案

看看本文 ^ ]，看看是否可以使用它来使您的运行正常.

Have a look at this article Calculating Running Totals[^] and see if you can use it to get your running total working.

这篇关于CTE的最终余额计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！