Link to home
Create AccountLog in
Avatar of Mr_Ezi
Mr_Ezi

asked on

get the previous row value

i have a transaction table with credit, debit ,Balance.. i like to do when i make a update in the new record it should update the balance field from the previous record  balance + the current  (Debit-Credit) field
what is the best way to do it in sql
ASKER CERTIFIED SOLUTION
Avatar of tiagosalgado
tiagosalgado
Flag of Portugal image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Kevin Cross
Try something like this with SQL 2005+
-- common table expression against account ledger
;with cte as (
   select credit, debit, balance
        , row_number()
             -- partition by needed if ledger has multi-accounts
             -- each account would have different running balance
             over(partition by /* account id column */
             -- order by is needed either way to sequence DR/CR tx
                  order by /* tx id or time stamp column */
             ) as rn
   from Ledger -- replace with your table name
)
update tto 
set tto.balance = coalesce(tfr.balance, 0) + tto.debit - tto.credit
from cte tto
left join cte tfr on 
-- match account ids if multi-account ledger
tfr.accountid = tto.accountid and
-- get previous row
tfr.rn = tto.rn - 1
-- used to only update new rows
-- only works if balance is set to null to distinguish from $0 bal
where tto.balance is null
;

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Mr_Ezi
Mr_Ezi

ASKER

hi Mwvisa1 thanks for you're response
but it docent work... i think it because when it run it still don't have the right balance  from tfr table
What is the query you used?  There was some changes you had to make above, so in order to diagnose the "it docent work" comment I would need your actual query and some feedback on data with regard to things like columns involved and maybe sample data.
Avatar of Mr_Ezi

ASKER

here is the code which i write  
with cte as (
   select StartSaleTime,TransactionNo ,credit, debit, CurrBalance,CustomerID 
        , row_number()
             -- partition by needed if ledger has multi-accounts
             -- each account would have different running balance
             over(partition by Customerid
             -- order by is needed either way to sequence DR/CR tx
                  order by StartSaleTime
             ) as rn
   from [Transaction] -- replace with your table name]
)
update tto 
set tto.CurrBalance = coalesce(tfr.CurrBalance, 0) + (tto.debit - tto.credit)
--select tto.rn,  tto.CurrBalance,tto.Credit,tto.Debit,tto.CustomerID,tfr.rn,tfr.CurrBalance,tfr.TransactionNo  
from cte tto
left join cte tfr on 
-- match account ids if multi-account ledger
tfr.Customerid = tto.Customerid and
-- get previous row
tfr.rn = tto.rn - 1
-- used to only update new rows
-- only works if balance is set to null to distinguish from $0 bal
where tto.Customerid ='CA1714D1-7ED0-43F8-B38E-01113920516A'

Open in new window

Avatar of Mr_Ezi

ASKER

when do it as select not as update i get
it like  here
rn	CurrBalance	Credit	Debit	rn2	CurrBalance2
1	104	0	104	NULL	NULL
2	-104	104	0	1	104
3	67.4	0	67.4	2	-104
4	-67.4	67.4	0	3	67.4
5	47	0	47	4	-67.4
6	-47	47	0	5	47
7	271.8	0	271.8	6	-47
8	229.25	0	229.25	7	271.8
9	-451.05	451.05	0	8	229.25
10	1081.95	0	1081.95	9	-451.05
11	-50	50	0	10	1081.95
12	-50	50	0	11	-50
13	378	0	378	12	-50
14	408.2	0	408.2	13	378
15	-408.2	408.2	0	14	408.2
16	140.55	0	140.55	15	-408.2
17	-140.55	140.55	0	16	140.55
18	162	0	162	17	-140.55
19	-162	162	0	18	162
20	203.3	0	203.3	19	-162
21	-203.3	203.3	0	20	203.3

Open in new window

This might not be the most efficient, but this may be what you need:
with cte as (
   select StartSaleTime, TransactionNo, CustomerID
        , credit, debit, CurrBalance 
        , row_number()
             over(partition by Customerid 
                  order by StartSaleTime) as rn
   from [Transaction]
)
update tto 
set tto.CurrBalance = (
   select sum(tfr.debit - tfr.credit)
   from cte tfr 
   where tfr.Customerid = tto.Customerid and tfr.rn <= tto.rn
)
from cte tto
where tto.Customerid ='CA1714D1-7ED0-43F8-B38E-01113920516A'
;

Open in new window

you have rn column through which you can get the previous row, once you get it, you can do approaite action with its balance
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account