Home Forums SQL Server 2008 SQL Server 2008 - General Looping through the recordset to assign different balance value to each transaction row RE: Looping through the recordset to assign different balance value to each transaction row

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!