如何获得期初余额的运行总计? [英] How can I get a running total with opening balance ?
问题描述
我有此表tbl_CustJournals
J_Id日期编号类型custId借方贷方净额
1 17/01/2018 5000发票2503 1472.78 0 1472.78
2 17/01/2018 1薪水2503 0 980 -980
3 07/02/2018 5发票2503 318.8 0 318.8
4 17/01/2018 2薪水2503 0 492 -492
5 09/02/2018 5发票270 4490 0 4490
6 07/02/2018 3薪水270 0 900 -900
7 10/02/2018 55发票65 14000 0 14000
8 17/01/2018 23发票65520 0520
9 17/01/2018 24发票65525 0 525
10 17/01/2018 24发票65 1400 0 1400
11 17/01/2018 25发票65 1400 0 1400
12 17/01/2018 25发票65975 0 975
13 17/01/2018 26发票65 3310 0 3310
14 17/01/2018 252发票65 3708.1 0 3708.1
15 04/02/2018 27发票2503 14000 0 14000
16 04/02/2018 28发票2503 14000 0 14000
17 17/02/2018 29发票65640 0640
18 07/02/2018 4薪水2503 0 999 -999
19 07/02/2018 4薪水2503 0 900 -900
20 07/02/2018 4薪水2503 0 101 -101
21 07/02/2018 4薪水2503 0 900 -900
22 17/01/2018 78发票65 180.99 0 180.99
23 17/01/2018 78发票65231 0231
我想拥有总跑步量
我尝试过的事情:
我尝试过此程序
i have this table tbl_CustJournals
J_Id Date Num Type custId Debit Credit Net
1 17/01/2018 5000 Invoice 2503 1472.78 0 1472.78
2 17/01/2018 1 Pay 2503 0 980 -980
3 07/02/2018 5 Invoice 2503 318.8 0 318.8
4 17/01/2018 2 Pay 2503 0 492 -492
5 09/02/2018 5 Invoice 270 4490 0 4490
6 07/02/2018 3 Pay 270 0 900 -900
7 10/02/2018 55 Invoice 65 14000 0 14000
8 17/01/2018 23 Invoice 65 520 0 520
9 17/01/2018 24 Invoice 65 525 0 525
10 17/01/2018 24 Invoice 65 1400 0 1400
11 17/01/2018 25 Invoice 65 1400 0 1400
12 17/01/2018 25 Invoice 65 975 0 975
13 17/01/2018 26 Invoice 65 3310 0 3310
14 17/01/2018 252 Invoice 65 3708.1 0 3708.1
15 04/02/2018 27 Invoice 2503 14000 0 14000
16 04/02/2018 28 Invoice 2503 14000 0 14000
17 17/02/2018 29 Invoice 65 640 0 640
18 07/02/2018 4 Pay 2503 0 999 -999
19 07/02/2018 4 Pay 2503 0 900 -900
20 07/02/2018 4 Pay 2503 0 101 -101
21 07/02/2018 4 Pay 2503 0 900 -900
22 17/01/2018 78 Invoice 65 180.99 0 180.99
23 17/01/2018 78 Invoice 65 231 0 231
i want to have A running total
What I have tried:
i tried this Procedure
ALTER procedure [dbo].[SP_Customer_Get_Balnance_By_Declare_Table]
@custcode int,
@datefrom date,
@dateto date
as
DECLARE @st TABLE
(
[Date] DATE ,
J_Number int,
J_Type varchar(25),
[Description] varchar(max),
Debit money,
Credit money,
Gl_Debits money,
Net_Balance money
);
DECLARE @NetBalance money = 0;
INSERT @st([Date],J_Number,J_Type,[Description],Debit,Credit, Gl_Debits, Net_Balance)
SELECT Journal_Date,Journal_Num,Journal_Type,[Description],Debit,Credit, GL_Debits, RunningTotal = 0
FROM dbo.Tbl_Cust_Journals
where Tbl_Cust_Journals.custId =@custcode and Journal_Date between @datefrom and @dateto
ORDER BY Journal_Date;
UPDATE @st
SET @NetBalance = Net_Balance = @NetBalance + Gl_Debits
FROM @st;
SELECT [Date] ,J_Number ,J_Type ,[Description] ,Debit ,Credit , Gl_Debits , Net_Balance
FROM @st
ORDER BY [Date];
return
但是问题是当监护人为2503并且日期介于"04/02/2018"和"07/02/2018"之间时，我希望获得平衡的余额
有人可以帮我吗
but the problem is when the custid is 2503 and the date is between "04/02/2018" and "07/02/2018" i want to have an oppining balance
can any one help me
推荐答案
假定SQL Server的最新版本:(2012或更高版本)
Assuming a recent version of SQL Server: (2012 or later)
SELECT
Journal_Date,
Journal_Num,
Journal_Type,
[Description],
Debit,
Credit,
GL_Debits,
SUM(GL_Debits) OVER (ORDER BY Journal_Date ROWS BETWEEN Unbounded Preceding And Current Row) As Net_Balance
FROM
dbo.Tbl_Cust_Journals
WHERE
custId = @custcode
And
Journal_Date Between @datefrom And @dateto
ORDER BY
Journal_Date
;
OVER子句(Transact-SQL)| Microsoft文档 [ ^ ]
OVER Clause (Transact-SQL) | Microsoft Docs[^]
这篇关于如何获得期初余额的运行总计?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
