Here is an example:
SET DATEFORMAT dmy;
CREATE TABLE #purchases (PurID INT IDENTITY(1,1), ProviderId INT, Date DATETIME, Amount DECIMAL(8,2))
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 TABLE #payments (PayID INT IDENTITY(1,1), ProviderId INT, Date DATETIME, Amount DECIMAL(8,2))
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 @cols VARCHAR(300)
SET @cols = '[Purchase],[Payment]'
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)
DECLARE @ft VARCHAR(MAX)
SET @dt ='SELECT ProviderId, Date, Amount, ''Purchase'' AS [Operation] ' +
'FROM #purchases ' +
'UNION ALL ' +
'SELECT ProviderId, Date, Amount, ''Payment'' AS [Operation] ' +
'FROM #payments '
SET @pt = 'SELECT ProviderId, Date, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT(SUM(Amount) FOR Operation IN(' + @cols + ')) AS PT '
CREATE TABLE #tmp (OperationId INT, ProviderId INT, Date DATETIME, Purchase DECIMAL(8,2), Payment DECIMAL(8,2))
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)
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
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 ;)