July 25, 2014 at 12:40 pm
Table A has day to day transactions, Table B has beginning balance. I'd like to get a running total balance day to day. Really what I want to do is use the previous days total to add the current days transaction to, but I don't know how to do it. The basic layout is below, but as you can see, I'm not getting the totals correct.
Any help is appreciated!
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)
select a.acctnum,
a.dates,
a.transtype,
b.balance,
a.amt,
runningtotal = b.balance + a.amt
from #current a inner join #balance b
on a.acctNum = b.acctNum
July 25, 2014 at 12:54 pm
See this article for one way to do this. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
Make sure you read and understand what is happening here. There are a number of thing that MUST be in place for this to work correctly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2014 at 1:04 pm
If you're using SQL Server 2012, the easiest method (not the fastest) would be something like this:
select a.acctnum,
a.dates,
a.transtype,
b.balance,
a.amt,
runningtotal = b.balance + SUM(a.amt) OVER( PARTITION BY a.acctnum ORDER BY a.dates)
from #current a
join #balance b on a.acctNum = b.acctNum AND a.dates >= b.AsOfDate
July 25, 2014 at 1:14 pm
Thank you - just what I was looking for!
July 26, 2014 at 4:03 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply