高手请进,关于银行计息存储过程的问题???

zhangboshiyan 2006-07-31 04:37:43
利率表
id InterestDate(利率变动日期) Interest(利率)
1 2000-1-1 0.012
2 2000-9-5 0.011
3 2001-5-5 0.015
4 2002-8-3 0.023
5 2003-5-3 0.017
6 2004-9-10 0.020
7 2005-6-3 0.012
8 2006-6-3 0.015

业务存取表
id personAccount(个人账户) cnMoney(存取金额) cnDate(存取日期)
1 2035469884566 100000 2002-6-1
2 1265469545456 200000 2003-5-5
3 2035469884566 -1000 2004-4-4
4 2035469884566 -2000 2004-12-20
5 2035469884566 50000 2005-9-9
6 1265469545456 -2000 2004-5-5

说明:个人账户是唯一的,存取金额中负数代表取钱,利率表中的利率代表年利率。

采用积数计息法
计息公式为: 日利率 = 年利率 /360
利息=本金×实际天数×日利率。

需求:求某个人账户的余额和利息。
存储过程的传入参数:1.个人账户号;2.查询日期
存储过程的输出参数:1.个人账户余额;2.利息
...全文
288 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
turenjie 2006-08-02
  • 打赏
  • 举报
回复
只能帮你顶了,估计是业务太烦了!
十一月猪 2006-08-02
  • 打赏
  • 举报
回复
注释 怎么都变成? 了
奇怪
十一月猪 2006-08-02
  • 打赏
  • 举报
回复
Create Proc Sp_PerMoney_0802 @personAccount varchar(20) , @date datetime , --????
@Personblance money output, @interest_ret money output
as
Begin
declare @cnMoney money , @cnDate datetime , @cnMoney_Nxt Money , @cndate_nxt Datetime
declare @InterestDate datetime , @Interest numeric(10,6) ,@InterestDate_Nxt datetime , @Interest_nxt numeric(10,6)
declare @lx Money
declare @flag1 int


set @flag1 = 0
set @lx = 0 --?????


Declare cursor1 scroll cursor for
select cnMoney , cnDate from permoney
where personAccount = @personAccount and cnDate <= @Date order by cnDate asc

Open cursor1

--???????,??????????

Fetch Next from cursor1 into @cnMoney , @cnDate
Fetch Next from cursor1 into @cnMoney_nxt,@cnDate_nxt

While @@fetch_status = 0
begin

--???????,?????????
if @flag1 = 1 Goto Lab1

Declare cursor2 scroll cursor for select Interest ,InterestDate
from Interestt order by InterestDate asc

Open cursor2
Fetch Next from cursor2 into @Interest , @Interestdate
Fetch Next from cursor2 into @Interest_nxt , @InterestDate_nxt

while @@fetch_status = 0
begin
if @cnDate >= @InterestDate and @cnDate <@InterestDate_nxt and @cnDate_nxt <@InterestDate_nxt
begin
select @lx =@lx + @cnMoney * @Interest/360 * datediff(day,@cnDate,@cnDate_nxt )
end
if @cnDate >= @InterestDate and @cnDate <@InterestDate_nxt and @cnDate_nxt > @InterestDate_nxt
begin
select @lx =@lx + @cnMoney * @Interest/360 * datediff(day,@cnDate,@InterestDate_nxt )
select @cnDate = @Interest_Nxt -- ??????????????
end
--@cndate_nxt < @InterestDate ?????
Select @InterestDate = @InterestDate_Nxt , @Interest = @Interest_Nxt

Fetch Next from cursor2 into @Interest_nxt , @InterestDate_nxt
end

--??2??
Close cursor2
DealLocate cursor2

Lab1: if @cndate > @InterestDate_nxt -- ????????????,???????????
begin
set @flag1 = 1 --???? ,?????2
select @lx = @lx + @cnMoney*@Interest/360 * datediff(day,@cndate,@cndate_nxt)
end


Select @cnMoney = @cnMoney + @cnMoney_nxt , @cnDate = @cnDate_nxt --??????????,??????
Fetch Next from cursor1 into @cnMoney_nxt,@cnDate_nxt
End

Close cursor1
DealLocate cursor1

--?????????????
if @flag1 = 1
begin
set @lx = @lx + @cnMoney *@Interest/360 * datediff(day , @cnDate ,@date) --?????????
end
else
Begin
Declare cursor3 scroll cursor for select Interest,InterestDate
from Interestt order by InterestDate asc

Open cursor3
Fetch Next from cursor3 into @Interest , @InterestDate
Fetch Next from cursor3 into @Interest_nxt , @InterestDate_nxt

