领料单的处理

最新推荐文章于 2024-10-24 14:34:01 发布

原创最新推荐文章于 2024-10-24 14:34:01 发布 · 531 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #join #table #null #c

本文介绍了一个使用SQL进行物料净需求计算的过程。通过定义表变量、插入需求数据，并使用复杂的SQL语句来逐步计算出每种物料的净需求量。该过程涉及需求表、库存表等多个数据源，并通过循环和子查询实现了需求量与现有库存之间的匹配。

	declare @ErrCounter int ;
	declare @ErrCounterMax int ;

	set @ErrCounter = 0 ;
	set @ErrCounterMax = 10  ;

-- 定义表变量

-- 临时表
declare @tempTable table( itemId int, batchNo varchar(50), Qty decimal(18,2) );

-- 需要量
declare @requirementTable table( itemId int, Qty decimal(18,2) );

insert into @requirementTable values( 1625, 100 )
insert into @requirementTable values( 1626, 100 )

-- select * from @requirementTable


	while @@rowcount > 0 and @ErrCounter <= @ErrCounterMax
	begin

		set @ErrCounter = @ErrCounter + 1 ;

		-- 临时量
		with a as (
			select sum( Qty ) as sumQty
				, itemId
			from @tempTable
			group by itemId
		)

		-- 净需要量
		, b as (
			select b.Qty - case when  a.sumQty is null then 0 else a.sumQty end as netRequestmentQty
				,a.itemId
			from @requirementTable b left join a on a.itemId = b.ItemId
		)

		-- 库存表
		, c as (
			select fItemId as itemId
				, fBatchNo as batchNo
				, fQty as Qty
			from ICINVENTORY 
			where fItemId = 1625 and FStockId = 1949 and fQty > 0 
		)

		-- 净库存
		, d as (
			select c.itemId, c.batchNo, c. Qty
			from c left join @tempTable e
				on c.itemId = e.itemId 
					and c.batchNo = e.batchNo and e.itemId is null
		)

		
		insert @tempTable select top 1 itemId, batchNo, Qty from d

	end

select * from @tempTable