InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 06:41:48 GMT20http://www.sqlservercentral.com/Forums/Topic999572-391-1.aspxGood Afternoon Lowell, Here is the DDL. Please help. Thank you.---For current balanceCREATE 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 transactionsCREATE 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 ALLSELECT '112233','SV','1/6/2004','101','850' UNION ALLSELECT '112233','SV','1/20/2004','101','1400' UNION ALLSELECT '112233','SV','1/31/2004','110','105.58' UNION ALLSELECT '112233','SV','2/2/2004','101','284' UNION ALLSELECT '112233','SV','2/10/2004','101','1000' UNION ALLSELECT '112233','SV','2/17/2004','101','600' UNION ALLSELECT '112233','SV','2/29/2004','110','104.7' UNION ALLSELECT '112233','SV','3/2/2004','101','400' UNION ALLSELECT '112233','SV','3/29/2004','101','1200' UNION ALLSELECT '112233','SV','3/31/2004','110','114.2' UNION ALLSELECT '112233','SV','4/12/2004','101','700')Wed, 06 Oct 2010 11:04:04 GMTyogesh_pandeyhttp://www.sqlservercentral.com/Forums/Topic999572-391-1.aspx[quote][b]yogesh_pandey (10/6/2010)[/b][hr]Thank you. Can I send you a DDL and may be you can help. Please let me know.[/quote]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....Wed, 06 Oct 2010 10:53:31 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic999572-391-1.aspxThank you. Can I send you a DDL and may be you can help. Please let me know.Wed, 06 Oct 2010 10:48:52 GMTyogesh_pandeyhttp://www.sqlservercentral.com/Forums/Topic999572-391-1.aspx[quote][b]yogesh_pandey (10/6/2010)[/b][hr]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.[/quote]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 [b]actual CREATE TABLE definitions [/b]and [b]some sample data [/b]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....Wed, 06 Oct 2010 10:46:15 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic999572-391-1.aspxThank 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.Wed, 06 Oct 2010 10:35:48 GMTyogesh_pandeyhttp://www.sqlservercentral.com/Forums/Topic999572-391-1.aspxwhat 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:[code]SELECT a.cur_bal + b.cur_balfrom aleft outer join bon a.ID = B.ID + 1[/code]here's your data formatted as consumable data for others to play with:[code]SELECT '112233' as acct_no,'SV' as acct_type,'9/4/2003' as effective_dt,'181447.83' as cur_bal UNION ALLSELECT '112233','SV','12/31/2003','110','100.16' UNION ALLSELECT '112233','SV','1/6/2004','101','850' UNION ALLSELECT '112233','SV','1/20/2004','101','1400' UNION ALLSELECT '112233','SV','1/31/2004','110','105.58' UNION ALLSELECT '112233','SV','2/2/2004','101','284' UNION ALLSELECT '112233','SV','2/10/2004','101','1000' UNION ALLSELECT '112233','SV','2/17/2004','101','600' UNION ALLSELECT '112233','SV','2/29/2004','110','104.7' UNION ALLSELECT '112233','SV','3/2/2004','101','400' UNION ALLSELECT '112233','SV','3/29/2004','101','1200' UNION ALLSELECT '112233','SV','3/31/2004','110','114.2' UNION ALLSELECT '112233','SV','4/12/2004','101','700'[/code]Wed, 06 Oct 2010 10:24:41 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic999572-391-1.aspxI 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 storedacct_no acct_type effective_dt cur_bal112233 SV 9/4/2003 181447.83 This is how the transaction is stored.acct_no acct_type effective_dt tran_code amt112233 SV 12/31/2003 110 100.16112233 SV 1/6/2004 101 850112233 SV 1/20/2004 101 1400112233 SV 1/31/2004 110 105.58112233 SV 2/2/2004 101 284112233 SV 2/10/2004 101 1000112233 SV 2/17/2004 101 600112233 SV 2/29/2004 110 104.7112233 SV 3/2/2004 101 400112233 SV 3/29/2004 101 1200112233 SV 3/31/2004 110 114.2112233 SV 4/12/2004 101 700I 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 Balance112233 SV 12/31/2003 110 100.16 176138.5112233 SV 1/6/2004 101 850 176038.3112233 SV 1/20/2004 101 1400 176888.3112233 SV 1/31/2004 110 105.58 178288.3112233 SV 2/2/2004 101 284 178182.7112233 SV 2/10/2004 101 1000 178466.7112233 SV 2/17/2004 101 600 179466.7112233 SV 2/29/2004 110 104.7 180066.7112233 SV 3/2/2004 101 400 179962112233 SV 3/29/2004 101 1200 180362112233 SV 3/31/2004 110 114.2 181562112233 SV 4/12/2004 101 700 181447.8Wed, 06 Oct 2010 10:16:17 GMTyogesh_pandey