Here is a Window function running total example, performs quite nicely;-)
😎
USE tempdb;
GO
create table #current(acctNum int,
dates date,
transtype char(10),
amt INT
)
insert into #current(acctNum, dates, transtype, amt)
values (11,'7/1/2014','debit',1),
(11,'7/2/2014','debit',3),
(12,'7/3/2014','credit',5)
create table #balance (acctNum int,
AsOfDate date,
balance INT
)
insert into #balance(acctNum, asofdate, balance)
values(11,'7/1/2014',35),
(12,'7/1/2014',12)
;WITH BASE_DATA AS
(
SELECT
acctNum
,asofdate
,'balance' AS transtype
,balance
FROM #balance
UNION ALL
SELECT
acctNum
,dates
,transtype
,amt
FROM #current
)
SELECT
BD.acctNum
,BD.asofdate
,BD.transtype
,BD.balance
,SUM(BD.balance) OVER
(
PARTITION BY BD.acctNum
ORDER BY BD.asofdate
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS CURRENT_BALANCE
FROM BASE_DATA BD;
DROP TABLE #balance;
DROP TABLE #current;
Results
acctNum asofdate transtype balance CURRENT_BALANCE
----------- ---------- ---------- ----------- ---------------
11 2014-07-01 balance 35 35
11 2014-07-01 debit 1 36
11 2014-07-02 debit 3 39
12 2014-07-01 balance 12 12
12 2014-07-03 credit 5 17
And another with a trantype twist
USE tempdb;
GO
create table #current(acctNum int,
dates date,
transtype char(10),
amt INT
)
insert into #current(acctNum, dates, transtype, amt)
values (11,'7/1/2014','debit',1),
(11,'7/2/2014','debit',3),
(12,'7/3/2014','credit',5)
create table #balance (acctNum int,
AsOfDate date,
balance INT
)
insert into #balance(acctNum, asofdate, balance)
values(11,'7/1/2014',35),
(12,'7/1/2014',12)
;WITH BASE_DATA AS
(
SELECT
acctNum
,asofdate
,1 AS transtype
,balance
FROM #balance
UNION ALL
SELECT
acctNum
,dates
,CASE
WHEN transtype = 'credit' THEN 1
ELSE -1
END AS transtype
,amt
FROM #current
)
SELECT
BD.acctNum
,BD.asofdate
,BD.transtype
,BD.balance
,SUM(BD.balance * BD.transtype) OVER
(
PARTITION BY BD.acctNum
ORDER BY BD.asofdate
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS CURRENT_BALANCE
FROM BASE_DATA BD;
DROP TABLE #balance;
DROP TABLE #current;
Results
acctNum asofdate transtype balance CURRENT_BALANCE
----------- ---------- ----------- ----------- ---------------
11 2014-07-01 1 35 35
11 2014-07-01 -1 1 34
11 2014-07-02 -1 3 31
12 2014-07-01 1 12 12
12 2014-07-03 1 5 17