有表
表 核销类别
字段 核销类别
记录 自动
手动表 客户档案
客户编码(PK)， 客户名称， 账期， 信用额表 应收借方
借方单据号(PK)，客户编码(FK)， 发生时间， 金额表 应收贷方
贷方单据号(PK)，客户编码(FK)， 发生时间， 金额， 核销类别(FK)表 核销对照
借方单据号(PK)， 贷方单据号(PK)， 核销金额， 核销时间题目：
把自动核销的贷方单据余额，自动按照发生时间去核销借方余额，填写核销时间为当前时间（要求写sql或者存储过程都可）
表 核销类别
字段 核销类别
记录 自动
手动表 客户档案
客户编码(PK)， 客户名称， 账期， 信用额表 应收借方
借方单据号(PK)，客户编码(FK)， 发生时间， 金额表 应收贷方
贷方单据号(PK)，客户编码(FK)， 发生时间， 金额， 核销类别(FK)表 核销对照
借方单据号(PK)， 贷方单据号(PK)， 核销金额， 核销时间题目：
把自动核销的贷方单据余额，自动按照发生时间去核销借方余额，填写核销时间为当前时间（要求写sql或者存储过程都可）
A客户第一天买了1000的东西，没付款，第二天又买了2000的东西没付款，第三天又买了1000的东西没付款，第四天付了1000用于核销第一天的500和第二天的500，第五天付了1500,用于核销第一天的500和第二天的500，第六天付了4000用于核销以前的欠款，剩下的钱先存着备用，
问第六天该怎么写sql语句？
测试数据：
if object_id('核销类别') is not null
drop table 核销类别
go
create table 核销类别(核销类别 varchar(10) primary key )
insert 核销类别
select '自动' union all
select '手动'goif object_id('客户档案') is not null
drop table 客户档案
go
create table 客户档案(客户编码 varchar(10) primary key,客户名称 varchar(10),
账期 int,信用额 float)
goif object_id('应收借方') is not null
drop table 应收借方
go
create table 应收借方(借方单据号 varchar(10) primary key,
客户编码 varchar(10),
发生时间 datetime,
金额 float,
foreign key(客户编码) references 客户档案(客户编码))
goif object_id('应收贷方') is not null
drop table 应收贷方
go
create table 应收贷方(贷方单据号 varchar(10) primary key,
客户编码 varchar(10),
发生时间 datetime,
金额 float,
核销类别 varchar(10),
foreign key(客户编码) references 客户档案(客户编码),
foreign key(核销类别) references 核销类别(核销类别))
go
if object_id('核销对照') is not null
drop table 核销对照
go
create table 核销对照(借方单据号 varchar(10) ,
贷方单据号 varchar(10) ,
核销金额 float,
核销时间 datetime,
CONSTRAINT 单据号 PRIMARY KEY (借方单据号,贷方单据号))
---------------------------------------------------------------------------
insert dbo.客户档案
select '001','a',20,'100000' union all
select '002','b',31,'150000'
go--7月1号我卖给客户a100元东西，他还没付钱
insert dbo.应收借方
select '借001','001','20110701',100
go
--7月2号我卖给客户a200元东西，他还没付钱
insert dbo.应收借方
select '借002','001','20110702',200
--7月3号 客户a付了150元
insert 应收贷方
select '贷001','001','20110703',150,'手动'
--7月4号 客户a说，这150元，付第一次那笔款的50元，第二此那笔款的100元
insert 核销对照
select '借001','贷001',50,'20110704' union all
select '借002','贷001',100,'20110704'
--8月5号客户a又付1000元，说自动核销以前的欠款
insert 应收贷方
select '贷002','001','20110805',1000,'自动'
-------------------------------------------------
--7月6号我卖给客户b1000元东西，他还没付钱
insert dbo.应收借方
select '借003','002','20110706',1000
go
--7月7号我卖给客户b2000元东西，他还没付钱
insert dbo.应收借方
select '借004','002','20110707',2000
--7月8号 客户b付了1500元
insert 应收贷方
select '贷003','002','20110708',1500,'手动'
--7月9号 客户b说，这1500元，付第一次那笔款的500元，第二此那笔款的1000元
insert 核销对照
select '借003','贷003',500,'20110709' union all
select '借004','贷003',1000,'20110709'
--7月10号 客户a又买了300的东西
insert dbo.应收借方
select '借005','001','20110710',300
--7月18号 客户a又买了300的东西
insert dbo.应收借方
select '借006','001','20110718',300
--7月20号 客户a又买了100的东西
insert dbo.应收借方
select '借007','001','20110720',100
--7月26号 客户a又买了200的东西
insert dbo.应收借方
select '借008','001','20110726',200
--4月26号 客户a又买了200的东西
insert dbo.应收借方
select '借009','001','20110426',50
--8月6号客户a又付2000元，说自动核销以前的欠款，有剩余的话先存着
insert 应收贷方
select '贷004','001','20110806',2000,'自动'
select * from dbo.应收贷方 where 核销类别='自动' order by 发生时间
贷方单据号 客户编码 发生时间 金额 核销类别
贷002 001 2011-08-05 00:00:00.000 1000 自动
贷004 001 2011-08-06 00:00:00.000 2000 自动
去核销
select *,a.金额-isnull(b.核销金额,0) as 余额
from dbo.应收借方 a full join dbo.核销对照 b on a.借方单据号=b.借方单据号 order by a.发生时间
借方单据号 客户编码 发生时间 金额 借方单据号 贷方单据号 核销金额 核销时间 余额
借009 001 2011-04-26 00:00:00.000 50 NULL NULL NULL NULL 50
借001 001 2011-07-01 00:00:00.000 100 借001 贷001 50 2011-07-04 00:00:00.000 50
借002 001 2011-07-02 00:00:00.000 200 借002 贷001 100 2011-07-04 00:00:00.000 100
借003 002 2011-07-06 00:00:00.000 1000 借003 贷003 500 2011-07-09 00:00:00.000 500
借004 002 2011-07-07 00:00:00.000 2000 借004 贷003 1000 2011-07-09 00:00:00.000 1000
借005 001 2011-07-10 00:00:00.000 300 NULL NULL NULL NULL 300
借006 001 2011-07-18 00:00:00.000 300 NULL NULL NULL NULL 300
借007 001 2011-07-20 00:00:00.000 100 NULL NULL NULL NULL 100
借008 001 2011-07-26 00:00:00.000 200 NULL NULL NULL NULL 200
题目1
select x.*,y.应收贷方合计,z.超过账期的应收余额,z.[应收余额],z.[应收余额-60],z.[应收余额]
from
(select a.客户编码,a.客户名称,a.账期,a.信用额,sum(b.金额) as 应收借方合计
from dbo.客户档案a join dbo.应收借方b
on a.客户编码=b.客户编码
group by a.客户编码,a.客户名称,a.账期,a.信用额) x
join
(select a.客户编码,sum(b.金额) as 应收贷方合计
from dbo.客户档案a, dbo.应收贷方b
where a.客户编码=b.客户编码
group by a.客户编码,a.客户名称,a.账期,a.信用额) y on x.客户编码=y.客户编码
join
(select d.客户编码,sum(d.超过账期的应收余额) as 超过账期的应收余额,sum(isnull(d.[应收余额],0)) as [应收余额],
sum(isnull(d.[应收余额-60],0)) as [应收余额-60],sum(isnull(d.[应收余额],0)) as [应收余额]
from (select a.客户编码,a.金额-isnull(b.核销金额,0) as 超过账期的应收余额,
case when datediff(day,a.发生时间, getdate())-c.账期<30 then a.金额-isnull(b.核销金额,0) end as [应收余额],
case when datediff(day,a.发生时间, getdate())-c.账期>=30 and datediff(day,a.发生时间, getdate())-c.账期<=60 then a.金额-isnull(b.核销金额,0) end as [应收余额-60],
case when datediff(day,a.发生时间, getdate())-c.账期>60 then a.金额-isnull(b.核销金额,0) end as [应收余额]
from dbo.应收借方a full join (select 借方单据号,sum(核销金额) as 核销金额from dbo.核销对照group by 借方单据号) b
on a.借方单据号=b.借方单据号join dbo.客户档案c on a.客户编码=c.客户编码
and datediff(day,a.发生时间, getdate())>c.账期 ) d group by d.客户编码) z on y.客户编码=z.客户编码
题目2
insert dbo.核销对照
select top 1 a. 借方单据号,b.贷方单据号,case when a.余额<b.余额then a.余额else b.余额end as 核销金额,getdate() as 核销时间
from (select a.*,b.核销金额,a.金额-isnull(b.核销金额,0) as 余额from dbo.应收贷方a full join
(select 贷方单据号,sum(核销金额) as 核销金额from dbo.核销对照group by 贷方单据号) b
on a.贷方单据号=b.贷方单据号 where a.金额-isnull(b.核销金额,0)<>0 ) b,
(select a.*,b.核销金额,a.金额-isnull(b.核销金额,0) as 余额from dbo.应收借方a full join
(select 借方单据号,sum(核销金额) as 核销金额from dbo.核销对照group by 借方单据号) b
on a.借方单据号=b.借方单据号 where a.金额-isnull(b.核销金额,0)<>0 ) a
where a.客户编码=b.客户编码
WHILE @@ROWCOUNT>0
begin
insert dbo.核销对照
select top 1 a. 借方单据号,b.贷方单据号,case when a.余额<b.余额then a.余额else b.余额end as 核销金额,getdate() as 核销时间
from (select a.*,b.核销金额,a.金额-isnull(b.核销金额,0) as 余额from dbo.应收贷方a full join
(select 贷方单据号,sum(核销金额) as 核销金额from dbo.核销对照group by 贷方单据号) b
on a.贷方单据号=b.贷方单据号 where a.金额-isnull(b.核销金额,0)<>0 ) b,
(select a.*,b.核销金额,a.金额-isnull(b.核销金额,0) as 余额from dbo.应收借方a full join
(select 借方单据号,sum(核销金额) as 核销金额from dbo.核销对照group by 借方单据号) b
on a.借方单据号=b.借方单据号 where a.金额-isnull(b.核销金额,0)<>0 ) a
END
创建测试表，插入测试数据
if object_id('核销类别') is not null
drop table 核销类别
go
create table 核销类别(核销类别varchar(10) primary key )
insert 核销类别
select '自动' union all
select '手动'goif object_id('客户档案') is not null
drop table 客户档案
go
create table 客户档案(客户编码varchar(10) primary key,客户名称varchar(10),
账期int,信用额float)
goif object_id('应收借方') is not null
drop table 应收借方
go
create table 应收借方(借方单据号varchar(10) primary key,
客户编码varchar(10),
发生时间datetime,
金额float,
foreign key(客户编码) references 客户档案(客户编码))
goif object_id('应收贷方') is not null
drop table 应收贷方
go
create table 应收贷方(贷方单据号varchar(10) primary key,
客户编码varchar(10),
发生时间datetime,
金额float,
核销类别varchar(10),
foreign key(客户编码) references 客户档案(客户编码),
foreign key(核销类别) references 核销类别(核销类别))
go
if object_id('核销对照') is not null
drop table 核销对照
go
create table 核销对照(借方单据号varchar(10) ,
贷方单据号varchar(10) ,
核销金额float,
核销时间datetime,
CONSTRAINT 单据号PRIMARY KEY (借方单据号,贷方单据号))
---------------------------------------------------------------------------
insert dbo.客户档案
select '001','a',20,'100000' union all
select '002','b',31,'150000'
go--7月号我卖给客户a100元东西，他还没付钱
insert dbo.应收借方
select '借','001','20110701',100
go
--7月号我卖给客户a200元东西，他还没付钱
insert dbo.应收借方
select '借','001','20110702',200
--7月号客户a付了元
insert 应收贷方
select '贷','001','20110703',150,'手动'
--7月号客户a说，这元，付第一次那笔款的元，第二此那笔款的元
insert 核销对照
select '借','贷',50,'20110704' union all
select '借','贷',100,'20110704'
--7月号客户a说，付款元，付第一次那笔款的元
insert 应收贷方
select '贷','001','20110705',50,'手动'
insert 核销对照
select '借','贷',50,'20110705'
--8月号客户a又付元，说自动核销以前的欠款
insert 应收贷方
select '贷','001','20110805',1000,'自动'
-------------------------------------------------
--7月号我卖给客户b1000元东西，他还没付钱
insert dbo.应收借方
select '借','002','20110706',1000
go
--7月号我卖给客户b2000元东西，他还没付钱
insert dbo.应收借方
select '借','002','20110707',2000
--7月号客户b付了元
insert 应收贷方
select '贷','002','20110708',1500,'手动'
--7月号客户b说，这元，付第一次那笔款的元，第二此那笔款的元
insert 核销对照
select '借','贷',500,'20110709' union all
select '借','贷',1000,'20110709'
--7月号客户a又买了的东西
insert dbo.应收借方
select '借','001','20110710',300
--7月号客户a又买了的东西
insert dbo.应收借方
select '借','001','20110718',300
--7月号客户a又买了的东西
insert dbo.应收借方
select '借','001','20110720',100
--7月号客户a又买了的东西
insert dbo.应收借方
select '借','001','20110726',200
--4月号客户a又买了的东西
insert dbo.应收借方
select '借','001','20110426',50
--8月号客户a又付元，说自动核销以前的欠款，有剩余的话先存着
insert 应收贷方
select '贷','001','20110806',2000,'自动'