表 存款存款额 存款人(不重复) 存款时间
52 a 2011-02-02
10 b 2011-03-03
536 xx 2011-03-05
44 xxx 2011-05-03
21 xxxx 2009-02-02
52 xxxxx 2010-05-02
0-60天 日利率是0
60-180天 日利率是0.01
大于180天 日利率是0.02怎么查询在4月1日--4月31日产生的利息啊。
52 a 2011-02-02
10 b 2011-03-03
536 xx 2011-03-05
44 xxx 2011-05-03
21 xxxx 2009-02-02
52 xxxxx 2010-05-02
0-60天 日利率是0
60-180天 日利率是0.01
大于180天 日利率是0.02怎么查询在4月1日--4月31日产生的利息啊。
--当月的利息,当已存天数存放的是到今天的天数时
select 存款额,已存天数,存款人,
利息=case when 已存天数-180>=day(getdate()) then 存款额*day(getdate())*0.02
when 已存天数-180<day(getdate()) and 已存天数-180>=0 then 存款额*(已存天数-180)*0.02+存款额*(day(getdate())+180-已存天数)*0.01
when 已存天数-60>=day(getdate()) then 存款额*day(getdate())*0.01
when 已存天数-60<day(getdate()) and 已存天数-60>=0 then 存款额*(已存天数-180)*0.01
end
from 存款
/*
create table 存款
(
存款额 int not null,
存款人 nvarchar(10) not null,
存款时间 date not null
);
insert into 存款(存款额,存款人,存款时间)
values
(52,'a','2011-02-02'),
(10,'b','2011-03-03'),
(536,'xx','2011-03-05'),
(44,'xxx','2011-05-03'),
(21,'xxxx','2009-02-02'),
(52,'xxxxx','2010-05-02');
*/
select t2.存款额,t2.存款人,t2.存款时间,(t2.利息2 - t2.利息1) as [4月1日--4月31日产生的利息]
from
(
select *,
case
when (DATEDIFF(DD,t1.存款时间,'2011-04-01') < 0)
then 0
when (DATEDIFF(DD,t1.存款时间,'2011-04-01') >= 0)
and (DATEDIFF(DD,t1.存款时间,'2011-04-01') < 60)
then t1.存款额
when (DATEDIFF(DD,t1.存款时间,'2011-04-01') >= 60)
and (DATEDIFF(DD,t1.存款时间,'2011-04-01') < 180)
then t1.存款额 * POWER(1.01,(DATEDIFF(DD,t1.存款时间,'2011-04-01')))
when (DATEDIFF(DD,t1.存款时间,'2011-04-01') >= 180)
then t1.存款额 * POWER(1.02,(DATEDIFF(DD,t1.存款时间,'2011-04-01')))
end as [利息1],
case
when (DATEDIFF(DD,t1.存款时间,'2011-04-30') < 0)
then 0
when (DATEDIFF(DD,t1.存款时间,'2011-04-30') >= 0)
and (DATEDIFF(DD,t1.存款时间,'2011-04-30') < 60)
then t1.存款额
when (DATEDIFF(DD,t1.存款时间,'2011-04-30') >= 60)
and (DATEDIFF(DD,t1.存款时间,'2011-04-30') < 180)
then t1.存款额 * POWER(1.01,(DATEDIFF(DD,t1.存款时间,'2011-04-30')))
when (DATEDIFF(DD,t1.存款时间,'2011-04-30') >= 180)
then t1.存款额 * POWER(1.02,(DATEDIFF(DD,t1.存款时间,'2011-04-30')))
end as [利息2]
from 存款 as t1
) as t2;
use tempdb;
/*
create table 存款
(
存款额 int not null,
存款人 nvarchar(10) not null,
存款时间 date not null
);
insert into 存款(存款额,存款人,存款时间)
values
(52,'a','2011-02-02'),
(10,'b','2011-03-03'),
(536,'xx','2011-03-05'),
(44,'xxx','2011-05-03'),
(21,'xxxx','2009-02-02'),
(52,'xxxxx','2010-05-02');
*/
select t2.存款额,t2.存款人,t2.存款时间,(t2.利息2 - t2.利息1) as [4月1日--4月30日产生的利息]
from
(
select *,
case
when (DATEDIFF(DD,t1.存款时间,'2011-04-01') < 0)
then 0
when (DATEDIFF(DD,t1.存款时间,'2011-04-01') >= 0)
and (DATEDIFF(DD,t1.存款时间,'2011-04-01') < 60)
then t1.存款额
when (DATEDIFF(DD,t1.存款时间,'2011-04-01') >= 60)
and (DATEDIFF(DD,t1.存款时间,'2011-04-01') < 180)
then t1.存款额 * POWER(1.01,(DATEDIFF(DD,t1.存款时间,'2011-04-01')))
when (DATEDIFF(DD,t1.存款时间,'2011-04-01') >= 180)
then t1.存款额 * POWER(1.02,(DATEDIFF(DD,t1.存款时间,'2011-04-01')))
end as [利息1],
case
when (DATEDIFF(DD,t1.存款时间,'2011-04-30') < 0)
then 0
when (DATEDIFF(DD,t1.存款时间,'2011-04-30') >= 0)
and (DATEDIFF(DD,t1.存款时间,'2011-04-30') < 60)
then t1.存款额
when (DATEDIFF(DD,t1.存款时间,'2011-04-30') >= 60)
and (DATEDIFF(DD,t1.存款时间,'2011-04-30') < 180)
then t1.存款额 * POWER(1.01,(DATEDIFF(DD,t1.存款时间,'2011-04-30')))
when (DATEDIFF(DD,t1.存款时间,'2011-04-30') >= 180)
then t1.存款额 * POWER(1.02,(DATEDIFF(DD,t1.存款时间,'2011-04-30')))
end as [利息2]
from 存款 as t1
) as t2;
select 52, 'a', '2011-02-02' union all
select 10 , 'b', '2011-03-03' union all
select 536, 'xx', '2011-03-05' union all
select 44 , 'xxx', '2011-05-03' union all
select 21 , 'xxxx', '2009-02-02' union all
select 52, 'xxxxx', '2010-05-02'select a.*,
case when DATEDIFF(d,a.存款时间,'2011-4-30')>180 then a.存款额*30*0.02
when DATEDIFF(d,a.存款时间,'2011-4-30')>60 then (a.存款额*(DATEDIFF(d,a.存款时间,'2011-4-30')-60)*0.01)
else 0 end 利息
from @存款 a存款额 存款人 存款时间 利息
----------- ------ ---------- ---------------------------------------
52 a 2011-02-02 14.04
10 b 2011-03-03 0.00
536 xx 2011-03-05 0.00
44 xxx 2011-05-03 0.00
21 xxxx 2009-02-02 12.60
52 xxxxx 2010-05-02 31.20(6 行受影响)写法里面不完善,但大体思路已经具备,不完善的地方在利率交界处与计息日期数的判断(比如>180的或许有部分在60-180之间,这个就没写了,而60-180的也有些小问题,不敲代码了。。)
这个不对吧
把4月份的日期生成一张临时表,里面有30条记录,就是4月份的30天的日期,然后利用这张临时表和现有的表CROSS JOIN下,计算每天的利率,然后相加。
create table t1
(
cunkuane int,
cunkuanren varchar(10),
cunkuanriqi datetime
)
insert into t1
select 52,'a','2011-02-02' union all
select 10,'b','2011-03-03' union all
select 536,'xx','2011-03-05' union all
select 44,'xxx','2011-05-03' union all
select 21,'xxxx','2009-02-02' union all
select 52,'xxxxx','2010-05-02'
create table t2
(
riqi datetime
)
declare @riqi datetime
set @riqi='2011-04-01'
while @riqi<'2011-05-01'
begin
insert into t2 (riqi) values (@riqi)
set @riqi=DATEADD(DAY,1,@riqi)
end
select * from t1
select * from t2;with aaa as
(select t1.*,t2.*,DATEDIFF(DAY,cunkuanriqi,riqi) as chayiriqi from t1 cross join t2)
,bbb as
(select *,case when chayiriqi>=0 and chayiriqi <=60 then 0
when chayiriqi>60 and chayiriqi<=180 then 0.01
when chayiriqi>180 then 0.02
else 0
end as lilv
from aaa)
,ccc as
(select *,cunkuane*lilv as lixi from bbb)
select cunkuanren as '存款人',SUM(lixi) as '4月份利息' from ccc group by cunkuanren以上代码为MSSQL2005及以上版本。
楼主看下是不是你的需求。
select 52, 'a', '2011-01-05' union all
select 52, 'a', '2011-02-05' union all
select 10 , 'b', '2011-03-03' union all
select 536, 'xx', '2011-03-05' union all
select 44 , 'xxx', '2011-05-03' union all
select 21 , 'xxxx', '2009-02-02' union all
select 21 , 'xxxx', '2010-10-04' union all
select 52, 'xxxxx', '2010-05-02'select a.*,DATEDIFF(d,a.存款时间,'2011-4-1') as 存款间隔,
case when DATEDIFF(d,a.存款时间,'2011-4-1')>180 then a.存款额*30*0.02
when DATEDIFF(d,a.存款时间,'2011-4-1')>150 then a.存款额*(DATEDIFF(d,a.存款时间,'2011-4-1')-150)*0.02+a.存款额*(180-DATEDIFF(d,a.存款时间,'2011-4-1'))*0.01
when DATEDIFF(d,a.存款时间,'2011-4-1')>60 then a.存款额*30*0.01
when DATEDIFF(d,a.存款时间,'2011-4-1')>30 then a.存款额*(DATEDIFF(d,a.存款时间,'2011-4-1')-30)*0.01
else 0 end 利息
from @存款 a
insert 存款
select 52 ,'a', '2011-02-02' union
select 10 ,'b', '2011-03-03' union
select 536 ,'xx', '2011-03-05' union
select 44 ,'xxx', '2011-05-03' union
select 21 ,'xxxx', '2009-02-02' union
select 52 ,'xxxxx', '2010-05-02'0-60天 日利率是0
60-180天 日利率是0.01
大于180天 日利率是0.02declare @date1 datetime,@date2 datetime
set @date1='2011-04-01';set @date2='2011-04-30'
select 存款人,sum(存款额*(case when 天数>=180 then 0.02 when 天数>=60 and 天数<180 then 0.01 else 0 end))
from
(select 存款.*,天数=datediff(day,存款时间,dateadd(day,number,@date1))
from 存款,master..spt_values a
where a.type='p' and dateadd(day,number,@date1)<=@date2) b
group by 存款人
(
select * ,diff=datediff(dd,cunkuanriqi,getdate())
from t1),
wang2 as(select cunkuane,cunkuanren,利息=30*cunkuane*case when diff between 0 and 60 then 0
when diff between 60 and 180 then 0.01
when diff >180 then 2.02 end
from wang1)
select * from wang2
when getdate()-存款时间 >= 60 and getdate()-存款时间 < 180 then 存款额*(day(getdate())*0.01
else 存款额*(day(getdate())*0.02
end from 存款
另,这是那就银行,这么高的利率?