计利息问题

[sunfor]

采购单（表TB1）
名称	    日期	     金额
广工学院	2012-05-13	6000.00
广工学院	2012-06-15	2000.00
理工学院	2012-05-13	1000.00
理工学院	2012-06-03	2000.00
理工学院	2012-07-20	3000.00

收款单（表TB2）
名称	    日期1	     金额1	  日期2	  金额2
广工学院	2012-07-13	5000.00	2012-09-10	5000
理工学院	2012-08-13	15000.00	NULL	NULL

在收款单里，是从客户收款回来的第一次时间（日期1,金额1）开始计利息。
如果一次没收完款，就在第二次时间（日期2,金额2）来收。

例如：广工学院在7、9月各收5000元，而广工学院在5月采购了6000元，所以把6000元分为7月及9月计，5000元的7月，1000元9月算。

名称	    日期	     金额	     想得到这利息金额？
广工学院	2012-05-13	6000.00	5000.00*（2012-07-13 - 2012-05-13）*0.00334 + 1000.00*（2012-09-10 - 2012-05-13）*0.00334
广工学院	2012-06-15	2000.00	2000.00*（2012-09-10 - 2012-06-15）*0.00334
理工学院	2012-05-13	1000.00	1000.00*（2012-08-13 - 2012-05-13）*0.00334
理工学院	2012-06-03	2000.00	2000.00*（2012-08-13 - 2012-05-13）*0.00334
理工学院	2012-07-20	3000.00	3000.00*（2012-08-13 - 2012-05-13）*0.00334
请教上面的SQL语句?

[Vidor]

-- table definition
with tb1(name,date,money) as
(
	select '广工学院', '2012-05-13', 6000.00 union all
	select '广工学院', '2012-06-15', 2000.00 union all
	select '理工学院', '2012-05-13', 1000.00 union all
	select '理工学院', '2012-06-03', 2000.00 union all
	select '理工学院', '2012-07-20', 3000.00
), tb2(name,date1,money1,date2,money2) as
(
	select '广工学院', '2012-07-13', 5000.00, '2012-09-10', 5000 union all
	select '理工学院', '2012-08-13', 15000.00, null, null
),
-- start from here
a as
(
	select *,m=(select isnull(sum(money),0) from tb1 where name=t.name and date<t.date) from tb1 t
)
select *,
	case
		when money1>=money+m then money*datediff(day,date,date1)*0.00334
		when money1>m then (money1-m)*datediff(day,date,date1)*0.00334 + isnull((money-money1+m)*datediff(day,date,date2)*0.00334, 0)
		else money*datediff(day,date,date2)*0.00034
	end
from a join tb2 b on a.name=b.name
where isnull(money1,0)+isnull(money2,0)>=money+m
/*
name	date	money	m	name	date1	money1	date2	money2	(无列名)
广工学院	2012-05-13	6000.00	0.00	广工学院	2012-07-13	5000.00	2012-09-10	5000	1419.500000
广工学院	2012-06-15	2000.00	6000.00	广工学院	2012-07-13	5000.00	2012-09-10	5000	59.160000
理工学院	2012-05-13	1000.00	0.00	理工学院	2012-08-13	15000.00	NULL	NULL	307.280000
理工学院	2012-06-03	2000.00	1000.00	理工学院	2012-08-13	15000.00	NULL	NULL	474.280000
理工学院	2012-07-20	3000.00	3000.00	理工学院	2012-08-13	15000.00	NULL	NULL	240.480000
*/

[-晴天]

