求库存台账SQL处理方法现存量表
id code qty
1 A 100出入库表
id ddate type code qty
1 2009-03-02 00:00:00.000 入库 A 100.0
2 2009-03-03 00:00:00.000 入库 A 50.0
3 2009-03-04 00:00:00.000 出库 A 150.0
4 2009-03-06 00:00:00.000 入库 A 20.0
5 2009-03-09 00:00:00.000 出库 A 30.0求台账:
code ddate qcqty rkqty ckqty jcqty
A NULL 100 0 0 100
A 2009-03-02 100 100 0 200
A 2009-03-03 200 50 0 250
A 2009-03-04 250 0 150 100
A 2009-03-06 100 20 0 120
A 2009-03-09 120 0 30 90
id code qty
1 A 100出入库表
id ddate type code qty
1 2009-03-02 00:00:00.000 入库 A 100.0
2 2009-03-03 00:00:00.000 入库 A 50.0
3 2009-03-04 00:00:00.000 出库 A 150.0
4 2009-03-06 00:00:00.000 入库 A 20.0
5 2009-03-09 00:00:00.000 出库 A 30.0求台账:
code ddate qcqty rkqty ckqty jcqty
A NULL 100 0 0 100
A 2009-03-02 100 100 0 200
A 2009-03-03 200 50 0 250
A 2009-03-04 250 0 150 100
A 2009-03-06 100 20 0 120
A 2009-03-09 120 0 30 90
select qty+(select sum(qty) from 出入库表 where ddate<=a.ddate)+b.qty
from 出入库表 a
left join 现存量表 b ON a.code = b.code
create table table1(id int,code varchar(1),qty int)
insert into table1 values(1,'A',100)create table table2(id int,ddate datetime,[type] varchar(10),code varchar(1),qty int)
insert into table2 values(1,'2009-03-02','入库','A',100 )
insert into table2 values(2,'2009-03-03','入库','A',50 )
insert into table2 values(3,'2009-03-04','出库 ','A',150 )
insert into table2 values(4,'2009-03-06','入库','A',20 )
insert into table2 values(5,'2009-03-09','出库 ','A',30 )SELECT * FROM table1select code,ddate=null,qcqty=qty,rkqty=0,ckqty=0,jcqty=qty
from table1
union all
SELECT a.code,a.ddate
,qcqty=b.qty+isnull((select sum(case when [type] ='入库' then qty else -1*qty end) from table2 where ddate<a.ddate),0)
,rkqty= case when [type] ='入库' then a.qty else 0 end
,ckqty= case when [type] ='入库' then 0 else a.qty end
,jcqty=(select sum(qty) from table1)+(select sum(case when [type] ='入库' then qty else -1*qty end) from table2 where ddate<=a.ddate)
FROM table2 a
left join table1 b ON a.code = b.code/*
code ddate qcqty rkqty ckqty jcqty
---- ----------------------- ----------- ----------- ----------- -----------
A NULL 100 0 0 100
A 2009-03-02 00:00:00.000 100 100 0 200
A 2009-03-03 00:00:00.000 200 50 0 250
A 2009-03-04 00:00:00.000 250 0 150 100
A 2009-03-06 00:00:00.000 100 20 0 120
A 2009-03-09 00:00:00.000 120 0 30 90(6 行受影响)
*/