quote:

---

It would also be fairly simple to produce a query with a running sum total to make the results look like a bank statement.

---

create table #tran(tranid int identity(1,1), Date datetime,debit money,credit money,accountid int)

set dateformat 'dmy'
insert #tran (date, debit, credit, accountid)
select date, debit, credit, accountid
from	(
	select '12/12/2008' date ,0 debit ,100 credit, 1 accountid union all
	select '13/12/2008',0,200,1 union all
	select '14/12/2008',50,0,1
	) a
order by date


select	date
	,debit
	,credit
	,balance = 
		(select sum(credit - debit) 
		from #tran 
		where accountid = a.accountid and tranid <= a.tranid)
from	#tran a
where	a.accountid = 1
order by tranid


--EDIT:
print 'alternate technique'
select	a.date
	,a.debit
	,a.credit
	,sum(b.credit - b.debit) balance
from	#tran a
join #tran b
	on a.accountid = b.accountid
	and a.tranid >= b.tranid
group by a.accountid
	,a.tranid
	,a.date
	,a.debit
	,a.credit
order by a.accountid, a.tranid
--/EDIT

drop table #tran

output:
date                                    debit                 credit                balance               
------------------------------------------------------------- --------------------- --------------------- 
2008-12-12 00:00:00.000                 .0000                 100.0000              100.0000
2008-12-13 00:00:00.000                 .0000                 200.0000              300.0000
2008-12-14 00:00:00.000                 50.0000               .0000                 250.0000

create table #tran(tranid int identity(1,1), Date datetime,debit money,credit money,accountid int)

--set up sample data
set dateformat 'dmy'
insert #tran (date, debit, credit, accountid)
select date, debit, credit, accountid
from	(
	select '12/12/2008' date ,0 debit ,100 credit, 1 accountid union all
	select '13/12/2008',0,200,1 union all
	select '14/12/2008',50,0,1 union all
	select '15/12/2008',0,200,1 union all
	select '16/12/2008',50,0,1 union all
	select '17/12/2008',0,200,1 
	) a
order by date


set dateformat 'ymd'

--input parameters
declare @fromDate datetime
	,@toDate datetime
	,@acctid int
select	@fromDate = '2008-12-14'
	,@toDate = '2008-12-15'
	,@acctid = 1

--local variables
declare @begBal money, @begTranID int

--get the beginning tranID and Balance for earliest date up to (but not including) @fromDate
select @begBal = sum(credit - debit)
	,@begTranID = max(tranid)
from	#tran
where	accountid = @acctid
and	date < @fromDate


--Get details from @fromDate through @ToDate
select	date
	,debit
	,credit
	,balance = @begBal +
		(select sum(credit - debit) 
		from #tran 
		where tranid > @begTranid 
		and accountid = a.accountid 
		and tranid <= a.tranid)
from	#tran a
where	a.accountid = @acctID
and	tranid > @begTranid
and	date <= @toDate

--EDIT:
print 'alternate technique - maybe faster?'
select	a.date
	,a.debit
	,a.credit
	,@begBal + sum(b.credit - b.debit) balance
from	#tran a
join #tran b
	on a.accountid = b.accountid
	and a.tranid >= b.tranid
	and b.tranid > @begTranid
where	a.tranid > @begTranid
and	a.date <= @toDate
group by a.accountid
	,a.tranid
	,a.date
	,a.debit
	,a.credit
order by a.accountid, a.tranid
--/EDIT

drop table #tran

output:
date                               debit                 credit                balance               
-------------------------------------------------------- --------------------- --------------------- 
2008-12-14 00:00:00.000            50.0000               .0000                 250.0000
2008-12-15 00:00:00.000            .0000                 200.0000              450.0000

quote:

---

Originally posted by Jeff Moden

Lemme know when you need something that will calculate a couple of million rows in a few seconds.

---