create table TB1(名称 varchar(10),日期 datetime,金额 decimal(18,2)) insert into tb1 select '广工学院','2012-05-13',6000.00 insert into tb1 select '广工学院','2012-06-15',2000.00 insert into tb1 select '理工学院','2012-05-13',1000.00 insert into tb1 select '理工学院','2012-06-03',2000.00 insert into tb1 select '理工学院','2012-07-20',3000.00 create table TB2(名称 varchar(20),日期1 datetime,金额1 decimal(18,2),日期2 datetime,金额2 decimal(18,2)) insert into tb2 select '广工学院','2012-07-13',5000.00,'2012-09-10',5000 insert into tb2 select '理工学院','2012-08-13',15000.00,NULL,NULL go ;with c1 as( select *,row_number()over(partition by 名称 order by 日期)as rn from tb1 ),c2 as( select *,row_number()over(partition by 名称 order by 日期1)as rn1 from( select 名称,日期1,金额1 from tb2 union all select 名称,日期2,金额2 from tb2 where 金额2>0 )t ),c3 as( select a.rn,b.rn1,a.名称,a.日期,convert(decimal(18,2),a.金额)金额,b.日期1,b.金额1,convert(decimal(18,2),a.金额-b.金额1) as ye from c1 a inner join c2 b on a.名称=b.名称 and a.rn=1 and b.rn1=1 union all select a.rn,b.rn1,a.名称,a.日期,convert(decimal(18,2),a.ye),b.日期1,b.金额1,convert(decimal(18,2),a.ye-b.金额1) from c3 a inner join c2 b on a.名称=b.名称 and a.rn1=b.rn1-1 and a.ye>0 union all select a.rn,b.rn1,a.名称,a.日期,convert(decimal(18,2),a.金额),b.日期1,b.金额1,convert(decimal(18,2),a.金额+b.ye) from c1 a inner join c3 b on a.名称=b.名称 and a.rn=b.rn+1 and b.ye<=0 ),c4 as( select 名称,日期,datediff(d,日期1,日期)*(case when 金额>金额1 then 金额-ye else 金额 end)*0.00334 as lx from c3 )select 名称,日期,sum(-lx)as 利息 from c4 group by 名称,日期 /* 名称 日期 利息 ---------- ----------------------- --------------------------------------- 广工学院 2012-05-13 00:00:00.000 1419.5000000 理工学院 2012-05-13 00:00:00.000 307.2800000 理工学院 2012-06-03 00:00:00.000 474.2800000 广工学院 2012-06-15 00:00:00.000 581.1600000 理工学院 2012-07-20 00:00:00.000 240.4800000 (5 行受影响) */ go drop table tb1,tb2

[-晴天]

没试过金额正好的,或许c3中间那句应该是je>=0而下面那句是je<0.

[你的选择B]

--table1
if OBJECT_ID('table1','u') is not null
drop table table1

go
create table table1
(
Name nvarchar(20),
StartDate date,
TotalMoney decimal(18,2)
)

go
insert into table1 values
('广工学院','2012-05-13',6000.00),
('广工学院','2012-06-15',2000.00),
('理工学院','2012-05-13',1000.00),
('理工学院','2012-06-03',2000.00),
('理工学院','2012-07-20',3000.00)

--table2
if OBJECT_ID('table2','u') is not null
drop table table2

go
create table table2
(
Name nvarchar(20),
Date1 date,
Money1 decimal(18,2),
Date2 date,
Money2 decimal(18,2)
)

go
insert into table2 values
('广工学院','2012-07-13',5000.00,'2012-09-10',5000),
('理工学院','2012-08-13',15000.00,null,null)

--SQL
select A.Name,A.StartDate,A.TotalMoney,B.Date1,B.Money1,B.Date2,B.Money2,
case when A.TotalMoney<=B.Money1 then A.TotalMoney*( DATEDIFF(DAY,A.StartDate,B.Date1)*0.00334)
else B.Money1*(DATEDIFF(DAY,A.StartDate,B.Date1)*0.00334)+ 
     (A.TotalMoney- B.Money2)*(DATEDIFF(DAY,A.StartDate,B.Date2)*0.00334)
end  金额
from table1 A
inner join table2 B on A.Name=B.Name

--结果集
/*
Name        StartDate   TotalMoney    Date1         Money1      Date2       Money2    金额
广工学院	2012-05-13	6000.00	      2012-07-13	5000.00	    2012-09-10	5000.00	  1419.5000000
广工学院	2012-06-15	2000.00	      2012-07-13	5000.00	    2012-09-10	5000.00	  187.0400000
理工学院	2012-05-13	1000.00	      2012-08-13	15000.00    NULL	    NULL	  307.2800000
理工学院	2012-06-03	2000.00	      2012-08-13	15000.00	NULL	    NULL	  474.2800000
理工学院	2012-07-20	3000.00	      2012-08-13	15000.00	NULL	    NULL	  240.4800000
*/