MSSQL Sqlerver Script calculates the balance of the data sheet by sharing the method

Transferred from: http://www.maomao365.com/?p=5710

Summary:
Today, we received a request, a data sheet, a record of consumption details,
Now you need to make a cumulative balance, record the total of each data balance,
The following shows how a SQL script is written
Lab environment: SQL Server R2
As shown in the following example:

Detail Memento Account flow chart==========field: Qt_srmoney ( Money) Revenue Qt_zcmoney ( Money) Expenditure qt_date (datetime) Operation Date Qt_dkfs (nvarchar) How to make money------------------need to get a data message with balance==========The balances in the accounting table are queried based on the dates and amounts in the opening table. Book balance = opening amount+revenue-Expense lab environment: SQL Server -R2*/ Create TableDetail (Qt_srmoney Money, Qt_zcmoney Money, Qt_datedatetime, Qt_dkfsnvarchar( -))Insert  intoDetailValues( -,0,'2018-1-2 10:00:00',' Other')Insert  intoDetailValues(0,Ten,'2018-2-2 10:00:00',' Other')Insert  intoDetailValues(0, -,'2018-3-2 10:00:00',' Other')Insert  intoDetailValues( -,0,'2018-4-2 10:00:00',' Other')Insert  intoDetailValues(Ten, -,'2018-5-2 10:00:00',' Other')GoDeclare @datestart datetime ---Start Calculation dateSet @datestart ='2018-2-1'Declare @qcomney  Money --early StageSet @qcomney = -  SelectT1.qt_date,t1.qt_dkfs,t1.qt_srmoney,t1.qt_zcmoney,sum(T2.qt_srmoney-T2.qt_zcmoney)+@qcomney fromdetail T1,detail T2whereT1.qt_date>=t2.qt_date andT1.qt_date>=@datestartGroup  byT1.qt_date,t1.qt_dkfs,t1.qt_srmoney,t1.qt_zcmoneyOrder  byt1.qt_date;Go---Avoid date two results in calculation of the balance data is abnormal-----Declare @datestart datetime ---Start Calculation dateSet @datestart ='2018-2-1'Declare @qcomney  Money --early StageSet @qcomney = - withCtename as (  SelectQt_srmoney,qt_zcmoney,qt_date,qt_dkfs, row_number () Over(Order  byQt_date) asKeyID fromDetailwhereQt_date>=@datestart)  SelectT1.keyid,t1.qt_date,t1.qt_dkfs,t1.qt_srmoney,t1.qt_zcmoney,sum(T2.qt_srmoney-T2.qt_zcmoney)+@qcomney fromctename t1,ctename T2whereT1.keyid>=T2.keyidGroup  byT1.qt_date,t1.qt_dkfs,t1.qt_srmoney,t1.qt_zcmoney,t1.keyidOrder  byT1.keyid;Gotruncate TableDetailDrop TableDetail

MSSQL Sqlerver Script calculates the balance of the data sheet by sharing the method