Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Looping through the recordset to assign different balance value to each transaction row Expand / Collapse
Author
Message
Posted Wednesday, October 6, 2010 10:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 8:38 AM
Points: 16, Visits: 75
I have two tables that stores balance and transaction information about an account.
The first table stores all the transaction with transaction amount and effective date. The transaction has a code that differentiates deposit or withdrawal.
The other table stores balance but this balance is not historical. It is a rolling balance. It keeps on updating every time there is a transaction.
There is no place that stores the historical balance information after each transaction.
How do I calculate balance after each transaction in the history table using the current balance and the transaction code? Below is the example…
This is how the balance is stored
acct_no acct_type effective_dt cur_bal
112233 SV 9/4/2003 181447.83

This is how the transaction is stored.
acct_no acct_type effective_dt tran_code amt
112233 SV 12/31/2003 110 100.16
112233 SV 1/6/2004 101 850
112233 SV 1/20/2004 101 1400
112233 SV 1/31/2004 110 105.58
112233 SV 2/2/2004 101 284
112233 SV 2/10/2004 101 1000
112233 SV 2/17/2004 101 600
112233 SV 2/29/2004 110 104.7
112233 SV 3/2/2004 101 400
112233 SV 3/29/2004 101 1200
112233 SV 3/31/2004 110 114.2
112233 SV 4/12/2004 101 700

I want to able to assign the end balance after each transaction based on the current balance. The row with the max date gets the cur balance and then you should be able to work back in time. Any transaction with Tran code of 110 I want to add to the cur bal and any 101 I want to subtract. How do I loop through or what can I do to achieve the result like below?
acct_no acct_type effective_dt tran_code amt Balance
112233 SV 12/31/2003 110 100.16 176138.5
112233 SV 1/6/2004 101 850 176038.3
112233 SV 1/20/2004 101 1400 176888.3
112233 SV 1/31/2004 110 105.58 178288.3
112233 SV 2/2/2004 101 284 178182.7
112233 SV 2/10/2004 101 1000 178466.7
112233 SV 2/17/2004 101 600 179466.7
112233 SV 2/29/2004 110 104.7 180066.7
112233 SV 3/2/2004 101 400 179962
112233 SV 3/29/2004 101 1200 180362
112233 SV 3/31/2004 110 114.2 181562
112233 SV 4/12/2004 101 700 181447.8

Post #999572
Posted Wednesday, October 6, 2010 10:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 12,910, Visits: 32,023
what you are after is a "Running Total" type of query;
what you end up doing is join the table to itself with an alias , by offsetting one row to the next;
so you need to be able to join row 1 to row 2;
you do that with row_number() usually;
something like this as an example:
SELECT a.cur_bal +  b.cur_bal
from a
left outer join b
on a.ID = B.ID + 1

here's your data formatted as consumable data for others to play with:

SELECT '112233' as acct_no,'SV' as acct_type,'9/4/2003' as effective_dt,'181447.83'  as cur_bal UNION ALL
SELECT '112233','SV','12/31/2003','110','100.16' UNION ALL
SELECT '112233','SV','1/6/2004','101','850' UNION ALL
SELECT '112233','SV','1/20/2004','101','1400' UNION ALL
SELECT '112233','SV','1/31/2004','110','105.58' UNION ALL
SELECT '112233','SV','2/2/2004','101','284' UNION ALL
SELECT '112233','SV','2/10/2004','101','1000' UNION ALL
SELECT '112233','SV','2/17/2004','101','600' UNION ALL
SELECT '112233','SV','2/29/2004','110','104.7' UNION ALL
SELECT '112233','SV','3/2/2004','101','400' UNION ALL
SELECT '112233','SV','3/29/2004','101','1200' UNION ALL
SELECT '112233','SV','3/31/2004','110','114.2' UNION ALL
SELECT '112233','SV','4/12/2004','101','700'



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #999589
Posted Wednesday, October 6, 2010 10:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 8:38 AM
Points: 16, Visits: 75
Thank you for the reply. But the cur balance is in one table and the transaction is in another. I would have to use cur balance and remember the previous cur balance to calculate cur_bal for the previous transaction as I go up the list. I don't know if i make any sense. Please let me know.
Post #999601
Posted Wednesday, October 6, 2010 10:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 12,910, Visits: 32,023
yogesh_pandey (10/6/2010)
Thank you for the reply. But the cur balance is in one table and the transaction is in another. I would have to use cur balance and remember the previous cur balance to calculate cur_bal for the previous transaction as I go up the list. I don't know if i make any sense. Please let me know.


the concept is still the same.; it's still a running total type of situation.

you join the main table to the transaction table to get the starting row and all the child rows. those two tables joined together are a query(just like the example data i posted);
then you can join them together, offset by a row_number() function.

without you providing the actual CREATE TABLE definitions and some sample data in a consumable format, like i posted, all i can do is offer concepts on how to tackle the issue.

or of course you could help us help you....


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #999620
Posted Wednesday, October 6, 2010 10:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 8:38 AM
Points: 16, Visits: 75
Thank you. Can I send you a DDL and may be you can help. Please let me know.
Post #999624
Posted Wednesday, October 6, 2010 10:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 12,910, Visits: 32,023
yogesh_pandey (10/6/2010)
Thank you. Can I send you a DDL and may be you can help. Please let me know.


post the DDL and sample data here; there's lots of volunteers on SSC that will jump to offer an example if you give them enough to work with in SSMS....


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #999628
Posted Wednesday, October 6, 2010 11:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 8:38 AM
Points: 16, Visits: 75
Good Afternoon Lowell,
Here is the DDL. Please help. Thank you.

---For current balance

CREATE TABLE #Display
(
acct_no int,
acct_type char(3),
effective_dt datetime,
cur_bal decimal(18,2)
)
INSERT INTO #Display
(
acct_no,
acct_type,
effective_dt,
cur_bal
)
(
SELECT '112233' as acct_no,'SV' as acct_type,'9/4/2003' as effective_dt,'181447.83' as cur_bal
)

----For transactions

CREATE TABLE #Transaction
(
acct_no int,
acct_type char(3),
effective_dt datetime,
tran_code int,
amt decimal(18,2)
)
INSERT INTO #Transaction
(
acct_no,
acct_type,
effective_dt,
tran_code,
amt
)
(
SELECT '112233','SV','12/31/2003','110','100.16' UNION ALL
SELECT '112233','SV','1/6/2004','101','850' UNION ALL
SELECT '112233','SV','1/20/2004','101','1400' UNION ALL
SELECT '112233','SV','1/31/2004','110','105.58' UNION ALL
SELECT '112233','SV','2/2/2004','101','284' UNION ALL
SELECT '112233','SV','2/10/2004','101','1000' UNION ALL
SELECT '112233','SV','2/17/2004','101','600' UNION ALL
SELECT '112233','SV','2/29/2004','110','104.7' UNION ALL
SELECT '112233','SV','3/2/2004','101','400' UNION ALL
SELECT '112233','SV','3/29/2004','101','1200' UNION ALL
SELECT '112233','SV','3/31/2004','110','114.2' UNION ALL
SELECT '112233','SV','4/12/2004','101','700'
)
Post #999637
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse