27,579
社区成员
发帖
与我相关
我的任务
分享
--如函数dbo.udf_GetFreeStock(a.XX)一定要用
--1.优化函数代码
--2.拆分SQL执行,先提取需要的记录,再调用函数.将调用函数次数降到最低.
select * into #t
from from table1 where ...
select dbo.udf_GetFreeStock(a.XX) from #t
CREATE FUNCTION [dbo].[udf_Storage_GetFreeStockExt2]
(
@GID varchar(50),
@RequireDate datetime = NULL,
@IncludeSafetyStockInFreeStock int = 0
)
RETURNS decimal(18,4)
BEGIN
declare @QtyStock decimal(18,4),
@QtySafe decimal(18,4),
@QtyFreeStock decimal(18,4),
@QtyEnd1 decimal(18,4),
@QtyEnd2 decimal(18,4),
@QtyMin decimal(18,4)
if @RequireDate is null or @RequireDate = null
begin
select @RequireDate = getdate()
end
if @IncludeSafetyStockInFreeStock <> 0
begin
select @IncludeSafetyStockInFreeStock = 1
end
--取当前库存数量
select @QtyStock = dbo.udf_GetUsableKCAccount(@GID)
--取安全库存数量
select @QtySafe = isnull(QtySafe,0)
from Storage_Goods
where GID = @GID
--取指定日期的供需结存数量
select top 1 @QtyEnd1 = isnull(QtyEnd,0)
from Storage_SupplyRequest
where GID = @GID
and convert(datetime,convert(varchar(10),DateSr,121)) <= convert(datetime,convert(varchar(10),@RequireDate,121))
order by convert(datetime,convert(varchar(10),DateSr,121)) desc
if @QtyEnd1 is null or @QtyEnd1 = null
begin
select @QtyEnd1 = 0
end
select @QtyMin = @QtyEnd1
--取指定日期后物料供需计划中最小的结存数
if exists(select * from Storage_SupplyRequest
where GID = @GID
and convert(datetime,convert(varchar(10),DateSr,121)) > convert(datetime,convert(varchar(10),@RequireDate,121))
and (QtySupply > QtySupplyFinish
or QtyRequest > QtyRequestFinish))
begin
select @QtyEnd2 = isnull(min(QtyEnd),0)
from Storage_SupplyRequest
where GID = @GID
and convert(datetime,convert(varchar(10),DateSr,121)) > convert(datetime,convert(varchar(10),@RequireDate,121))
and (QtySupply > QtySupplyFinish
or QtyRequest > QtyRequestFinish)
if @QtyEnd2 is null or @QtyEnd2 = null
begin
select @QtyEnd2 = 0
end
--取最小值
if @QtyEnd2 < @QtyMin
begin
select @QtyMin = @QtyEnd2
end
end
--计算富余库存数量
select @QtyFreeStock = @QtyStock - (case when @IncludeSafetyStockInFreeStock = 0 then 0 else @QtySafe end) + @QtyMin
return isnull(@QtyFreeStock,0)
END
create procedure [dbo].[usp_Storage_SupplyRequest_Diff]
(
@IsSafeStock bit=1, --是否考虑安全库存
@IsLessZero bit=1 --是否只查看缺料
)
as
begin
set nocount on
create table #Temp_QtyStorage
(
GID varchar(50) not null,
DateSr datetime not null,
QtySupply decimal(18,4) not null default(0), --供应数量
QtyRequest decimal(18,4) not null default(0), --需求数量
QtyStock decimal(18,4) not null default(0), --现有库存数
QtySafeStock decimal(18,4) not null default(0), --安全库存数
QtyBegin decimal(18,4) not null default(0), --起始数量
QtyDiff decimal(18,4) not null default(0), --差料数量
QtyFreeStock decimal(18,4) not null default(0) --富余库存数量
)
if @IsSafeStock=1
begin
--将现有库存数、安全库存数、差料数装载到临时表#Temp_QtyStorage中
insert into #Temp_QtyStorage(GID,DateSr,QtySupply,QtyRequest,QtyStock,QtySafeStock,QtyBegin,QtyDiff,QtyFreeStock)
select A.GID,
A.DateSr,
A.QtySupply-A.QtySupplyFinish,
A.QtyRequest-A.QtyRequestFinish,
isnull(B.QtyStock,0) as QtyStock,
isnull(C.QtySafe,0) as QtySafeStock,
(A.QtyBegin+isnull(B.QtyStock,0)-isnull(C.QtySafe,0)) as QtyBegin,
(A.QtyEnd+isnull(B.QtyStock,0)-isnull(C.QtySafe,0)) as QtyDiff,
isnull(dbo.udf_Storage_GetFreeStockExt2(A.GID,A.DateSr,@IsSafeStock),0) as QtyFreeStock
from Storage_SupplyRequest A
left join (select GID,isnull(Sum(Number),0) as QtyStock
from Storage_KCAccount
where GState in ('9002','9004') group by GID) B on A.GID=B.GID
left join Storage_Goods C on A.GID=C.GID
end
else
begin
--将现有库存数、安全库存数(因为不考虑安全库存,所以该值为0)、差料数装载到临时表#Temp_QtyStorage中
insert into #Temp_QtyStorage(GID,DateSr,QtySupply,QtyRequest,QtyStock,QtySafeStock,QtyBegin,QtyDiff,QtyFreeStock)
select A.GID,
A.DateSr,
A.QtySupply-A.QtySupplyFinish,
A.QtyRequest-A.QtyRequestFinish,
isnull(B.QtyStock,0) as QtyStock,
0,
(A.QtyBegin+isnull(B.QtyStock,0)) as QtyBegin,
(A.QtyEnd+isnull(B.QtyStock,0)) as QtyDiff,
isnull(dbo.udf_Storage_GetFreeStockExt2(A.GID,A.DateSr,@IsSafeStock),0) as QtyFreeStock
from Storage_SupplyRequest A
left join (select GID,isnull(Sum(Number),0) as QtyStock
from Storage_KCAccount
where GState in ('9002','9004') group by GID) B on A.GID=B.GID
end
--计算最早补数时间并返回
if @IsLessZero=1
begin
select row_number() over(order by t1.GID) as ID, t1.GID,t1.DateSr,t1.QtySupply,t1.QtyRequest,t1.QtyStock,t1.QtySafeStock,t1.QtyBegin,
t1.QtyDiff,
t2.Name,t2.Model,t2.Unit,
DateSupply=case when t1.QtyDiff<0 then (select Min(t2.DateSr)
from #Temp_QtyStorage t2
where t1.GID=t2.GID and t2.QtyDiff>=0 and datediff(s,t1.DateSr,t2.DateSr)>0) else t1.DateSr end,t1.QtyFreeStock
from #Temp_QtyStorage t1 left join Storage_Goods t2 on t1.GID=t2.GID
where QtyDiff<0 order by DateSr ASC
end
else
begin
select row_number() over(order by t1.GID) as ID, t1.GID,t1.DateSr,t1.QtySupply,t1.QtyRequest,t1.QtyStock,t1.QtySafeStock,t1.QtyBegin,
t1.QtyDiff,
t2.Name,t2.Model,t2.Unit,
DateSupply=case when t1.QtyDiff<0 then (select Min(t2.DateSr)
from #Temp_QtyStorage t2
where t1.GID=t2.GID and t2.QtyDiff>=0 and datediff(s,t1.DateSr,t2.DateSr)>0) else t1.DateSr end,t1.QtyFreeStock
from #Temp_QtyStorage t1 left join Storage_Goods t2 on t1.GID=t2.GID order by DateSr ASC
end
drop table #Temp_QtyStorage
set nocount off
end