从表格贷方借方列中选择运行余额

Question

我有一个SQL Server 2008表，我需要从中选择一个运行余额

TransDate    Credit    Debit    Datasource
------------------------------------------
2014-01-01   5000      NULL     3
2014-01-07   NULL      2000     3
2014-01-11   5000      NULL     3
2014-02-03   6000      NULL     4
2014-02-06   NULL      4000     4
2014-02-11   3000      NULL     4
2014-02-21   NULL      1000     3
2014-02-28   2000      NULL     3
2014-03-01   5000      NULL     3

我尝试了相关查询

Select 
    t.TransDate, 
    t.Credit, 
    t.Debit, 
    (Select sum(coalesce(x.credit, 0) - coalesce(x.debit, 0))  
    From Transactions x 
    WHERE x.DataSource IN (3,4)  AND (x.TransDate >= '2014/02/01' AND x.TransDate <= '2014/02/28' ) 
    AND x.TransDate = t.TransDate) Balance
From 
    Transactions t

但是我得到了所有的空值Balance。

预期输出为

TransDate    Credit    Debit     Balance
------------------------------------------
2014-01-11   NULL      NULL      8000      <- opening balance     
2014-02-03   6000      NULL      14000
2014-02-06   NULL      4000      10000
2014-02-11   3000      NULL      13000
2014-02-21   NULL      1000      12000
2014-02-28   2000      NULL      15000     <- closing balance

Answer 1

您需要自加入表。

CREATE TABLE Test
(
  TransDate DATE,
  Credit INT,
  Debit INT,
);
INSERT INTO Test VALUES
('2014-01-01',   5000,      NULL),  
('2014-01-07',   NULL,      2000),   
('2014-01-11',   5000,      NULL),   
('2014-02-03',   6000,      NULL),    
('2014-02-06',   NULL,      4000),    
('2014-02-11',   3000,      NULL),   
('2014-02-21',   NULL,      1000),     
('2014-02-28',   2000,      NULL),     
('2014-03-01',   5000,      NULL)

WITH CTE AS
(
SELECT t2.TransDate, 
       t2.Credit, 
       t2.Debit, 
       SUM(COALESCE(t1.credit, 0) - COALESCE(t1.debit, 0)) AS Balance
FROM Test t1 
INNER JOIN Test t2
    ON t1.TransDate <= t2.TransDate
WHERE t1.DataSource IN (3,4)  
GROUP BY t2.TransDate, t2.Credit, t2.Debit
)
SELECT * 
FROM CTE
WHERE (TransDate >= '2014/01/11' AND TransDate <= '2014/02/28' )

输出

TransDate   Credit  Debit   Balance
2014-01-11  5000    (null)  8000
2014-02-03  6000    (null)  14000
2014-02-06  (null)  4000    10000
2014-02-11  3000    (null)  13000
2014-02-21  (null)  1000    12000
2014-02-28  2000    (null)  14000

SQL字段