hi frndz,
I tried some cte in this example and the result is totally wrong. plz see my example
declare @table table
(
entrydate datetime,
openingbal decimal(15,3),
credit decimal(15,3),
debit decimal(15,3),
closingbal decimal(15,3)
)
insert into @table
select '2014-10-02 23:05:43.203', 94000.000, 0.000, 900.000 ,93100.000
union
select '2014-10-03 23:05:43.203', 0.000 ,1000.000 ,0.000, 0.000
union
select '2014-10-03 23:06:43.203', 0.000, 0.000, 500.000 ,0.000
union
select '2014-10-14 23:52:27.327', 4000.000 ,0.000 ,1500.000 ,2500.000
;
with cte as(
select *, ROW_NUMBER()over (order by entrydate)rid from @table
)
, cte1 as(
select entrydate ,cast (openingbal as decimal(15,3)) openingbal,credit,debit, cast (closingbal as decimal(15,3))closingbal ,
ROW_NUMBER()over (order by entrydate)rid
from cte
union all
select b.entrydate ,cast (a.closingbal as decimal(15,3)) openingbal ,b.credit,b.debit,cast ((a.closingbal+b.credit) -b.debit as decimal(15,3)) closingbal
,ROW_NUMBER()over (order by a.entrydate) rid
from cte a
join cte1 b on b.rid=a.rid+1
)
select entrydate ,isnull(openingbal,0)openingbal,isnull(credit,0) CreditAmount
,isnull(debit,0) DebitAmount,isnull(closingbal,openingbal)closingbal ,rid from cte1 order by entrydate,rid
--------------------------------------------------------------------------
but i want to generate the opening bal based on prev row closingbal like this restult
entryate opnbal cr dr closingbal
02-10-14 23:05 94000 0 900 93100
03-10-14 23:05 93100 1000 0 94100
03-10-14 23:06 94100 0 500 93600
14-10-14 23:52 97600 0 1500 96100
plz help me.