TONY兄请进，高难度求某进仓单结存情况！！

sankyqiu 2009-10-09 09:49:10

先进先出法计算进仓单结存情况

按先进先出法计算某入仓单的结余情况，处理思路，先用出仓单冲减进仓单数量，如果某进仓单的数量小于出仓单，则再取下一张进仓单继续冲减，如此类推.
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 2000

006 2009-09-26 090926001 a仓 g001 600
007 2009-09-27 090927001 b仓 k002 400

outstored出仓单
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 800

806 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

...全文

497 53 打赏收藏转发到动态举报

写回复

用AI写文章

53 条回复

切换为时间正序

请发表友善的回复…

发表回复

luoyoumou 2009-10-14

打赏
举报

--你按照4楼的改一下就行了！
--实在不行，我帮你来改吧！

sankyqiu 2009-10-14

打赏
举报

能不能改为函数进行查询？因为我的软件的接口可以直接调用函数并且可进行条件过滤的。

luoyoumou 2009-10-14

打赏
举报

---嗯：呵呵，速度怎么样？

---原表有多少记录行？

---我觉得写成函数与写成存储过程，没什么区别吧！

sankyqiu 2009-10-14

打赏
举报

测试了一下，数据对了！

luoyoumou 2009-10-14

打赏
举报

用存储过程不一样吗？

数据对吗？

sankyqiu 2009-10-14

打赏
举报

我再试试，应该没什么问题的了。、
还想请教个问题这个存储过程能否改成函数进行查询？？

luoyoumou 2009-10-14

打赏
举报

--我是分仓库核算的，不知道你的期初(起始时间段之前的库存)算不算在库存之内？
--这样的话：是肯定不对的！

luoyoumou 2009-10-14

打赏
举报

--等等：我现在上班有事，等闲着的时候再好好看看，
你把你那两张表(instored、outstored)的数据用Excel发到我邮箱：monkeyluo1202@163.com

sankyqiu 2009-10-14

打赏
举报

要分仓核算的，就是说可以某个仓库A ，里面有货品 G001
也可能某个仓库B ，里面有货品 G001，
货品G001在仓库里面都有出、入库的，要按仓库分组计算出每个仓库每个货品的结存单号

zhanghelpsgz 2009-10-14

打赏
举报

学习

sankyqiu 2009-10-14

打赏
举报

exec inout_proc '2009-08-01','2009-09-30'
单号仓库货品结存
SI0004 4 186 23989
5695 4 186 12660
C0000042 4 186 43

结存数量对不上！

sankyqiu 2009-10-14

打赏
举报

以下是我数据库里面的数据结存应该是11,673.80
日期单据类型单号收入数量发出数量结存数量
2009-8-31 期初 SI0004 11,480 11,480
2009.09.01 领料单 B0007314 680 10,800
2009.09.01 领料单 B0007353 165 10,635
2009.09.03 领料单 B0007382 500 10,135
2009.09.03 领料单 B0007392 368.6 9,766.40
2009.09.04 领料单 B0007419 495 9,271.40
2009.09.04 其他发货单B0007426 10 9,261.40
2009.09.04 领料单 B0007420 165 9,096.40
2009.09.07 领料单 B0007482 170 8,926.40
2009.09.08 领料单 B0007509 1,121 7,805.40
2009.09.08 领料单 B0007526 80 7,725.40
2009.09.09 领料单 B0007530 319 7,406.40
2009.09.09 领料单 B0007529 160.4 7,246
2009.09.10 领料单 B0007546 30 7,216
2009.09.10 领料单 B0007567 165 7,051
2009.09.11 领料单 B0007588 30 7,021
2009.09.11 领料单 B0007584 394 6,627
2009.09.11 领料单 B0007600 165 6,462
2009.09.11 领料单 B0007608 16 6,446
2009.09.12 领料单 B0007612 43 6,403
2009.09.12 领料单 B0007627 165 6,238
2009.09.14 领料单 B0007637 330.4 5,907.60
2009.09.15 领料单 B0007691 335.4 5,572.20
2009.09.15 领料单 B0007692 165.2 5,407
2009.09.16 领料单 B0007714 528 4,879
2009.09.16 领料单 B0007711 330.4 4,548.60
2009.09.16 领料单 B0007728 166 4,382.60
2009.09.16 领料单 B0007717 500.2 3,882.40
2009.09.17 领料单 B0007734 495 3,387.40
2009.09.17 领料单 B0007741 369 3,018.40
2009.09.19 领料单 B0007806 165 2,853.40
2009.09.21 领料单 B0007840 165 2,688.40
2009.09.21 领料单 B0007833 257 2,431.40
2009.09.21 领料单 B0007836 251 2,180.40
2009.09.22 领料单 B0007855 495.2 1,685.20
2009.09.23 领料单 B0007887 695 990.2
2009.09.24 领料单 B0007916 165 825.2
2009.09.24 领料单 B0007912 495.2 330
2009.09.24 其他发货单B0007934 10 320
2009.09.24 领料单 B0007932 165 155
2009.09.25 采购发票 0005695 12,660 12,815
2009.09.26 领料单 B0007977 96 12,719
2009.09.28 领料单 B0007990 98 12,621
2009.09.28 领料单 B0007987 330 12,291
2009.09.28 领料单 B0008002 165.2 12,125.80
2009.09.29 领料单 B0008020 495 11,630.80
2009.09.30 退料单 C0000042 -43 11,673.80
期末合计 24,140 12,466.20 11,673.80

sankyqiu 2009-10-14

打赏
举报

[Quote=引用 35 楼 luoyoumou 的回复:]
--OK，你按照我33楼的去修改一下，测试一下，还不行的话，明天再说！
[/Quote]
测试了，可以运行，没提示出错！

sankyqiu 2009-10-14

打赏
举报

我还要更加努力呀，道路还很远很远。
谢谢josy兄的支持，经细心测试josy兄的代码也是OK的。

谢谢luoyoumou兄的帮忙，你做的代码运行速度还可以的，一个月全部数据10秒内可以出来了。

先结贴呀！

luoyoumou 2009-10-14

打赏
举报

--呵呵：其实无所谓“高手”，学会独立思考是关键！

--建议有时间好好把我这些代码搞懂，你会有收获的！

--我相信：楼主一定能编写出比我更优秀的代码来的！

--不过：我的代码，好多人看了头都是大的，

--呵呵..................

sankyqiu 2009-10-14

打赏
举报

高手呀！

luoyoumou 2009-10-14

打赏
举报

-------------------------------------------------------

-----       进出仓结存----修正版(函数版)   ------------ 

----------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

luoyoumou 2009-10-14

打赏
举报

-------------------------------------------------------

-----       进出仓结存----修正版(函数版)         ------------ 

----------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 --指定时间段的出库总数量

);



/*

create table #temp2( --保存剩余库存

id int,

billid varchar(4),

bd datetime,

bc varchar(10),

storeid varchar(4),

goodsid varchar(5),  

qty 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