先进先出法计算结存货品单据
instored进仓单
billid bd bc storeid goodsid qty
001 2009-08-01 090801001 a仓 g001 700
002 2009-08-20 090820001 a仓 g001 800
003 2009-08-23 090823001 b仓 g001 600
009 2009-08-20 090820002 a仓 g001 300
004 2009-08-23 090823002 b仓 k002 700
005 2009-08-23 090823003 b仓 k002 2000006 2009-09-26 090926001 a仓 g001 600
007 2009-09-27 090927001 b仓 k002 400outstored出仓单
billid bd bc storeid goodsid qty
801 2009-08-20 090820901 a仓 g001 700
802 2009-08-20 090820902 a仓 g001 900
804 2009-08-28 090828001 b仓 k002 800806 2009-09-26 090926901 a仓 g001 600
807 2009-09-28 090928008 b仓 k002 1000
求结果如下: 希望能做成函数去查询
SELECT * FROM 函数('2009-08-01','2009-08-31')时
billid bd bc storeid goodsid qty
009 2009-08-20 090820002 a仓 g001 200
003 2009-08-23 090823001 b仓 g001 600
005 2009-08-23 090823003 b仓 k002 1900
SELECT * FROM 函数('2009-08-01','2009-09-30')时
billid bd bc storeid goodsid qty
006 2009-09-26 090926001 a仓 g001 200
003 2009-08-23 090823001 b仓 g001 600
005 2009-08-23 090823003 b仓 k002 900
007 2009-09-27 090927001 b仓 k002 400
instored进仓单
billid bd bc storeid goodsid qty
001 2009-08-01 090801001 a仓 g001 700
002 2009-08-20 090820001 a仓 g001 800
003 2009-08-23 090823001 b仓 g001 600
009 2009-08-20 090820002 a仓 g001 300
004 2009-08-23 090823002 b仓 k002 700
005 2009-08-23 090823003 b仓 k002 2000006 2009-09-26 090926001 a仓 g001 600
007 2009-09-27 090927001 b仓 k002 400outstored出仓单
billid bd bc storeid goodsid qty
801 2009-08-20 090820901 a仓 g001 700
802 2009-08-20 090820902 a仓 g001 900
804 2009-08-28 090828001 b仓 k002 800806 2009-09-26 090926901 a仓 g001 600
807 2009-09-28 090928008 b仓 k002 1000
求结果如下: 希望能做成函数去查询
SELECT * FROM 函数('2009-08-01','2009-08-31')时
billid bd bc storeid goodsid qty
009 2009-08-20 090820002 a仓 g001 200
003 2009-08-23 090823001 b仓 g001 600
005 2009-08-23 090823003 b仓 k002 1900
SELECT * FROM 函数('2009-08-01','2009-09-30')时
billid bd bc storeid goodsid qty
006 2009-09-26 090926001 a仓 g001 200
003 2009-08-23 090823001 b仓 g001 600
005 2009-08-23 090823003 b仓 k002 900
007 2009-09-27 090927001 b仓 k002 400
billid bd bc storeid goodsid qty
009 2009-08-20 090820002 a仓 g001 200
003 2009-08-23 090823001 b仓 g001 600
005 2009-08-23 090823003 b仓 k002 2700
005 2009-08-28 090823003 b仓 k002 1900
或者:SELECT * FROM 函数('2009-08-01','2009-08-31')时
billid bd bc storeid goodsid qty
009 2009-08-20 090820002 a仓 g001 200
003 2009-08-23 090823001 b仓 g001 600
005 2009-08-28 090823003 b仓 k002 1900
create table tb1(billid varchar(10), bd datetime, bc varchar(10), storeid varchar(10), goodsid varchar(10), qty int)
insert into tb1 values('001' , '2009-08-01' , '090801001' , 'a仓' , 'g001' , 700 )
insert into tb1 values('002' , '2009-08-20' , '090820001' , 'a仓' , 'g001' , 800 )
insert into tb1 values('003' , '2009-08-23' , '090823001' , 'b仓' , 'g001' , 600 )
insert into tb1 values('009' , '2009-08-20' , '090820002' , 'a仓' , 'g001' , 300 )
insert into tb1 values('004' , '2009-08-23' , '090823002' , 'b仓' , 'k002' , 700 )
insert into tb1 values('005' , '2009-08-23' , '090823003' , 'b仓' , 'k002' , 2000)
insert into tb1 values('006' , '2009-09-26' , '090926001' , 'a仓' , 'g001' , 600 )
insert into tb1 values('007' , '2009-09-27' , '090927001' , 'b仓' , 'k002' , 400 )
create table tb2(billid varchar(10), bd datetime, bc varchar(10), storeid varchar(10), goodsid varchar(10), qty int)
insert into tb2 values('801' , '2009-08-20' , '090820901' , 'a仓' , 'g001' , 700 )
insert into tb2 values('802' , '2009-08-20' , '090820902' , 'a仓' , 'g001' , 900 )
insert into tb2 values('804' , '2009-08-28' , '090828001' , 'b仓' , 'k002' , 800 )
insert into tb2 values('806' , '2009-09-26' , '090926901' , 'a仓' , 'g001' , 600 )
insert into tb2 values('807' , '2009-09-28' , '090928008' , 'b仓' , 'k002' , 1000)
goCREATE PROCEDURE my_proc @dt1 as datetime , @dt2 as datetime
AS
select m.billid , m.bd , m.bc , m.storeid , m.goodsid ,isnull(m.qty,0) - isnull(n.qty , 0) qty from
(select max(billid) billid , max(bd) bd , max(bc) bc , storeid , goodsid , sum(qty) qty from tb1 where bd between @dt1 and @dt2 group by storeid , goodsid ) m
left join
(select max(bd) bd , max(bc) bc , storeid , goodsid , sum(qty) qty from tb2 where bd between @dt1 and @dt2 group by storeid , goodsid ) n
on m.storeid = n.storeid and m.goodsid = n.goodsid
goexec my_proc '2009-08-01','2009-08-31'
/*
billid bd bc storeid goodsid qty
---------- ------------------------------------------------------ ---------- ---------- ---------- -----------
009 2009-08-20 00:00:00.000 090820002 a仓 g001 200
003 2009-08-23 00:00:00.000 090823001 b仓 g001 600
005 2009-08-23 00:00:00.000 090823003 b仓 k002 1900(所影响的行数为 3 行)
*/exec my_proc '2009-08-01','2009-09-30'/*
billid bd bc storeid goodsid qty
---------- ------------------------------------------------------ ---------- ---------- ---------- -----------
009 2009-09-26 00:00:00.000 090926001 a仓 g001 200
003 2009-08-23 00:00:00.000 090823001 b仓 g001 600
007 2009-09-27 00:00:00.000 090927001 b仓 k002 1300(所影响的行数为 3 行)
*/drop proc my_proc
drop table tb1 , tb2
billid bd bc storeid goodsid qty
006 2009-09-26 090926001 a仓 g001 200
003 2009-08-23 090823001 b仓 g001 600
005 2009-08-23 090823003 b仓 k002 900
007 2009-09-27 090927001 b仓 k002 400 我再手工去算一下,这里是对的了。
在进仓单里按日期一张一张的减出仓单的数量,如果进仓单不够减出仓单的,要在下一张进仓单里面冲减。
还要注意同一天可能有同一个货品进仓,出仓单也同样可以是同一天同一货品出仓。
billid bd bc storeid goodsid qty
009 2009-08-20 090820002 a仓 g001 200
003 2009-08-23 090823001 b仓 g001 600
005 2009-08-23 090823003 b仓 k002 2700 ----这里肯定是不对的了
005 2009-08-28 090823003 b仓 k002 1900 ----
billid bd bc storeid goodsid qty
---------- ------------------------------------------------------ ---------- ---------- ---------- -----------
009 2009-09-26 00:00:00.000 090926001 a仓 g001 200
003 2009-08-23 00:00:00.000 0823001 b仓 g001 600
007 2009-09-27 00:00:00.000 0927001 b仓 k002 1300 ---这里---billid=007这张单进仓数量只有400,不可能是1300的。
returns table
as
begin
declare @returntable table(billid varchar(10),bd varchar(20),bc varchar(20),storeid varchar(20),goodsid varchar(10),qty int)
declare @table1 table(goodsid vahrcar(10),qty int)
insert into @table1
select goodsid,sum(qty) from outsotred where db between @datestart and @dateend
group by goodsid,storeid
delcare cc cursor for
selct goodsid,qty from @table1
open cc
while (2>1)
begin
fetch next from cc into @goodsid,@qty
if @@fetch_status<>0 break
declare ccc cursor for
select billid,bd,bc,storeid,qty from instored where goodsid=@goodsid
order by bd asc,storeid asc
open ccc
while (2>1)
begin
fetch next from ccc into @billid,@bd,@bc,@storeid,@qty2
if @qty=0 break
if @@fetch_status<>0
begin
insert into @returntable
select '负批次','','','',@goodsid,@qty
break
end
if(@qty2>=@qty)
begin
insert into @returntable
select @billid,@bd,@bc,@storeid,@goodsid,@qty
set @qty=0
end
else
begin
insert into @returntable
select @billid,@bd,@bc,@storeid,@goodsid,@qty2
set @qty=@qty-@qty2
end
end
deallocate ccc
end
end
return @returntable
insert into tb1 values('001' , '2009-08-01' , '090801001' , 'a仓' , 'g001' , 700 )
insert into tb1 values('002' , '2009-08-20' , '090820001' , 'a仓' , 'g001' , 800 )
insert into tb1 values('003' , '2009-08-23' , '090823001' , 'b仓' , 'g001' , 600 )
insert into tb1 values('009' , '2009-08-20' , '090820002' , 'a仓' , 'g001' , 300 )
insert into tb1 values('004' , '2009-08-23' , '090823002' , 'b仓' , 'k002' , 700 )
insert into tb1 values('005' , '2009-08-23' , '090823003' , 'b仓' , 'k002' , 2000)
insert into tb1 values('006' , '2009-09-26' , '090926001' , 'a仓' , 'g001' , 600 )
insert into tb1 values('007' , '2009-09-27' , '090927001' , 'b仓' , 'k002' , 400 )
create table tb2(billid varchar(10), bd datetime, bc varchar(10), storeid varchar(10), goodsid varchar(10), qty int)
insert into tb2 values('801' , '2009-08-20' , '090820901' , 'a仓' , 'g001' , 700 )
insert into tb2 values('802' , '2009-08-20' , '090820902' , 'a仓' , 'g001' , 900 )
insert into tb2 values('804' , '2009-08-28' , '090828001' , 'b仓' , 'k002' , 800 )
insert into tb2 values('806' , '2009-09-26' , '090926901' , 'a仓' , 'g001' , 600 )
insert into tb2 values('807' , '2009-09-28' , '090928008' , 'b仓' , 'k002' , 1000)
godeclare @start datetime
,@end datetime
select @start=''2009-08-01',@end=''2009-08-31'select
max(tb1.billid) billid
,max(tb.bd) bd
,bc
,storeid
,goodsid
,(sum(tb1.qty)-sum(tb2.qty)) qty
from tb1 left join tb2
on tb1.bc = tb2.bc
and tb1.storeid = tb2.storeid
and tb1.goodsid = tb2.goodsid
and t2.bd >= @start
and t2.bd <= @end
where
t1.bd >= @start
and t1.bd <= @end
group by
tb1.bc,tb1.storeid,tb1.goodsid没有测试。
create table tb1(billid varchar(10), bd datetime, bc varchar(10), storeid varchar(10), goodsid varchar(10), qty int)
insert into tb1 values('001' , '2009-08-01' , '090801001' , 'a仓' , 'g001' , 700 )
insert into tb1 values('002' , '2009-08-20' , '090820001' , 'a仓' , 'g001' , 800 )
insert into tb1 values('003' , '2009-08-23' , '090823001' , 'b仓' , 'g001' , 600 )
insert into tb1 values('009' , '2009-08-20' , '090820002' , 'a仓' , 'g001' , 300 )
insert into tb1 values('004' , '2009-08-23' , '090823002' , 'b仓' , 'k002' , 700 )
insert into tb1 values('005' , '2009-08-23' , '090823003' , 'b仓' , 'k002' , 2000)
insert into tb1 values('006' , '2009-09-26' , '090926001' , 'a仓' , 'g001' , 600 )
insert into tb1 values('007' , '2009-09-27' , '090927001' , 'b仓' , 'k002' , 400 )
create table tb2(billid varchar(10), bd datetime, bc varchar(10), storeid varchar(10), goodsid varchar(10), qty int)
insert into tb2 values('801' , '2009-08-20' , '090820901' , 'a仓' , 'g001' , 700 )
insert into tb2 values('802' , '2009-08-20' , '090820902' , 'a仓' , 'g001' , 900 )
insert into tb2 values('804' , '2009-08-28' , '090828001' , 'b仓' , 'k002' , 800 )
insert into tb2 values('806' , '2009-09-26' , '090926901' , 'a仓' , 'g001' , 600 )
insert into tb2 values('807' , '2009-09-28' , '090928008' , 'b仓' , 'k002' , 1000)
go declare @start datetime
,@end datetime
select @start=''2009-08-01',@end=''2009-08-31' select
max(tb1.billid) billid
,max(tb1.bd) bd
,tb1.bc
,tb1.storeid
,tb1.goodsid
,(sum(tb1.qty)-sum(tb2.qty)) qty
from tb1 left join tb2
on tb1.bc = tb2.bc
and tb1.storeid = tb2.storeid
and tb1.goodsid = tb2.goodsid
and t2.bd >= @start
and t2.bd <= @end
where
t1.bd >= @start
and t1.bd <= @end
group by
tb1.bc,tb1.storeid,tb1.goodsid
没有测试。
下面参考2楼的写法
create table tb1(billid varchar(10), bd datetime, bc varchar(10), storeid varchar(10), goodsid varchar(10), qty int)
insert into tb1 values('001' , '2009-08-01' , '090801001' , 'a仓' , 'g001' , 700 )
insert into tb1 values('002' , '2009-08-20' , '090820001' , 'a仓' , 'g001' , 800 )
insert into tb1 values('003' , '2009-08-23' , '090823001' , 'b仓' , 'g001' , 600 )
insert into tb1 values('009' , '2009-08-20' , '090820002' , 'a仓' , 'g001' , 300 )
insert into tb1 values('004' , '2009-08-23' , '090823002' , 'b仓' , 'k002' , 700 )
insert into tb1 values('005' , '2009-08-23' , '090823003' , 'b仓' , 'k002' , 2000)
insert into tb1 values('006' , '2009-09-26' , '090926001' , 'a仓' , 'g001' , 600 )
insert into tb1 values('007' , '2009-09-27' , '090927001' , 'b仓' , 'k002' , 400 )
create table tb2(billid varchar(10), bd datetime, bc varchar(10), storeid varchar(10), goodsid varchar(10), qty int)
insert into tb2 values('801' , '2009-08-20' , '090820901' , 'a仓' , 'g001' , 700 )
insert into tb2 values('802' , '2009-08-20' , '090820902' , 'a仓' , 'g001' , 900 )
insert into tb2 values('804' , '2009-08-28' , '090828001' , 'b仓' , 'k002' , 800 )
insert into tb2 values('806' , '2009-09-26' , '090926901' , 'a仓' , 'g001' , 600 )
insert into tb2 values('807' , '2009-09-28' , '090928008' , 'b仓' , 'k002' , 1000)
goCREATE PROCEDURE my_proc @dt1 as datetime , @dt2 as datetime
AS
select m.billid , m.bd , m.bc , m.storeid , m.goodsid ,(m.qty - isnull(n.qty , 0)) qty
from
(select
max(billid) billid --如果billid与bd都是升序或者降序排列的话,这里可以这样写,否则得换其他写法。
,max(bd) bd , bc , storeid , goodsid , sum(qty) qty from tb1 where bd between @dt1 and @dt2 group by bc,storeid,goodsid ) m
left join
(select bc , storeid , goodsid , sum(qty) qty from tb2 where bd between @dt1 and @dt2 group by bc,storeid,goodsid ) n
on m.bc = n.bc and m.storeid = n.storeid and m.goodsid = n.goodsid
goexec my_proc '2009-08-01','2009-08-31'
exec my_proc '2009-08-01','2009-09-30'
没有测试
declare @start datetime
,@end datetime
select @start=''2009-08-01',@end=''2009-08-31' select
max(tb1.billid) billid
,max(tb1.bd) bd
,tb1.bc
,tb1.storeid
,tb1.goodsid
,(sum(tb1.qty)-sum(tb2.qty)) qty
from tb1 left join tb2
on tb1.bc = tb2.bc ----------有点疑问,进仓单和出仓单的单号是不相同的呀!!
and tb1.storeid = tb2.storeid
and tb1.goodsid = tb2.goodsid
and t2.bd >= @start
and t2.bd <= @end
where
t1.bd >= @start
and t1.bd <= @end
group by
tb1.bc,tb1.storeid,tb1.goodsid
按照先进先出的计算结果应该只会是instored进仓单的数据记录,根据出仓单的记录在进仓单的原始记录里扣减,不需要根据日期,仓库,料号进行合计的.SELECT * FROM 函数('2009-08-01','2009-08-31')时
billid bd bc storeid goodsid qty
009 2009-08-20 090820002 a仓 g001 200
003 2009-08-23 090823001 b仓 g001 600
005 2009-08-23 090823003 b仓 k002 2700 --这条2700不对
005 2009-08-28 090823003 b仓 k002 1900 SELECT * FROM 函数('2009-08-01','2009-08-31')时
billid bd bc storeid goodsid qty
009 2009-08-20 090820002 a仓 g001 200
003 2009-08-23 090823001 b仓 g001 600
005 2009-08-28 090823003 b仓 k002 1900 --这条记录的日期不对