有两个表,应收单和收款单,现在想用语句实现核销。应收与收款只有客户可以关联,收款单的金额自动去与应收单匹配,日期早的先匹配,直到收款金额为0。--应收单
if object_id('salebills') is not null drop table salebills
go
create table salebills(cusname varchar(50),vouchdate datetime,bsum float)
go
insert into salebills(cusname,vouchdate,bsum)
select '客户A','2012-01-01',100
union all
select '客户A','2012-01-05',200
union all
select '客户A','2012-01-10',300
union all
select '客户B','2012-01-02',400
union all
select '客户B','2012-01-06',500
go
select * from salebills
go
--收款单
if object_id('sa_closebills') is not null drop table sa_closebills
go
create table sa_closebills(cusname varchar(50),vouchdate datetime,bsum float)
go
insert into sa_closebills(cusname,vouchdate,bsum)
select '客户A','2012-01-08',220
union all
select '客户A','2012-01-10',300
union all
select '客户B','2012-01-05',300
union all
select '客户B','2012-01-10',500
go
select * from sa_closebills想实现的效果是客户 应收日期 应收金额 收款日期 收款金额
客户A 2012-1-1 100 2012-1-8 100
客户A 2012-1-5 200 2012-1-8 120
客户A 2012-1-5 200 2012-1-10 80
客户A 2012-1-10 300 2012-1-10 220
客户B 2012-1-2 400 2012-1-5 300
客户B 2012-1-2 400 2012-1-10 100
客户B 2012-1-6 500 2012-1-10 400先谢谢了
if object_id('salebills') is not null drop table salebills
go
create table salebills(cusname varchar(50),vouchdate datetime,bsum float)
go
insert into salebills(cusname,vouchdate,bsum)
select '客户A','2012-01-01',100
union all
select '客户A','2012-01-05',200
union all
select '客户A','2012-01-10',300
union all
select '客户B','2012-01-02',400
union all
select '客户B','2012-01-06',500
go
select * from salebills
go
--收款单
if object_id('sa_closebills') is not null drop table sa_closebills
go
create table sa_closebills(cusname varchar(50),vouchdate datetime,bsum float)
go
insert into sa_closebills(cusname,vouchdate,bsum)
select '客户A','2012-01-08',220
union all
select '客户A','2012-01-10',300
union all
select '客户B','2012-01-05',300
union all
select '客户B','2012-01-10',500
go
select * from sa_closebills想实现的效果是客户 应收日期 应收金额 收款日期 收款金额
客户A 2012-1-1 100 2012-1-8 100
客户A 2012-1-5 200 2012-1-8 120
客户A 2012-1-5 200 2012-1-10 80
客户A 2012-1-10 300 2012-1-10 220
客户B 2012-1-2 400 2012-1-5 300
客户B 2012-1-2 400 2012-1-10 100
客户B 2012-1-6 500 2012-1-10 400先谢谢了
这个要是用集合的方式的的确确挺不容易的.... 回家研究研究再说
这个要是用集合的方式的的确确挺不容易的.... 回家研究研究再说