计利息问题

Question

采购单（表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语句?

Answer 1

-- 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
*/

Answer 2

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

Answer 3

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

Answer 4

--table1
if OBJECT_ID('table1','u') is not null
drop table table1go
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 table2go
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
*/