while @@fetch_status = 0
begin
if @cnDate >= @InterestDate and @cnDate <@InterestDate_nxt and @InterestDate_nxt < @date
begin
set @lx = @lx + @cnMoney * @Interest / 360 * datediff(day,@cnDate,@InterestDate_nxt)
select @cnDate = @InterestDate_Nxt
end
if @cnDate >= @InterestDate and @cnDate <@InterestDate_nxt and @InterestDate_nxt > @date
begin
set @lx = @lx + @cnMoney * @Interest / 360 * datediff(day,@cnDate,@Date)
end

Select @InterestDate = @InterestDate_Nxt , @Interest = @Interest_Nxt
Fetch Next from cursor3 into @Interest_nxt , @InterestDate_nxt
end

close cursor3
deallocate cursor3

if @date > @InterestDate
begin
set @lx = @lx + @cnMoney * @Interest / 360 * datediff(day,@cnDate,@Date)
end
end
set @interest_ret = @lx
set @Personblance = @cnMoney + @interest_ret
end
hellowork 2006-08-02
  • 打赏
  • 举报
回复
----测试,请楼主多试试几个结算日期
declare @AccountNo varchar(20) /*帐号*/
declare @EndDate datetime /*结算日期*/
declare @Balance money /*帐户余额*/
declare @Interest money /*利息*/
set @AccountNo = '2035469884566'
set @EndDate = getdate() --'2005-01-02'
----计算余额和利息
EXEC spAccount @AccountNo,@EndDate,@Balance output,@Interest output
----查看余额和利息
select @Balance,@Interest
hellowork 2006-08-02
  • 打赏
  • 举报
回复
if object_id('Interest_Test') is not null
drop table Interest_Test
if object_id('Account_Test') is not null
drop table Account_Test
GO
----创建利率表
create table Interest_Test(id int,InterestDate datetime,Interest decimal(5,3))
----创建业务存取表
create table Account_Test(id int,PersonAccount varchar(20),cnMoney money,cnDate datetime)
GO
insert Interest_Test
select 1,'2000-1-1',0.012 union all
select 2,'2000-9-5',0.011 union all
select 3,'2001-5-5',0.015 union all
select 4,'2002-8-3',0.023 union all
select 5,'2003-5-3',0.017 union all
select 6,'2004-9-10',0.020 union all
select 7,'2005-6-3',0.012 union all
select 8,'2006-6-3',0.015
insert Account_Test
select 1,'2035469884566',100000,'2002-6-1' union all
select 2,'1265469545456',200000,'2003-5-5' union all
select 3,'2035469884566',-1000,'2004-4-4' union all
select 4,'2035469884566',-2000,'2004-12-20' union all
select 5,'2035469884566',50000,'2005-9-9' union all
select 6,'1265469545456',-2000,'2004-5-5'
GO
----创建计算余额和利息的存储过程
if object_id('spAccount') is not null
drop proc spAccount
GO
CREATE PROC spAccount
@AccountNo varchar(20), /*帐号*/
@EndDate datetime, /*结算日期*/
@Balance money output, /*帐户余额*/
@Interest money output /*利息*/
AS
if object_id('tempdb..#t') is not null
drop table #t
----生成结算用的临时表
SELECT x.cnDate,i.InterestDate,
isnull(x.NextcnDate,@EndDate) as NextcnDate, /*如果结算日期大于最后一次存取款日期,则把结算日期作为下一次存储款日期*/
x.cnMoney,x.Balance,i.Interest INTO #t FROM
(
select PersonAccount,cnMoney,cnDate,
NextcnDate = case when /*如果结算日期小于下一次存取款日期,则将结算日期作为下一次存储款日期*/
(select top 1 cnDate from Account_Test where PersonAccount = a.PersonAccount and cnDate > a.cnDate order by id asc ) > @EndDate
then @EndDate
else (select top 1 cnDate from Account_Test where PersonAccount = a.PersonAccount and cnDate > a.cnDate order by id asc )
end ,
Balance = /*每次存取款时的帐户余额,该余额作为计算存取款之后利息的本金*/
(select sum(cnMoney) from Account_Test where PersonAccount = a.PersonAccount and cnDate <= a.cnDate)
from Account_Test a
where a.PersonAccount = @AccountNo and cnDate <= @EndDate /*指定帐号和结算日期*/
) x
LEFT JOIN Interest_Test i on i.InterestDate between x.cnDate and x.NextcnDate
--select * from #t /*可以查看一下结算所使用的表结构和数据,以下算法都是依据该表的.*/

