先进先出法计算进仓单结存情况按先进先出法计算某入仓单的结余情况,处理思路,先用出仓单冲减进仓单数量,如果某进仓单的数量小于出仓单,则再取下一张进仓单继续冲减,如此类推.
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
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
go
create table [instored]([billid] varchar(3),[bd] datetime,[bc] varchar(9),[storeid] varchar(3),[goodsid] varchar(4),[qty] int)
insert [instored]
select '001','2009-08-01','090801001','a仓','g001',700 union all
select '002','2009-08-20','090820001','a仓','g001',800 union all
select '003','2009-08-23','090823001','b仓','g001',600 union all
select '009','2009-08-20','090820002','a仓','g001',300 union all
select '004','2009-08-23','090823002','b仓','k002',700 union all
select '005','2009-08-23','090823003','b仓','k002',2000 union all
select '006','2009-09-26','090926001','a仓','g001',600 union all
select '007','2009-09-27','090927001','b仓','k002',400
if object_id('[outstored]') is not null drop table [outstored]
go
create table [outstored]([billid] int,[bd] datetime,[bc] varchar(9),[storeid] varchar(3),[goodsid] varchar(4),[qty] int)
insert [outstored]
select 801,'2009-08-20','090820901','a仓','g001',700 union all
select 802,'2009-08-20','090820902','a仓','g001',900 union all
select 804,'2009-08-28','090828001','b仓','k002',800 union all
select 806,'2009-09-26','090926901','a仓','g001',600 union all
select 807,'2009-09-28','090928008','b仓','k002',1000
go-->创建表值函数
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
select
billid,convert(varchar(10),bd,120),bc,storeid,goodsid,
case when iqty>qty then qty else iqty end as qty
from(
select
a.billid,a.bd,a.bc,a.storeid,a.goodsid,a.qty as iqty,
(select sum(qty)
from (select
*,
px=(select count(1)
from instored
where storeid=i.storeid and goodsid=i.goodsid and (bd<i.bd or bd=i.bd and billid<i.billid) and convert(varchar(10),bd,120) between @start and @end)
from instored i where convert(varchar(10),bd,120) between @start and @end)t
where storeid=a.storeid and goodsid=a.goodsid and px<=a.px
)-isnull(b.qty,0) as qtyfrom
(select
*,
px=(select count(1)
from instored
where storeid=i.storeid and goodsid=i.goodsid and (bd<i.bd or bd=i.bd and billid<i.billid) and convert(varchar(10),bd,120) between @start and @end)
from instored i where convert(varchar(10),bd,120) between @start and @end) a
left join
(select storeid,goodsid,sum(qty) as qty from [outstored] where convert(varchar(10),bd,120) between @start and @end group by storeid,goodsid) b
on
a.storeid=b.storeid and a.goodsid=b.goodsid
) tt
where qty>0
order by
storeid,goodsid
return
end
go-->查询1
SELECT * FROM dbo.f_fifo('2009-08-01','2009-08-31')
/**
billid bd bc storeid goodsid qty
----------- ---------- --------- ------- ------- -----------
9 2009-08-20 090820002 a仓 g001 200
3 2009-08-23 090823001 b仓 g001 600
5 2009-08-23 090823003 b仓 k002 1900(3 行受影响)
**/-->查询2
SELECT * FROM dbo.f_fifo('2009-08-01','2009-09-30')
/**
billid bd bc storeid goodsid qty
----------- ---------- --------- ------- ------- -----------
6 2009-09-26 090926001 a仓 g001 200
3 2009-08-23 090823001 b仓 g001 600
5 2009-08-23 090823003 b仓 k002 900
7 2009-09-27 090927001 b仓 k002 400(4 行受影响)**/
我是这样校检的:
某货品在某仓库的总的结存量 与 上面函数结存单据某仓库某货品明细合计数据
发现都对不上的。(某货品在某仓库的总的结存量<> 上面函数结存单据某仓库某货品明细合计数据 )
上次的已经给分了,希望大家更努力帮个大忙。多谢josy兄的支持。不知道为什么在我实际的数据库里面的资料算出来是不准确的。
我是这样校检的:
某货品在某仓库的总的结存量 与 上面函数结存单据某仓库某货品明细合计数据
发现都对不上的。(某货品在某仓库的总的结存量 <> 上面函数结存单据某仓库某货品明细合计数据 )
多谢josy兄的支持。不知道为什么在我 实际的数据库 里面的资料算出来是不准确的。
我是这样校检的:
某货品在某仓库的总的结存量 与 上面函数结存单据某仓库某货品明细合计数据
发现都对不上的。(某货品在某仓库的总的结存量 <> 上面函数结存单据某仓库某货品明细合计数据 )
---------------------------------------------------------------------
create table instored(
billid varchar(4),
bd datetime,
bc varchar(10),
storeid varchar(4),
goodsid varchar(5),
qty int);create table outstored(
billid varchar(4),
bd datetime,
bc varchar(10),
storeid varchar(4),
goodsid varchar(5),
qty int);insert into instored(billid, bd, bc, storeid, goodsid, qty)
select
'001','2009-08-01','090801001','a仓','g001',700 union all select
'002','2009-08-20','090820001','a仓','g001',800 union all select
'003','2009-08-23','090823001','b仓','g001',600 union all select
'009','2009-08-20','090820002','a仓','g001',300 union all select
'004','2009-08-23','090823002','b仓','k002',700 union all select
'005','2009-08-23','090823003','b仓','k002',2000 union all select
'006','2009-09-26','090926001','a仓','g001',600 union all select
'007','2009-09-27','090927001','b仓','k002',400;insert into outstored(billid, bd, bc, storeid, goodsid, qty)
select
'801','2009-08-20','090820901','a仓','g001',700 union all select
'802','2009-08-20','090820902','a仓','g001',900 union all select
'804','2009-08-28','090828001','b仓','k002',800 union all select
'806','2009-09-26','090926901','a仓','g001',600 union all select
'807','2009-09-28','090928008','b仓','k002',1000;
-----------------------------------------------------------------------------alter procedure inout_proc @fromdate datetime, @todate datetime
/*
exec inout_proc '2009-08-01','2009-08-31'
exec inout_proc '2009-08-01','2009-09-30'
*/
asbegincreate table #temp1(
id int identity(1,1), --自增标识位
billid varchar(4),
bd datetime,
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 #temp1(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.bd;--特定仓库,特定货品无出库记录的指定时间段内的入库记录直接插入
insert into #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
select Id, billid, bd, bc, storeid, goodsid, qty
from #temp1 where sum_out=0;----------------------------------------------
declare @id int
declare @billid varchar(4)
declare @bd datetime
declare @bc varchar(10)
declare @storeid varchar(4), @storeid2 varchar(4)
declare @goodsid varchar(5)
declare @qty int, @in_sumQty int, @sum_out int
declare @flag intset @storeid2=''--游标检索
DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
SELECT Id, billid, bd, bc, storeid, goodsid, qty, sum_out from #temp1
where sum_out<>0 order by Id
OPEN @MyData
FETCH NEXT FROM @MyData INTO @Id, @billid, @bd, @bc, @storeid, @goodsid, @qty, @sum_out
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@storeid2<>@storeid)
BEGIN
SET @storeid2=@storeid --如果仓库编码发生变化
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 #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
VALUES(@Id, @billid, @bd, @bc, @storeid, @goodsid, @in_sumQty-@sum_out);
SET @flag=1;
END
ELSE -----表示第二次或以上查找到符合条件的记录行(整条记录行都当作剩余的库存)
BEGIN
INSERT INTO #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
VALUES(@Id, @billid, @bd, @bc, @storeid, @goodsid, @qty);
END
END
FETCH NEXT FROM @MyData INTO @Id, @billid, @bd, @bc, @storeid, @goodsid, @qty, @sum_out
END
CLOSE @MyData
DEALLOCATE @MyData select billid, bd, bc, storeid, goodsid, qty from #temp2 order by Id;
drop table #temp1, #temp2;end
create table instored(
billid varchar(4),
bd datetime,
bc varchar(10),
storeid varchar(4),
goodsid varchar(5),
qty int);create table outstored(
billid varchar(4),
bd datetime,
bc varchar(10),
storeid varchar(4),
goodsid varchar(5),
qty int);insert into instored(billid, bd, bc, storeid, goodsid, qty)
select
'001','2009-08-01','090801001','a仓','g001',700 union all select
'002','2009-08-20','090820001','a仓','g001',800 union all select
'003','2009-08-23','090823001','b仓','g001',600 union all select
'009','2009-08-20','090820002','a仓','g001',300 union all select
'004','2009-08-23','090823002','b仓','k002',700 union all select
'005','2009-08-23','090823003','b仓','k002',2000 union all select
'006','2009-09-26','090926001','a仓','g001',600 union all select
'007','2009-09-27','090927001','b仓','k002',400;insert into outstored(billid, bd, bc, storeid, goodsid, qty)
select
'801','2009-08-20','090820901','a仓','g001',700 union all select
'802','2009-08-20','090820902','a仓','g001',900 union all select
'804','2009-08-28','090828001','b仓','k002',800 union all select
'806','2009-09-26','090926901','a仓','g001',600 union all select
'807','2009-09-28','090928008','b仓','k002',1000;-------------------------------------------------------
----- 进出仓结存----修正版 ------------
----------Author:Luoyoumou----------------------------alter procedure inout_proc @fromdate datetime, @todate datetime
/*
exec inout_proc '2009-08-01','2009-08-31'
exec inout_proc '2009-08-01','2009-09-30'
*/
asbegincreate table #temp1(
id int identity(1,1), --自增标识位
billid varchar(4),
bd datetime,
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 #temp1(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, goodsid, i.bd;--特定仓库,特定货品无出库记录的指定时间段内的入库记录直接插入
insert into #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
select Id, billid, bd, bc, storeid, goodsid, qty
from #temp1 where sum_out=0;-----------定义变量-------------------------
declare @id int
declare @billid varchar(4)
declare @bd datetime
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 intset @storeid2=''
set @goodsid2=''--游标检索
DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
SELECT Id, billid, bd, bc, storeid, goodsid, qty, sum_out from #temp1
where sum_out<>0 order by Id
OPEN @MyData
FETCH NEXT FROM @MyData INTO @Id, @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 #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
VALUES(@Id, @billid, @bd, @bc, @storeid, @goodsid, @in_sumQty-@sum_out);
SET @flag=1;
END
ELSE -----表示第二次或以上查找到符合条件的记录行(整条记录行都当作剩余的库存)
BEGIN
INSERT INTO #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
VALUES(@Id, @billid, @bd, @bc, @storeid, @goodsid, @qty);
END
END
FETCH NEXT FROM @MyData INTO @Id, @billid, @bd, @bc, @storeid, @goodsid, @qty, @sum_out
END
CLOSE @MyData
DEALLOCATE @MyData select billid, bd, bc, storeid, goodsid, qty from #temp2 order by Id;
drop table #temp1, #temp2;end
运行:exec inout_proc '2009-09-01','2009-09-30'提示如下:
服务器: 消息 209,级别 16,状态 1,过程 inout_proc,行 31
列名 'goodsid' 不明确。
billid varchar(4),
bd datetime,
bc varchar(10),
storeid varchar(4),
goodsid varchar(5),
qty int);create table outstored(
billid varchar(4),
bd datetime,
bc varchar(10),
storeid varchar(4),
goodsid varchar(5),
qty int);insert into instored(billid, bd, bc, storeid, goodsid, qty)
select
'001','2009-08-01','090801001','a仓','g001',700 union all select
'002','2009-08-20','090820001','a仓','g001',800 union all select
'003','2009-08-23','090823001','b仓','g001',600 union all select
'009','2009-08-20','090820002','a仓','g001',300 union all select
'004','2009-08-23','090823002','b仓','k002',700 union all select
'005','2009-08-23','090823003','b仓','k002',2000 union all select
'006','2009-09-26','090926001','a仓','g001',600 union all select
'007','2009-09-27','090927001','b仓','k002',400;insert into outstored(billid, bd, bc, storeid, goodsid, qty)
select
'801','2009-08-20','090820901','a仓','g001',700 union all select
'802','2009-08-20','090820902','a仓','g001',900 union all select
'804','2009-08-28','090828001','b仓','k002',800 union all select
'806','2009-09-26','090926901','a仓','g001',600 union all select
'807','2009-09-28','090928008','b仓','k002',1000;-------------------------------------------------------
----- 进出仓结存----修正版 ------------
----------Author:Luoyoumou----------------------------
-------------------------------------------------------alter procedure inout_proc @fromdate datetime, @todate datetime
/*
exec inout_proc '2009-08-01','2009-08-31'
exec inout_proc '2009-08-01','2009-09-30'
*/
asbegincreate table #temp1(
id int identity(1,1), --自增标识位
billid varchar(4),
bd datetime,
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 #temp1(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, goodsid, i.bd;--特定仓库,特定货品无出库记录的指定时间段内的入库记录直接插入
insert into #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
select Id, billid, bd, bc, storeid, goodsid, qty
from #temp1 where sum_out=0;
-----------定义变量-------------------------
declare @id int
declare @billid varchar(4)
declare @bd datetime
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 intset @storeid2=''
set @goodsid2=''--游标检索
DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
SELECT Id, billid, bd, bc, storeid, goodsid, qty, sum_out from #temp1
where sum_out<>0 order by Id
OPEN @MyData
FETCH NEXT FROM @MyData INTO @Id, @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 #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
VALUES(@Id, @billid, @bd, @bc, @storeid, @goodsid, @in_sumQty-@sum_out);
SET @flag=1;
END
ELSE -----表示第二次或以上查找到符合条件的记录行(整条记录行都当作剩余的库存)
BEGIN
INSERT INTO #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
VALUES(@Id, @billid, @bd, @bc, @storeid, @goodsid, @qty);
END
END
FETCH NEXT FROM @MyData INTO @Id, @billid, @bd, @bc, @storeid, @goodsid, @qty, @sum_out
END
CLOSE @MyData
DEALLOCATE @MyData select billid, bd, bc, storeid, goodsid, qty from #temp2 order by Id;
drop table #temp1, #temp2;end
billid varchar(4),
bd datetime,
bc varchar(10),
storeid varchar(4),
goodsid varchar(5),
qty int);create table outstored(
billid varchar(4),
bd datetime,
bc varchar(10),
storeid varchar(4),
goodsid varchar(5),
qty int);insert into instored(billid, bd, bc, storeid, goodsid, qty)
select
'001','2009-08-01','090801001','a仓','g001',700 union all select
'002','2009-08-20','090820001','a仓','g001',800 union all select
'003','2009-08-23','090823001','b仓','g001',600 union all select
'009','2009-08-20','090820002','a仓','g001',300 union all select
'004','2009-08-23','090823002','b仓','k002',700 union all select
'005','2009-08-23','090823003','b仓','k002',2000 union all select
'006','2009-09-26','090926001','a仓','g001',600 union all select
'007','2009-09-27','090927001','b仓','k002',400;insert into outstored(billid, bd, bc, storeid, goodsid, qty)
select
'801','2009-08-20','090820901','a仓','g001',700 union all select
'802','2009-08-20','090820902','a仓','g001',900 union all select
'804','2009-08-28','090828001','b仓','k002',800 union all select
'806','2009-09-26','090926901','a仓','g001',600 union all select
'807','2009-09-28','090928008','b仓','k002',1000;-------------------------------------------------------
----- 进出仓结存----修正版 ------------
----------Author:Luoyoumou----------------------------
-------------------------------------------------------alter procedure inout_proc @fromdate datetime, @todate datetime
/*
exec inout_proc '2009-08-01','2009-08-31'
exec inout_proc '2009-08-01','2009-09-30'
*/
asbegincreate table #temp1(
id int identity(1,1), --自增标识位
billid varchar(4),
bd datetime,
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 #temp1(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, goodsid, i.bd;--特定仓库,特定货品无出库记录的指定时间段内的入库记录直接插入
insert into #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
select Id, billid, bd, bc, storeid, goodsid, qty
from #temp1 where sum_out=0;
-----------定义变量-------------------------
declare @id int
declare @billid varchar(4)
declare @bd datetime
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 intset @storeid2=''
set @goodsid2=''--游标检索
DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
SELECT Id, billid, bd, bc, storeid, goodsid, qty, sum_out from #temp1
where sum_out<>0 order by Id
OPEN @MyData
FETCH NEXT FROM @MyData INTO @Id, @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 #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
VALUES(@Id, @billid, @bd, @bc, @storeid, @goodsid, @in_sumQty-@sum_out);
SET @flag=1;
END
ELSE -----表示第二次或以上查找到符合条件的记录行(整条记录行都当作剩余的库存)
BEGIN
INSERT INTO #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
VALUES(@Id, @billid, @bd, @bc, @storeid, @goodsid, @qty);
END
END
FETCH NEXT FROM @MyData INTO @Id, @billid, @bd, @bc, @storeid, @goodsid, @qty, @sum_out
END
CLOSE @MyData
DEALLOCATE @MyData select billid, bd, bc, storeid, goodsid, qty from #temp2 order by Id;
drop table #temp1, #temp2;end
-------------------------------------------------------
----- 进出仓结存----修正版 ------------
----------Author:Luoyoumou----------------------------
-------------------------------------------------------alter procedure inout_proc @fromdate datetime, @todate datetime
/*
exec inout_proc '2009-08-01','2009-08-31'
exec inout_proc '2009-08-01','2009-09-30'
*/
asbegincreate table #temp1(
id int identity(1,1), --自增标识位
billid varchar(4),
bd datetime,
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 #temp1(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, goodsid, i.bd;--特定仓库,特定货品无出库记录的指定时间段内的入库记录直接插入
insert into #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
select Id, billid, bd, bc, storeid, goodsid, qty
from #temp1 where sum_out=0;
-----------定义变量-------------------------
declare @id int
declare @billid varchar(4)
declare @bd datetime
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 intset @storeid2=''
set @goodsid2=''--游标检索
DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
SELECT Id, billid, bd, bc, storeid, goodsid, qty, sum_out from #temp1
where sum_out<>0 order by Id
OPEN @MyData
FETCH NEXT FROM @MyData INTO @Id, @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 #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
VALUES(@Id, @billid, @bd, @bc, @storeid, @goodsid, @in_sumQty-@sum_out);
SET @flag=1;
END
ELSE -----表示第二次或以上查找到符合条件的记录行(整条记录行都当作剩余的库存)
BEGIN
INSERT INTO #temp2(Id, billid, bd, bc, storeid, goodsid, qty)
VALUES(@Id, @billid, @bd, @bc, @storeid, @goodsid, @qty);
END
END
FETCH NEXT FROM @MyData INTO @Id, @billid, @bd, @bc, @storeid, @goodsid, @qty, @sum_out
END
CLOSE @MyData
DEALLOCATE @MyData select billid, bd, bc, storeid, goodsid, qty from #temp2 order by Id;
drop table #temp1, #temp2;end
order by i.storeid, goodsid, i.bd;
把goodsid 改成:i.goodsid,再试试
日期 单据类型 单号 收入数量 发出数量 结存数量
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
单号 仓库 货品 结存
SI0004 4 186 23989
5695 4 186 12660
C0000042 4 186 43结存数量对不上!
也可能某个仓库B , 里面有货品 G001,
货品G001在仓库里面都有出、入库的, 要按仓库分组计算出每个仓库每个货品的结存单号
你把你那两张表(instored、outstored)的数据用Excel发到我邮箱:[email protected]
----- 进出仓结存----修正版(函数版) ------------
----------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)
asbeginDECLARE @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 intset @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 returnend
----- 进出仓结存----修正版(函数版) ------------
----------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)
asbeginDECLARE @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 intset @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 returnend
谢谢josy兄的支持,经细心测试josy兄的代码也是OK的。谢谢luoyoumou兄的帮忙,你做的代码运行速度还可以的,一个月全部数据10秒内可以出来了。先结贴呀!