日期:2014-05-18 浏览次数:20547 次
------------------------------------------------------- ----- 进出仓结存----修正版(函数版) ------------ ----------Author:Luoyoumou---------------------------- ------------------------------------------------------- alter function inout_func(@fromdate datetime, @todate datetime) /* select * from dbo.inout_func( '2009-08-01','2009-08-31' ) select * from dbo.inout_func( '2009-08-01','2009-09-30' ) */ returns @t table([billid] int,[bd] varchar(10),[bc] varchar(10),[storeid] varchar(4),[goodsid] varchar(5),[qty] int) as begin DECLARE @t2 table ( id int identity(1,1), --自增标识位 billid varchar(4), bd varchar(10), bc varchar(10), storeid varchar(4), goodsid varchar(5), qty int, sum_out int --指定时间段的出库总数量 ); insert into @t2(billid, bd, bc, storeid, goodsid, qty, sum_out) select i.billid, i.bd, i.bc, i.storeid, i.goodsid, i.qty, isnull(o.sum_out,0) sum_out from instored i left join ( select storeid, goodsid, sum(qty) as sum_out from outstored where bd >=@fromdate and bd<=@todate group by storeid, goodsid ) o on i.storeid=o.storeid and i.goodsid=o.goodsid where i.bd >=@fromdate and i.bd<=@todate order by i.storeid, i.goodsid, i.bd; --特定仓库,特定货品无出库记录的指定时间段内的入库记录直接插入 insert into @t(billid, bd, bc, storeid, goodsid, qty) select billid, bd, bc, storeid, goodsid, qty from @t2 where sum_out=0; -----------定义变量------------------------- declare @billid varchar(4) declare @bd varchar(10) declare @bc varchar(10) declare @storeid varchar(4), @storeid2 varchar(4) declare @goodsid varchar(5), @goodsid2 varchar(4) declare @qty int, @in_sumQty int, @sum_out int declare @flag int set @storeid2='' set @goodsid2='' --游标检索 DECLARE @MyData CURSOR SET @MyData = CURSOR FOR SELECT billid, bd, bc, storeid, goodsid, qty, sum_out from @t2 where sum_out<>0 order by Id OPEN @MyData FETCH NEXT FROM @MyData INTO @billid, @bd, @bc, @storeid, @goodsid, @qty, @sum_out WHILE @@FETCH_STATUS = 0 BEGIN IF(@storeid2<>@storeid or @goodsid2<>@goodsid) --如果仓库编码或物料编码发生变化(没有细分是哪种情况了) BEGIN SET @storeid2=@storeid --重新初始化仓库编码变量 SET @goodsid2=@goodsid --重新初始化物料编码变量 SET @in_sumQty=0; --重新初始化进库存总数变量 SET @flag=0; --重新初始化标志位 END SET @in_sumQty=@in_sumQty+@qty; IF(@in_sumQty>@sum_out) BEGIN IF(@flag=0) BEGIN ----表示第一次查找到符合条件的将要拆分的记录行 INSERT INTO @t(billid, bd, bc, storeid, goodsid, qty) VALUES(@billid, @bd, @bc, @storeid, @goodsid, @in_sumQty-@sum_out); SET @flag=1; END ELSE -----表示第二次或以上查找到符合条件的记录行(整条记录行都当作剩余的库存) BEGIN INSERT INTO @t(billid, bd, bc, storeid, goodsid, qty) VALUES(@billid, @bd, @bc, @storeid, @goodsid, @qty); END END FETCH NEXT FROM @MyData INTO @billid, @bd, @bc, @storeid, @goodsid, @qty, @sum_out END CLOSE @MyData DEALLOCATE @MyData return end
-->创建表值函数
create function f_fifo(@start varchar(10),@end varchar(10))
returns
@t table([billid] int,[bd] varchar(10),[bc] varchar(9),[storeid] varchar(3),[goodsid] varchar(4),[qty] int)
as
begin
insert @t
sel