下面的代码只与下量表和收款表的最后一条记录以及收款总表有关，与下量表和收款表前面的记录无关。
不知道楼主是不是这个意思。 create table xlb(bh int, xlrq datetime,xlje int)
insert xlb select 1, '2008-1-1', 4
union select 1,'2008-2-1', 3
union select 1,'2008-3-1', 4
union select 1,'2008-4-1', 6
create table ssb(bh int, skrq datetime,ssje int)
insert ssb select 1,'2008-1-2', 2
union select 1,'2008-2-2', 3
union select 1,'2008-2-3', 3
union select 1,'2008-2-4', 6
create table hkzb(bh int, xlrq datetime,skrq datetime,hkje int)
insert hkzb select 1,'2008-1-1' ,'2008-1-2', 2
union select 1,'2008-1-1' ,'2008-2-2', 2
union select 1,'2008-2-1' ,'2008-2-2', 1
union select 1,'2008-2-1' ,'2008-2-3', 2
go
create proc get_hkb
as
begin
declare @xlb table(bh int, xlrq datetime,xlje int)
declare @ssb table(bh int, skrq datetime,ssje int)
declare @hkb table(bh int, xlrq datetime,skrq datetime,hkje int)
declare @zh table(bh int,xlrq datetime,xlje int,hkje int,skrq datetime,ssje int,fpje int)
--将数据转入新表，已经记录了还款的就不要了。
insert @xlb select * from xlb a where xlrq >isnull((select max(xlrq) from hkzb where bh= a.bh),'')
insert @ssb select * from ssb a where skrq >isnull((select max(skrq) from hkzb where bh= a.bh),'')
--但最后一次存在问题，需要补进新表，两种情况。
--获取还款总表中最后的下量日期和收款日期
insert @zh select bh,max(xlrq) xlrq,0,0,max(skrq) skrq,0,0 from hkzb group by bh
--获取最后下量日期对应的下量金额
update @zh set xlje = b.xlje from @zh a,xlb b where a.bh = b.bh and a.xlrq = b.xlrq
--获取最后收款日期对应的实收金额
update @zh set ssje = b.ssje from @zh a,ssb b where a.bh = b.bh and a.skrq = b.skrq
--获取最后下量对应的还款总数
update @zh set hkje = isnull((select sum(hkje) from hkzb where a.bh= bh and a.xlrq = xlrq ),0) from @zh a
--获取最后收款对应的分配总数
update @zh set fpje = isnull((select sum(hkje) from hkzb where a.bh= bh and a.skrq = skrq ),0) from @zh a
--不可能存在即没有还清的下量，又有收款的剩余，如果存在，说明前面的数据有错误，直接返回。
if exists(select 1 from @zh where xlje <> hkje and ssje <> fpje) return -1
--补进数据
--如果xlje>hkje 说明最后一次下量还没有还清,剩下的放入下量表中。
insert @xlb select bh,xlrq,xlje-hkje from @zh where xlje>hkje
--如果ssje>fpje 说明最后一次收款还没有分配完，剩下的放入收款表中。
insert @ssb select bh,skrq,ssje-fpje from @zh where ssje>fpje
--现在与前面的还款总表无关了，直接计算
select bh,convert(char(10),xlrq,120) xlrq,convert(char(10),skrq,120) skrq,hkje from(
select a.bh,xlrq,skrq,hkje=case when zxlje>zssje then zssje+xlje-zxlje else zxlje+ssje-zssje end from
(select bh,xlrq,xlje,zxlje=(select sum(xlje) from @xlb where bh = a.bh and xlrq<=a.xlrq) from @xlb a)a,
(select bh,skrq,ssje,zssje=(select sum(ssje) from @ssb where bh = a.bh and skrq<=a.skrq) from @ssb a)b
where a.bh = b.bh and ((zxlje>zssje and zxlje-zssje <xlje) or (zssje>=zxlje and zssje-zxlje <ssje)))c
order by bh,xlrq,skrq
return 0
end
go
get_hkb
go
drop table xlb,ssb,hkzb
go
drop proc get_hkb
/*
bh xlrq skrq hkje
----------- ---------- ---------- -----------
1 2008-03-01 2008-02-03 1
1 2008-03-01 2008-02-04 3
1 2008-04-01 2008-02-04 3
*/
不知道楼主是不是这个意思。 create table xlb(bh int, xlrq datetime,xlje int)
insert xlb select 1, '2008-1-1', 4
union select 1,'2008-2-1', 3
union select 1,'2008-3-1', 4
union select 1,'2008-4-1', 6
create table ssb(bh int, skrq datetime,ssje int)
insert ssb select 1,'2008-1-2', 2
union select 1,'2008-2-2', 3
union select 1,'2008-2-3', 3
union select 1,'2008-2-4', 6
create table hkzb(bh int, xlrq datetime,skrq datetime,hkje int)
insert hkzb select 1,'2008-1-1' ,'2008-1-2', 2
union select 1,'2008-1-1' ,'2008-2-2', 2
union select 1,'2008-2-1' ,'2008-2-2', 1
union select 1,'2008-2-1' ,'2008-2-3', 2
go
create proc get_hkb
as
begin
declare @xlb table(bh int, xlrq datetime,xlje int)
declare @ssb table(bh int, skrq datetime,ssje int)
declare @hkb table(bh int, xlrq datetime,skrq datetime,hkje int)
declare @zh table(bh int,xlrq datetime,xlje int,hkje int,skrq datetime,ssje int,fpje int)
--将数据转入新表，已经记录了还款的就不要了。
insert @xlb select * from xlb a where xlrq >isnull((select max(xlrq) from hkzb where bh= a.bh),'')
insert @ssb select * from ssb a where skrq >isnull((select max(skrq) from hkzb where bh= a.bh),'')
--但最后一次存在问题，需要补进新表，两种情况。
--获取还款总表中最后的下量日期和收款日期
insert @zh select bh,max(xlrq) xlrq,0,0,max(skrq) skrq,0,0 from hkzb group by bh
--获取最后下量日期对应的下量金额
update @zh set xlje = b.xlje from @zh a,xlb b where a.bh = b.bh and a.xlrq = b.xlrq
--获取最后收款日期对应的实收金额
update @zh set ssje = b.ssje from @zh a,ssb b where a.bh = b.bh and a.skrq = b.skrq
--获取最后下量对应的还款总数
update @zh set hkje = isnull((select sum(hkje) from hkzb where a.bh= bh and a.xlrq = xlrq ),0) from @zh a
--获取最后收款对应的分配总数
update @zh set fpje = isnull((select sum(hkje) from hkzb where a.bh= bh and a.skrq = skrq ),0) from @zh a
--不可能存在即没有还清的下量，又有收款的剩余，如果存在，说明前面的数据有错误，直接返回。
if exists(select 1 from @zh where xlje <> hkje and ssje <> fpje) return -1
--补进数据
--如果xlje>hkje 说明最后一次下量还没有还清,剩下的放入下量表中。
insert @xlb select bh,xlrq,xlje-hkje from @zh where xlje>hkje
--如果ssje>fpje 说明最后一次收款还没有分配完，剩下的放入收款表中。
insert @ssb select bh,skrq,ssje-fpje from @zh where ssje>fpje
--现在与前面的还款总表无关了，直接计算
select bh,convert(char(10),xlrq,120) xlrq,convert(char(10),skrq,120) skrq,hkje from(
select a.bh,xlrq,skrq,hkje=case when zxlje>zssje then zssje+xlje-zxlje else zxlje+ssje-zssje end from
(select bh,xlrq,xlje,zxlje=(select sum(xlje) from @xlb where bh = a.bh and xlrq<=a.xlrq) from @xlb a)a,
(select bh,skrq,ssje,zssje=(select sum(ssje) from @ssb where bh = a.bh and skrq<=a.skrq) from @ssb a)b
where a.bh = b.bh and ((zxlje>zssje and zxlje-zssje <xlje) or (zssje>=zxlje and zssje-zxlje <ssje)))c
order by bh,xlrq,skrq
return 0
end
go
get_hkb
go
drop table xlb,ssb,hkzb
go
drop proc get_hkb
/*
bh xlrq skrq hkje
----------- ---------- ---------- -----------
1 2008-03-01 2008-02-03 1
1 2008-03-01 2008-02-04 3
1 2008-04-01 2008-02-04 3
*/
insert xlb select 1, '2008-1-1', 4
union select 1,'2008-2-1', 3
union select 1,'2008-3-1', 4
union select 1,'2008-4-1', 2
create table ssb(bh int, skrq datetime,ssje int)
insert ssb select 1,'2008-1-2', 2
union select 1,'2008-2-2', 3
union select 1,'2008-2-3', 3
union select 1,'2008-2-4', 6
create table hkzb(bh int, xlrq datetime,skrq datetime,hkje int)
insert hkzb select 1,'2008-1-1' ,'2008-1-2', 2
union select 1,'2008-1-1' ,'2008-2-2', 2
union select 1,'2008-2-1' ,'2008-2-2', 1
union select 1,'2008-2-1' ,'2008-2-3', 2
go
create proc get_hkb
as
begin
declare @xlb table(bh int, xlrq datetime,xlje int)
declare @ssb table(bh int, skrq datetime,ssje int)
declare @hkb table(bh int, xlrq datetime,skrq datetime,hkje int)
declare @zh table(bh int,xlrq datetime,xlje int,hkje int,skrq datetime,ssje int,fpje int)
--将数据转入新表，已经记录了还款的就不要了。
insert @xlb select * from xlb a where xlrq >isnull((select max(xlrq) from hkzb where bh= a.bh),'')
insert @ssb select * from ssb a where skrq >isnull((select max(skrq) from hkzb where bh= a.bh),'')
--但最后一次存在问题，需要补进新表，两种情况。
--获取还款总表中最后的下量日期和收款日期
insert @zh select bh,max(xlrq) xlrq,0,0,max(skrq) skrq,0,0 from hkzb group by bh
--获取最后下量日期对应的下量金额
update @zh set xlje = b.xlje from @zh a,xlb b where a.bh = b.bh and a.xlrq = b.xlrq
--获取最后收款日期对应的实收金额
update @zh set ssje = b.ssje from @zh a,ssb b where a.bh = b.bh and a.skrq = b.skrq
--获取最后下量对应的还款总数
update @zh set hkje = isnull((select sum(hkje) from hkzb where a.bh= bh and a.xlrq = xlrq ),0) from @zh a
--获取最后收款对应的分配总数
update @zh set fpje = isnull((select sum(hkje) from hkzb where a.bh= bh and a.skrq = skrq ),0) from @zh a
--不可能存在即没有还清的下量，又有收款的剩余，如果存在，说明前面的数据有错误，直接返回。
if exists(select 1 from @zh where xlje <> hkje and ssje <> fpje) return -1
--补进数据
--如果xlje>hkje 说明最后一次下量还没有还清,剩下的放入下量表中。
insert @xlb select bh,xlrq,xlje-hkje from @zh where xlje>hkje
--如果ssje>fpje 说明最后一次收款还没有分配完，剩下的放入收款表中。
insert @ssb select bh,skrq,ssje-fpje from @zh where ssje>fpje
--现在与前面的还款总表无关了，直接计算select t1.bh,convert(char(10),t1.xlrq,120) xlrq,convert(char(10),t2.skrq,120) skrq,
hkje= (case when t2.sum_ssje>t1.sum_xlje then t1.sum_xlje else t2.sum_ssje end) -
(case when t2.sum_ssje-t2.ssje>t1.sum_xlje-t1.xlje then t2.sum_ssje-t2.ssje else t1.sum_xlje-t1.xlje end)
from (select *,sum_xlje=(select sum(xlje) from @xlb where bh=a.bh and xlrq<=a.xlrq) from @xlb a)t1
join (select *,sum_ssje=(select sum(ssje) from @ssb where bh=a.bh and ssje<=a.ssje) from @ssb a)t2 on t1.bh=t2.bh
where t2.sum_ssje-t2.ssje<t1.sum_xlje and t2.sum_ssje>t1.sum_xlje-t1.xlje order by xlrq,skrq
end
go
get_hkb
go
drop table xlb,ssb,hkzb
go
drop proc get_hkb
/*
bh xlrq skrq hkje
----------- ---------- ---------- -----------
1 2008-03-01 2008-02-03 1
1 2008-03-01 2008-02-04 3
1 2008-04-01 2008-02-04 2
*/