这是我的一个存储过程,你参考一下吧!
------客户动态分析
ALTER PROCEDURE dbo.[f_sp_CustomDF]
(
@AccNo varchar(13),--客户帐号
@type varchar(6),--分析周期,month,day,xun(旬)
@value varchar(6) --type为day时,前四位为年份,后两位为月份;为 month,xun时,是四位年份
)AS
begin
declare @intPeriod smallint --周期数
declare @intTemp smallint
declare @transdate datetime
declare @Balance decimal(12,2)
declare @i smallint
declare @str varchar(10)
declare @str1 varchar (10)
declare @Cretemp decimal(12,2)
declare @Debtemp decimal(12,2)
delete from tblnewperiod
if @type='month'
begin
select @intPeriod=12
select @i=1
while @i<=@intperiod
begin
insert into tblnewperiod(id,period) values(@i,cast(@i as varchar(2))+'月')
select @i=@i+1
end
delete from TblcustomDF
insert into TblcustomDF
Select (cast( month(t1.transdate) as varchar(2)) +'月' ) as Period,T1.TransDate,T1.balance from
tblcusdetail t1 where year(t1.transdate)=@value and T1.accno=@Accno order by T1.transDate
select @i=1
Delete from TblBalance
select top 1 @balance=balance from tblcusdetail where accno=@Accno
order by transdate desc
select @Cretemp=Sum(CreditAmt),@Debtemp=sum(DebitAmt) from tblcusdetail where year(transdate)>=@value and accno=@Accno
select @balance=@balance-(@Cretemp-@Debtemp)
while @i<=@intPeriod
begin
select @str=cast(@i as varchar(2)) +'月'
Declare cursor1 cursor for
Select Period,Transdate,Balance from TblcustomDF where Period=@str order by TransDate desc
open cursor1
fetch next from cursor1 into @str1,@TransDate,@Balance
close cursor1
deallocate cursor1
insert into TblBalance Values(@str,@balance)
set @i=@i+1
end
Select P.id 顺序号, p.Period as 时期,
M.交易笔数,M.借方交易金额,M.贷方交易金额,T.baLance 余额 from
tblnewperiod p
left join ( Select (cast(month(transdate) as varchar(2))+'月') as Period,
Count(Accno)as 交易笔数,sum(DebitAmt)as 借方交易金额,sum(CreditAmt) as 贷方交易金额
from dbo.tblCusDetail where Accno=@Accno and
year(transdate)=@value
Group by (cast(month(transdate) as varchar(2))+'月')
) M
on p.period=M.Period left join Tblbalance T on p.period=T.Period order by p.id
end