if object_id('bank') is not null drop table bank
if object_id('balance') is not null drop table balance 
if object_id('billData') is not null drop table billData
create table bank(
bankAccount nvarchar(10) not null primary key,
bankName nvarchar(50) null
)
create table balance(
iYear int not null,
iMonth int not null,
pre_acc float not null,
deaIn_acc float not null,
deaOu_acc float not null,
in_acc float not null,
ou_acc float not null,
Las_acc as(pre_acc+deaIn_acc-deaOu_acc+in_acc-ou_acc)
)
alter table balance add constraint [PK_balance] primary key clustered(iYear,iMonth) on [PRIMARY]
go
create table billData(
id int identity(1,1) not null primary key,
billDate datetime not null,
billType nvarchar(1) not null default('收'),
clientName nvarchar(20) not null,
bankAccount nvarchar(10) not null,
otherBankAccount nvarchar(10) null,
moneyLow float not null,
bewrite bit
)
go
insert into bank 
    select '001','AAA' 
    union all select '002','BBB'
    union all select '003','CCC'
go
insert into balance 
    select 2012,4,1000000,10000,20000,5000,4000
go
insert into billData
    select '2012-05-23','收','abc','001','001',100,0
union all select '2012-05-23','付','bcd','002','001',100,0
union all select '2012-05-24','收','ccc','002','001',200,0
union all select '2012-05-25','收','abc','002','001',100,0
go
if object_id('Balance_YM','P') is not null drop proc Balance_YM
go
create proc Balance_YM
@year int=2012,
@month int=5,
@day int=24
as
select iYear,iMonth,pre_acc,deaIn_acc=0,deaOu_acc=0,
        in_acc=0,ou_acc=0,Las_acc=0
    into #t 
    from balance 
    where (@month<>1 and iYear=@year and iMonth=@month-1)
        or (@month=1 and iYear=@year-1 and iMonth=12)
if @@rowcount>0
begin
    update #t 
        set pre_acc=#t.pre_acc+t.in_acc-t.ou_acc,
            in_acc=t.in_acc,
            ou_acc=t.ou_acc
        from (
                select in_acc=isnull(sum(case when billType='收' then moneyLow else 0 end),0),
                    ou_acc=isnull(sum(case when billType='付' then moneyLow else 0 end),0)
                from billData
                where datepart(year,billDate)=@year and datepart(month,billDate)=@month
                    and datepart(day,billDate)<@day
            )t
    select * from #t
end
drop table #t
go
exec Balance_YM 2012,5,24
exec Balance_YM 2012,5,25
go

------解决方案--------------------

探讨

bank表
bankAccount bankName
001          AAA
002          BBB
003          CCC

balance 表 (balance为月度表，pre_acc为期初余额，deaIn_acc为调入金额，deaOu_acc为调出金额，in_acc为本期收入，ou_acc为本期支出，las_acc为期末余额las_acc=pre+d……