在特定日期找到最后的余额

Question

我有一张存款/撤回交易表。我想在给定日期找到每个帐户的余额。

acctno  date  trantype   amount balance seqno
12     1/2/14   dep       100    100      1
12     3/2/14   wdl        50     50      2
12     1/3/14   dep       200     250     3
13     2/2/14   dep       500     500     1
13     5/2/14   dep       100     600     2
13     5/4/14   dep       100     700     2
14     1/3/14   dep       200     200     1

现在我想在2014年1月4日找到每个帐户的余额，而reult应该是这样的

acctno  balance
12        250
13        600
14        200

Answer 1

尝试以下：

SELECT T1.Acctno, T1.Balance
FROM YourTable T1 INNER JOIN
(
    SELECT T2.Acctno, T2.Date, MAX(T2.seqno) As MaxSeqno
    FROM YourTable T2
    GROUP BY T1.Acctno, T2.Date
)
ON(T1.Acctno = T2.Acctno AND T1.Date = T2.Date AND T1.seqno = MaxSeqno)
WHERE T1.Date = '2014-04-01'

Answer 2

尝试

    select * from (select *,row_number() over 
(partition by acctno order by date desc)  as rno 
from yourtable t WHERE T.Date <= '2014-04-01') t1 where rno=1