ALTER proc [dbo].[sp_bookview]
AS
DECLARE @Balance FLOAT
create table #tmp(
rownum int IDENTITY(1,1),
remark varchar(100),
AcctDate char(10),
VoucherStr varchar(20),
VoucherNum varchar(20),
period int,
debit float,
Credit float
)
--查询年初余额
select @Balance = FPreBalance from CN_AcctInfoView where id=3
--将日记账数据排好序放入临时表中
insert into #tmp( remark, AcctDate, VoucherStr,VoucherNum,period,debit,Credit)
SELECT tbl.Summary as remark,CONVERT(char(10), acctDate, 120)as AcctDate, VoucherStr,VoucherNum, period, debit, Credit
FROM CN_AcctBookView tbl WHERE AcctID = 3 order by AcctDate
SELECT * from (
select '前期余额' AS remark,'' AcctDate,'' VoucherStr,'' VoucherNum,'' period,null as debit, null as Credit, @Balance as balance
union all
select remark, AcctDate, VoucherStr,VoucherNum,period,debit,Credit,(select @Balance+ sum(debit)-sum(Credit) from #tmp where rownum<=a.rownum) as balance from #tmp a
union all
select '日合计' as remark,CONVERT(char(10), acctDate, 120) + '合计' as AcctDate,
'' VoucherStr,'' VoucherNum,'' period,sum(debit) debit, sum(Credit) as Credit,
(select @Balance+ sum(debit)-sum(Credit) from #tmp where acctDate<=a.acctDate) as balance
from #tmp a group by CONVERT(char(10), acctDate, 120)
union all
select '月合计' as remark,CONVERT(char(7), acctDate, 120) + '合计'as AcctDate,
'' VoucherStr,'' VoucherNum,'' period,sum(debit) debit, sum(Credit) as Credit,
(select @Balance+ sum(debit)-sum(Credit) from #tmp where CONVERT(char(7), acctDate, 120)<= CONVERT(char(7), a.acctDate, 120)) as balance
from #tmp a group by CONVERT(char(7), acctDate, 120)
union all
select '年合计' as remark,CONVERT(char(4), acctDate, 120) + '合计' as AcctDate,
'' VoucherStr,'' VoucherNum,'' period,sum(debit) debit, sum(Credit) as Credit,
(select @Balance+ sum(debit)-sum(Credit) from #tmp where CONVERT(char(4), acctDate, 120)<= CONVERT(char(4), a.acctDate, 120)) as balance
from #tmp a group by CONVERT(char(4), acctDate, 120)
) b order by AcctDate
--删除临时表
drop table #tmp