一个表kh_acno
有如下几个字段,帐号,起始日期,截止日期,金额
acno,stdt,eddt,acbl
数据如下:
A 20070101 20071129 1000
A 20071130 20071231 500
B 20070101 20071231 600另一个表:kh_inrt
有如下几个字段,帐号,起始日期,截止日期,利率
acno,stdt,eddt,inrt
A 20070101 20070929 1.2
A 20070930 20071231 1.4
B 20070101 20071231 1.6如何实现每天的余额乘以每天的利率,再把这每天的余额乘以每天的利率累加起来如何计算从2007.2.1开始,到2007.12.25结束?A:1000*1.2*241+1000*1.4*62+500*1.4*26
B:600*1.6*329如何用SQL实现?谢谢如果再关联一个表kh_percent,也有一个起始日期,截止日期,分成,员工
acno,stdt,eddt,percent,name
A 20070101 20070920 0.3 王一
A 20070101 20070920 0.7 王二
A 20070921 20071231 0.3 孙一
A 20070921 20071231 0.7 王二
B 20070101 20071231 1 王二现在想计算每个员工计算从2007.2.1开始,到2007.12.25结束的业务情况,还得乘以与之相关每天的分成,请问如何实现?
谢谢!
有如下几个字段,帐号,起始日期,截止日期,金额
acno,stdt,eddt,acbl
数据如下:
A 20070101 20071129 1000
A 20071130 20071231 500
B 20070101 20071231 600另一个表:kh_inrt
有如下几个字段,帐号,起始日期,截止日期,利率
acno,stdt,eddt,inrt
A 20070101 20070929 1.2
A 20070930 20071231 1.4
B 20070101 20071231 1.6如何实现每天的余额乘以每天的利率,再把这每天的余额乘以每天的利率累加起来如何计算从2007.2.1开始,到2007.12.25结束?A:1000*1.2*241+1000*1.4*62+500*1.4*26
B:600*1.6*329如何用SQL实现?谢谢如果再关联一个表kh_percent,也有一个起始日期,截止日期,分成,员工
acno,stdt,eddt,percent,name
A 20070101 20070920 0.3 王一
A 20070101 20070920 0.7 王二
A 20070921 20071231 0.3 孙一
A 20070921 20071231 0.7 王二
B 20070101 20071231 1 王二现在想计算每个员工计算从2007.2.1开始,到2007.12.25结束的业务情况,还得乘以与之相关每天的分成,请问如何实现?
谢谢!
--產生#T,每天的金額和利率都有了,接下來想怎么算都可以
create table kh_acno(acno varchar(10), stdt datetime,eddt datetime,acbl int)
insert into kh_acno select 'A','20070101','20071129',1000
insert into kh_acno select 'A','20071130','20071231',500
insert into kh_acno select 'B','20070101','20071231',600create table kh_inrt(acno varchar(10),stdt datetime,eddt datetime,inrt numeric(18,2))
insert into kh_inrt select 'A','20070101','20070929',1.2
insert into kh_inrt select 'A','20070930','20071231',1.4
insert into kh_inrt select 'B','20070101','20071231',1.6
select top 8000 id=identity(int,0,1) into #
from syscolumns a,syscolumns bdeclare @min datetime
select @min=min(stdt)
from
(select stdt from kh_acno
union all
select stdt from kh_inrt) tselect isnull(A.acno,B.acno) as acno,dateadd(day,#.id,@min) as [date],isnull(acbl,0) as acbl, isnull(inrt,0) as inrt
into #T
from #
left join kh_acno A
on datediff(day,@min,A.stdt)<=#.id
and datediff(day,@min,A.eddt)>=#.id
left join kh_inrt B
on datediff(day,@min,B.stdt)<=#.id
and datediff(day,@min,B.eddt)>=#.id
and A.acno=B.acno
where isnull(A.acno,B.acno) is not null
order by acno,dateselect * from #T
/*
acno date acbl inrt
-----------------------------------------------------
A 2007-01-01 00:00:00.000 1000 1.20
A 2007-01-02 00:00:00.000 1000 1.20
A 2007-01-03 00:00:00.000 1000 1.20
A 2007-01-04 00:00:00.000 1000 1.20
A 2007-01-05 00:00:00.000 1000 1.20
A 2007-01-06 00:00:00.000 1000 1.20
A 2007-01-07 00:00:00.000 1000 1.20
A 2007-01-08 00:00:00.000 1000 1.20
A 2007-01-09 00:00:00.000 1000 1.20
A 2007-01-10 00:00:00.000 1000 1.20
A 2007-01-11 00:00:00.000 1000 1.20
A 2007-01-12 00:00:00.000 1000 1.20
A 2007-01-13 00:00:00.000 1000 1.20
A 2007-01-14 00:00:00.000 1000 1.20
A 2007-01-15 00:00:00.000 1000 1.20
A 2007-01-16 00:00:00.000 1000 1.20
A 2007-01-17 00:00:00.000 1000 1.20
A 2007-01-18 00:00:00.000 1000 1.20
A 2007-01-19 00:00:00.000 1000 1.20
A 2007-01-20 00:00:00.000 1000 1.20
A 2007-01-21 00:00:00.000 1000 1.20
A 2007-01-22 00:00:00.000 1000 1.20
A 2007-01-23 00:00:00.000 1000 1.20
A 2007-01-24 00:00:00.000 1000 1.20
A 2007-01-25 00:00:00.000 1000 1.20
A 2007-01-26 00:00:00.000 1000 1.20
............................
...........................
............................
*/
drop table #,#T
drop table kh_inrt,kh_acno
declare @kh_acno table (acno varchar(1),stdt datetime,eddt datetime,acbl smallint)
insert into @kh_acno
select 'A','20070101','20071129',1000 union all
select 'A','20071130','20071231',500 union all
select 'B','20070101','20071231',600
-->测试数据2: @kh_inrt
declare @kh_inrt table (acno varchar(1),stdt datetime,eddt datetime,inrt numeric(2,1))
insert into @kh_inrt
select 'A','20070101','20070929',1.2 union all
select 'A','20070930','20071231',1.4 union all
select 'B','20070101','20071231',1.6select * into #kh_acno from @kh_acnocreate table #temp(acno varchar(1),stdt datetime,eddt datetime,acbl smallint)
while exists (select 1 from #kh_acno a join @kh_inrt b on a.acno=b.acno and a.stdt between b.stdt and b.eddt where a.eddt>b.eddt)
begin
truncate table #temp
insert into #temp select a.acno,c.stdt,a.eddt,a.acbl from #kh_acno a join @kh_inrt b on a.acno=b.acno and a.stdt between b.stdt and b.eddt join @kh_inrt c on b.acno=c.acno and datediff(day,b.eddt,c.stdt)=1 where a.eddt>b.eddt
update a set a.eddt=b.eddt from #kh_acno a join @kh_inrt b on a.acno=b.acno and a.stdt between b.stdt and b.eddt where a.eddt>b.eddt
insert #kh_acno select * from #temp
enddeclare @stdt datetime,@eddt datetime
select @stdt='20070201',@eddt='20071225'--你的公式有误:
select a.*,[datediff]=datediff(day,case when a.stdt<@stdt then @stdt else a.stdt end,case when a.eddt>@eddt then @eddt else a.eddt end) from #kh_acno a join @kh_inrt b on a.acno=b.acno and a.stdt>=b.stdt and a.eddt<=b.eddt order by a.acno,a.stdt
/*
是:
A:1000*1.2*241+1000*1.4*61+500*1.4*26
B:600*1.6*328
而不是:
A:1000*1.2*241+1000*1.4*62+500*1.4*26
B:600*1.6*329
*/--利率惊人,每天1.4倍,应该除以100吧(仍然相当惊人):
select a.acno,acbl=sum(acbl*(datediff(day,case when a.stdt<@stdt then @stdt else a.stdt end,case when a.eddt>@eddt then @eddt else a.eddt end)+1)*b.inrt/100) from #kh_acno a join @kh_inrt b on a.acno=b.acno and a.stdt>=b.stdt and a.eddt<=b.eddt group by a.acno
/*
acno acbl
---- ----------------------------------------
A 3928.000000
B 3148.800000
*/drop table #kh_acno,#temp
insert into kh_acno values('A', '20070101', '20071129', 1000)
insert into kh_acno values('A', '20071130', '20071231', 500)
insert into kh_acno values('B', '20070101', '20071231', 600)
create table kh_inrt(acno varchar(10),stdt datetime,eddt datetime,inrt decimal(18,1))
insert into kh_inrt values('A', '20070101', '20070929', 1.2)
insert into kh_inrt values('A', '20070930', '20071231', 1.4)
insert into kh_inrt values('B', '20070101', '20071231', 1.6)
create table kh_percent(acno varchar(10),stdt datetime,eddt datetime,[percent] decimal(18,1),name varchar(10))
insert into kh_percent values('A', '20070101', '20070920', 0.3, '王一')
insert into kh_percent values('A', '20070101', '20070920', 0.7, '王二')
insert into kh_percent values('A', '20070921', '20071231', 0.3, '孙一')
insert into kh_percent values('A', '20070921', '20071231', 0.7, '王二')
insert into kh_percent values('B', '20070101', '20071231', 1, '王二')
go----------------------------------------------------------------------
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2007-02-01'
set @dt2 = '2007-12-25'
--增加一临时表
SELECT TOP 2000 id = identity(int,0,1) INTO tmp FROM syscolumns a, syscolumns b --如何计算从2007.2.1开始,到2007.12.25结束?
--A:1000*1.2*241+1000*1.4*62+500*1.4*26
--B:600*1.6*329
select t1.acno , 累加利率 = sum(t1.acbl * t2.inrt) from
(select acno , stdt = dateadd(day,tmp.id,stdt) , acbl from kh_acno , tmp where dateadd(day,tmp.id,stdt) <= eddt) t1,
(select acno , stdt = dateadd(day,tmp.id,stdt) , inrt from kh_inrt , tmp where dateadd(day,tmp.id,stdt) <= eddt) t2
where t1.acno = t2.acno and t1.stdt = t2.stdt and t1.stdt >= @dt1 and t2.stdt <= @dt2
group by t1.acno
/*
acno 累加利率
---------- ----------------------------------------
A 392800.0
B 314880.0
(所影响的行数为 2 行)
*/--现在想计算每个员工计算从2007.2.1开始,到2007.12.25结束的业务情况,还得乘以与之相关每天的分成,请问如何实现?
select t3.name , 业务情况 = sum(t1.acbl * t2.inrt * t3.[percent]) from
(select acno , stdt = dateadd(day,tmp.id,stdt) , acbl from kh_acno , tmp where dateadd(day,tmp.id,stdt) <= eddt) t1,
(select acno , stdt = dateadd(day,tmp.id,stdt) , inrt from kh_inrt , tmp where dateadd(day,tmp.id,stdt) <= eddt) t2,
(select acno , stdt = dateadd(day,tmp.id,stdt) , [percent] , name from kh_percent , tmp where dateadd(day,tmp.id,stdt) <= eddt) t3
where t1.acno = t2.acno and t1.stdt = t2.stdt and t1.acno = t3.acno and t1.stdt = t3.stdt and t1.stdt >= @dt1 and t2.stdt <= @dt2
group by t3.name
/*
name 业务情况
---------- ----------------------------------------
孙一 34320.00
王二 589840.00
王一 83520.00(所影响的行数为 3 行)
*/
drop table kh_acno,kh_inrt,kh_percent,tmp
select 天数 = datediff(day,'2007-02-01','2007-12-25') + 1
/*
天数
-----------
328
(所影响的行数为 1 行)
*/
看来我还是每天生成数据保存起来,不整理了谢谢大家!