----计算帐户余额和利息
SELECT @Balance = max(Balance),@Interest = sum(Balance*days1*Interest1/360)+sum(Balance*days2*Interest2/360)
FROM /*可以将from后面的子查询复制后执行一下,查看一下表结构和数据*/
(
select *,
days1 = /*两次存取款之间从本次存取款开始,与每次利率调整日期之间各时间段的天数*/
--两次存取款之间没有发生利率调整
case when InterestDate is null
then datediff(dd,cnDate,NextcnDate)
--两次存取款之间只有一个利率调整日期
when not exists(select 1 from #t where cnDate = a.cnDate and InterestDate< a.InterestDate)
and not exists(select 1 from #t where cnDate = a.cnDate and InterestDate > a.InterestDate)
then datediff(dd,cnDate,InterestDate)
--两次存取款之间多次调整利率的第一个利率调整日期
when not exists(select 1 from #t where cnDate = a.cnDate and InterestDate< a.InterestDate)
then datediff(dd,cnDate,InterestDate)
--两次存取款之间多次调整利率第一个和最后一个以外的利率调整日期
when exists(select 1 from #t where cnDate = a.cnDate and InterestDate< a.InterestDate)
and exists(select 1 from #t where cnDate = a.cnDate and InterestDate> a.InterestDate)
then datediff(dd,(select top 1 InterestDate from #t where cnDate = a.cnDate and InterestDate < a.InterestDate order by InterestDate desc),InterestDate)
--两次存取款之间多次调整利率的最后一个利率调整日期
when not exists(select 1 from #t where cnDate = a.cnDate and InterestDate> a.InterestDate)
then datediff(dd,(select top 1 InterestDate from #t where cnDate = a.cnDate and InterestDate < a.InterestDate order by InterestDate desc),InterestDate)
else 0 end,
days2 = /*两次存取款之间最后一次利率调整日期到下次存取款之间的天数*/
--两次存取款之间只有一个利率调整日期
case when not exists(select 1 from #t where cnDate = a.cnDate and InterestDate< a.InterestDate)
and not exists(select 1 from #t where cnDate = a.cnDate and InterestDate > a.InterestDate)
then datediff(dd,InterestDate,NextcnDate)
--两次存取款之间最后一个利率调整日期
when not exists(select 1 from #t where cnDate = a.cnDate and InterestDate> a.InterestDate)
then datediff(dd,InterestDate,NextcnDate)
else 0 end,
Interest1 = /*与days1时间段对应的年利率*/
--两次存取款之间没有发生利率调整
case when InterestDate is null
then (select top 1 Interest from Interest_Test where InterestDate < a.cnDate order by InterestDate desc)
--两次存取款之间发生了利率调整
else (select top 1 Interest from Interest_Test where InterestDate < a.InterestDate order by InterestDate desc)
end,
Interest2 = /*与days2时间段对应的年利率*/
case --两次存取款之间只有一个利率调整日期或多次利率调整的最后一个利率调整日期
when not exists(select 1 from #t where cnDate = a.cnDate and InterestDate> a.InterestDate)
then Interest
else 0 end
from #t a
) b
----清除结算临时表
drop table #t
GO
hellowork 2006-08-02
  • 打赏
  • 举报
回复
试试,先说说思路:
计算利息的核心问题是两次连续的存取款日期之间发生利率变动时怎么处理利率.例如楼主所举的例子中:
2002-06-01至2004-04-04之间发生了二次利率变动,一次是在2002-08-03,另一次是在2003-05-03.
这就导致了利息必须这样计算:
2002-06-01 至 2002-08-03之间的利率为2001-05-05的利率,为0.015,共63天
2002-08-03 至 2003-05-03之间的利率为2002-08-03的利率,为0.023,共273天
2003-05-03 至 2004-04-04之间的利率为2003-05-03的利率,为0.017,共337天
根据这个思路,将楼主例子中的帐户存取表和利率表结合生成了一个计算利息的临时表,该临时表结构和数据如下:
帐号为'2035469884566',结算截止日期为2006-08-02.
cnDate:存取款日期,InterestDate:利率变动日期,NextcnDate:下次存取款日期
Balance:帐号余额
days1:本次存取款日期和利率变动日期之间的天数,如上说指的时间段
Interest1:days1时间段对应的年利率
days2:下次存取款日期前和利率变动日期之间的天数,如上说指的时间段
Interest2:days2时间段对应的年利率
------------------------------------------------------------------------------------
cnDate InterestDate NextcnDate Balance days1 Interest1 days2 Interest2
2002-06-01 2002-08-03 2004-04-04 100000 63 0.015 0 0
2002-06-01 2003-05-03 2004-04-04 100000 273 0.023 337 0.017
2004-04-04 2004-09-10 2004-12-20 99000 159 0.017 101 0.020
2004-12-20 2005-06-03 2005-09-09 97000 165 0.020 98 0.012
2005-09-09 NULL 2006-08-02 147000 327 0.012 NULL NULL
------------------------------------------------------------------------------------
这个表生成后,结存余额就等于max(Balance),
利息=Balance*days1*Interest1/360 + Balance*days2*Interest2/360

下面是说明上面表格中每行的时间段(days1,days2)是怎样计算得到的:
select
datediff(dd,'2002-06-01','2002-08-03'), /*第一行days1=63*/
datediff(dd,'2002-08-03','2003-05-03'), /*第二行days1=273*/
datediff(dd,'2003-05-03','2004-04-04'), /*第二行days2=337*/
datediff(dd,'2004-04-04','2004-09-10'), /*第三行days1=159*/
datediff(dd,'2004-09-10','2004-12-20'), /*第三行days2=101*/
datediff(dd,'2004-12-20','2005-06-03'), /*第四行days1=165*/
datediff(dd,'2005-06-03','2005-09-09'), /*第四行days2=98*/
datediff(dd,'2005-09-09','2006-08-02') /*第五行days1=327*/
十一月猪 2006-08-02
  • 打赏
  • 举报
回复
只处理了查询出来的前2条记录
=======
什么意思?
turenjie 2006-08-01
  • 打赏
  • 举报
回复
十一月猪,强呀!
你能把你计算利息的大致思路说一下吗,觉得有点麻烦了!
zhangboshiyan 2006-08-01
  • 打赏
  • 举报
回复
zjdyzwx(十一月猪) 我看了哈你写的程序,有点问题,你都是只处理了查询出来的前2条记录,但是有可能不只2条记录啊。zjdyzwx(十一月猪) 在帮我看看怎么解决这个问题啊,还有其他的高手呢,都跑那儿去了啊,小弟恳请帮我解答哈呀!
zhangboshiyan 2006-08-01
  • 打赏
  • 举报
回复
zjdyzwx(十一月猪) 可以解释一下 程序吗?
十一月猪 2006-07-31
  • 打赏
  • 举报
回复
我想主要的利息的计算
写得太麻烦了
不过可能可以达到lz的要求了

十一月猪 2006-07-31
  • 打赏
  • 举报
回复
Alter Proc Sp_PerMoney 改成 create Proc Sp_PerMoney
十一月猪 2006-07-31
  • 打赏
  • 举报
回复
selectcreate table Interestt
(
id int ,
InterestDate datetime,
Interest numeric(10,3)
)

create table PerMoney
(
id int ,
personAccount varchar(8000),
cnMoney money,
cnDate datetime
)

Insert into Interestt
select 1, '2000-1-1', 0.012 union
select 2, '2000-9-5', 0.011 union
select 3, '2001-5-5', 0.015 union
select 4, '2002-8-3', 0.023 union
select 5, '2003-5-3', 0.017 union
select 6, '2004-9-10', 0.020 union select 200000 * 1*0.017/360
select 7, '2005-6-3', 0.012 union
select 8, '2006-6-3', 0.015

Insert into PerMoney
select 1, '2035469884566', 100000, '2002-6-1' union
select 2, '1265469545456', 200000, '2003-5-5' union
select 3, '2035469884566', -1000, '2004-4-4' union
select 4, '2035469884566', -2000, '2004-12-20' union
select 5, '2035469884566', 50000, '2005-9-9' union
select 6, '1265469545456', -2000, '2004-5-5'


Alter Proc Sp_PerMoney @personAccount varchar(20) , @date datetime , --查询日期
@Personblance money output, @interest_ret money output
as
begin
-- declare @lv numeric(10,6)
declare @lx Money --利息
Set @lx = 0

declare @cnMoney money , @cnDate datetime
declare @cnMoney_Nxt money , @cnDate_Nxt datetime
declare @InterestDate datetime , @Interest numeric(10,6)
declare @InterestDate_before datetime , @Interest_before numeric(10,6)

declare cursor1 scroll cursor for select
cnMoney , cnDate from PerMoney
where cnDate <= @date --cnDate 不一定等于 @date
and personAccount = @personAccount
Open cursor1
Fetch Next from cursor1 into @cnMoney , @cnDate --begin
Fetch Next from cursor1 into @cnMoney_Nxt , @cnDate_Nxt

While @@fetch_status = 0
Begin
declare cursor2 scroll cursor for select
InterestDate,Interest from Interestt
-- where InterestDate Between @cnDate and @cnDate_Nxt
Order by InterestDate asc
Open cursor2

Fetch Next from cursor2 Into @InterestDate_before , @Interest_before
Fetch Next from cursor2 Into @InterestDate , @Interest
/*
While @@fetch_Status = 0
begin
Set @lx = @lx + @cnMoney * @Interest/360 * datediff(day,@cnDate,@InterestDate)
Set @cnDate = @InterestDate
Fetch Next from cursor2 Into @InterestDate , @Interest
end
Close cursor2
Deallocate cursor2

Set @cnDate = @cnDate_Nxt
Set @cnMoney = @cnMoney + @cnMoney_Nxt

Fetch Next from cursor1 into @cnMoney_Nxt , @cnDate_Nxt
End
*/ While @@Fetch_Status = 0
Begin
if @cnDate > @InterestDate_before and @cnDate < @InterestDate --1.第一个开始计算利息
begin
if @InterestDate >= @cnDate_Nxt
begin
-- Set @lx = @lx + @cnMoney * @Interest_before/360 * datediff(day,@cnDate,@cnDate_Nxt)
GoTO Lab2
end
else
begin
Set @lx = @lx + @cnMoney * @Interest_before/360 * datediff(day,@cnDate,@InterestDate)
end
-- Set @cnDate = @InterestDate
-- Fetch Next from cursor3 Into @InterestDate , @Interest
end
if @InterestDate_before > @cnDate
begin
if @InterestDate >= @cnDate_Nxt
begin
-- Set @lx = @lx + @cnMoney * @Interest_before/360 * datediff(day,@cnDate,@cnDate_Nxt)
GoTO Lab2
end
else
begin
Set @lx = @lx + @cnMoney * @Interest_before/360 * datediff(day,@Interest_before,@InterestDate)
end
end


-- Else if @cnDate > @InterestDate
-- Begin

-- End
Lab2:
Close cursor2
Deallocate cursor2

Set @cnDate = @cnDate_Nxt
set @cnMoney = @cnMoney + @cnMoney_Nxt

Select @InterestDate_before = @InterestDate , @Interest_before = @Interest
Fetch Next from cursor1 Into @InterestDate , @Interest
End
End
Close cursor1
Deallocate Cursor1
--计算最后一次存款到查询时间的利息
declare cursor3 scroll cursor for select
InterestDate,Interest from Interestt
--where InterestDate <= @date--利息
Order by InterestDate asc
Open cursor3
Fetch Next from cursor3 Into @InterestDate_before , @Interest_before
Fetch Next from cursor3 Into @InterestDate , @Interest
While @@Fetch_Status = 0
Begin
if @cnDate > @InterestDate_before and @cnDate < @InterestDate --1.第一个开始计算利息
begin
if @InterestDate >= @date
begin
-- Set @lx = @lx + @cnMoney * @Interest_before/360 * datediff(day,@cnDate,@date)
GoTO Lab1
end
else
begin
Set @lx = @lx + @cnMoney * @Interest_before/360 * datediff(day,@cnDate,@InterestDate)
end
-- Set @cnDate = @InterestDate
-- Fetch Next from cursor3 Into @InterestDate , @Interest
end

Else if @cnDate < @InterestDate_before
Begin
if @date > @InterestDate
begin
Set @lx = @lx + @cnMoney * @Interest_before/360 * datediff(day,@InterestDate_before,@InterestDate)
end
else
begin
Goto Lab1
end
End

Select @InterestDate_before = @InterestDate , @Interest_before = @Interest
Fetch Next from cursor3 Into @InterestDate , @Interest
End

Lab1:
Set @lx = @lx + @cnMoney * @Interest_before/360 * datediff(day,@cnDate,@date)

Close cursor3
Deallocate cursor3

Set @interest_ret = @lx --利息
Set @Personblance = @cnMoney + @lx --余额 = 余额+利息

End




declare @Personblance money , @interest_ret money
exec Sp_PerMoney '1265469545456' , '2003-5-6',@Personblance output, @interest_ret output
select @Personblance, @interest_ret

turenjie 2006-07-31
  • 打赏
  • 举报
回复
如果最早的日期在2000-1-1之前,那这段的利率怎么算?
turenjie 2006-07-31
  • 打赏
  • 举报
回复
create procedure pTest
(
@id varchar(50),
@inputdate datetime,
@leftmoney float output,
@RateMoney float ouput
)
as
select @leftmoney = sum(cnMoney) from 业务存取表 where cnDate<= @inputdate and id = @id group by id

-- 这是个人的余额
-- 利息的有点复杂,明天给你紧


27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