如何：在SQL SERVER中以累计方式计算Blance（按日期排序）（另请参阅：Vb.net或CrustalReports） [英] How to : Compute Blance in Cummulative Way (Ordered by Date) in SQL SERVER ( Else in : Vb.net OR CrustalReports )
问题描述
您好，
我会尽量简短。这是我的问题：
我正在使用SQL SERVER数据库（商店管理）在VB.Net中开发一个应用程序。
以下是与我当前问题相关的表：
1-提供者表：每个提供者都有一个BALANCE（我从这个提供商处购买的商品与我同一供应商的购买付款之间的差额）。
2-购买表：每个购买操作都有DATE和AMOUNT。 （FK =提供商ID）
3-付款表：每个付款操作都有DATE和AMOUNT。 （FK =提供商ID）
从这三个表中，我的客户希望获得每个提供商的平均计算历史记录如下：
提供商的平衡情况：X
_______________________________________
Hello ,
I will try to make it brief. Here is my problem :
I'm developing an application in VB.Net using SQL SERVER Database( Store Management ).
Here are the tables related to my current problem :
1- Providers table : Each provider has a BALANCE ( The difference between My purchases from this provider and The Payments of my Purchases for the same Provider ).
2- Purchases Table : Each Purchase operation has a DATE and an AMOUNT. (FK = Provider ID)
3- Payments Table : Each Payment operation has a DATE and an AMOUNT. (FK = Provider ID)
From these three Tables my client wants to get the History of Computing the BALANCE of each provider as following :
BALANCE SITUATION OF THE PROVIDER : X
_______________________________________
DATE......../..PURCHASES ......./..PAYMENTS........./.....BALANCE
____________________________________________________________________
01/01/2013 ./....... 1200 ....../...... Null ......./..... - 1200 (Initial Balance= 0)
03/01/2013 ./....... 800 ....../...... Null ......./..... - 2000
10/01/2013 ./....... Null ....../...... 2000 ......./...........0
15/01/2013 ./....... 1500 ....../...... 1500 ......./...........0
18/01/2013 ./....... 3000 ....../...... 1000 ......./..... - 2000
20/01/2013 ./....... Null ....../...... 1000 ......./...... -1000
23/01/2013 ./....... Null ....../...... 1000 ......./.......... 0 -------------- -------------------------------------------------- ------------
我希望桌子看起来不错。
注意：
1.日期栏汇总了购买日和付款日期。
2. 当前余额=（以前的平衡） - （当前购买金额）+（当前付款金额）
示例：在2013年1月18日：BALANCE = 0 - 3000 + 1000 = -2000
我尝试了很多方法但是徒劳无功。
SO我该怎么做：在SQL SERVER中（或者如果在VB.NET和Crystal Reports中有解决方案）？
如果有错误或需要明确的事情请提及。
我将非常感谢您的帮助提前谢谢：）
----------------------------------------------------------------------------
I hope the table looks good.
NB :
1.The Date Column makes together the Purchases and the Payments Dates.
2. Current BALANCE = (Previous BALANCE) - (Current PURCHASE AMOUNT) + (CURRENT PAYMENT AMOUNT)
Example : In 18/01/2013 : BALANCE = 0 - 3000 + 1000 = -2000
I tried many ways but in vain .
SO How Can I DO That : In SQL SERVER ( Or If there is a solution in VB.NET and Crystal Reports) ?
If there is a mistake or something that needs to be clear Please mention it .
I will really appreciate your help Thank you in advance : )
推荐答案
这是一个例子：
Here is an example:
SET DATEFORMAT dmy;
--create temporary table
CREATE TABLE #purchases (PurID INT IDENTITY(1,1), ProviderId INT, Date DATETIME, Amount DECIMAL(8,2))
--insert data
INSERT INTO #purchases (ProviderId, Date, Amount)
SELECT 1, '01/01/2013', 1200
UNION ALL SELECT 1, '03/01/2013', 800
UNION ALL SELECT 1, '15/01/2013', 1500
UNION ALL SELECT 1, '18/01/2013', 3000
--create temporary table
CREATE TABLE #payments (PayID INT IDENTITY(1,1), ProviderId INT, Date DATETIME, Amount DECIMAL(8,2))
--insert data
INSERT INTO #payments (ProviderId, Date, Amount)
SELECT 1, '10/01/2013', 2000
UNION ALL SELECT 1, '15/01/2013', 1500
UNION ALL SELECT 1, '18/01/2013', 1000
UNION ALL SELECT 1, '20/01/2013', 1000
UNION ALL SELECT 1, '23/01/2013', 1000
--declare and set varaible to store columns name
DECLARE @cols VARCHAR(300)
SET @cols = '[Purchase],[Payment]'
--declare and set variables
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)
DECLARE @ft VARCHAR(MAX)
--source for pivot
SET @dt ='SELECT ProviderId, Date, Amount, ''Purchase'' AS [Operation] ' +
'FROM #purchases ' +
'UNION ALL ' +
'SELECT ProviderId, Date, Amount, ''Payment'' AS [Operation] ' +
'FROM #payments '
--pivot table
SET @pt = 'SELECT ProviderId, Date, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT(SUM(Amount) FOR Operation IN(' + @cols + ')) AS PT '
--declare temporary table to store result of pivot
CREATE TABLE #tmp (OperationId INT, ProviderId INT, Date DATETIME, Purchase DECIMAL(8,2), Payment DECIMAL(8,2))
--insert data into temporary table
--use COALESCE to replace NULL values with zeros
SET @ft = 'INSERT INTO #tmp(OperationId, ProviderId, Date, Purchase, Payment) ' +
'SELECT ROW_NUMBER() OVER(PARTITION BY ProviderId ORDER BY Date) AS OperationID, ProviderId, Date, COALESCE(Purchase*-1,0) AS Purchase, COALESCE(Payment,0) AS Payment ' +
'FROM (' + @pt + ') AS T'
EXEC(@ft)
--view balance
SELECT ST.OperationId, ST.ProviderId, ST.Date, AT.Purchase, AT.Payment, ST.CumPur + ST.CumPay AS CurrentBalance
FROM (
SELECT t1.OperationId, t1.ProviderId, t1.Date, SUM(t2.Purchase) AS CumPur, SUM(t2.Payment) AS CumPay
FROM #tmp AS t1 INNER JOIN #tmp AS t2 ON t1.OperationId>=t2.OperationId AND t1.ProviderId = t2.ProviderId
GROUP BY t1.OperationId, t1.ProviderID, t1.Date
) AS ST INNER JOIN #tmp AS AT ON ST.OperationId = AT.OperationId AND ST.ProviderId = AT.ProviderId
ORDER BY ST.OperationId
--delete temporary tables
DROP TABLE #tmp
DROP TABLE #purchases
DROP TABLE #payments
结果：
Result:
OperationID ProviderId Date Purchase Payment CurrentBalance
1 1 2013-01-01 -1200.00 0.00 -1200.00
2 1 2013-01-03 -800.00 0.00 -2000.00
3 1 2013-01-10 0.00 2000.00 0.00
4 1 2013-01-15 -1500.00 1500.00 0.00
5 1 2013-01-18 -3000.00 1000.00 -2000.00
6 1 2013-01-20 0.00 1000.00 -1000.00
7 1 2013-01-23 0.00 1000.00 0.00
其余属于你;）
The rest belongs to you ;)
这篇关于如何：在SQL SERVER中以累计方式计算Blance（按日期排序）（另请参阅：Vb.net或CrustalReports）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
