扔个砖头:--库存变化异动查询 CREATE PROCEDURE BES_Q_Dpt_StockChg @Chk1 bit,@Chk2 bit,@Chk3 bit,@Chk4 bit, @ItemNo Varchar(50), @CPN varchar(50), @Str_Dt datetime,@End_Dt datetime, @Depot_Nm varchar(255) ASdeclare @cut_date datetime --用于指出提取数据的最大日期(利用结存表) select @Chk1=isnull(@Chk1,0),@Chk2=isnull(@Chk2,0),@Chk3=isnull(@Chk3,0),@Chk4=isnull(@Chk4,0), @Str_Dt=cast(convert(varchar(50),@Str_Dt,111) as datetime), @End_Dt=cast(convert(varchar(50),@End_Dt,111) as datetime),@Depot_Nm=dbo.Bes_SubStr(@Depot_Nm)--从结存表中提取最近一次结存记录,进行推算 If @chk3=1 and Exists(select Cut_Date from StockManage.dbo.Base_MonthlyCut where Cut_Date>=@End_Dt) select @cut_date = min(Cut_Date) from StockManage.dbo.Base_MonthlyCut where Cut_Date>=@End_Dt Else select @cut_date=getdate()--提取变更数据记录 select ItemNo,Chg_Qty,Unit,Src_Depot,To_Depot,Cast(Convert(varchar(50),Chg_Date,111) as Datetime) as Chg_Date, Cher,Chg_Type,Che_Date,cast(0 as numeric(18,4)) as Stock_Qty,a.IsChk,cast(0 as bit) as IsCom,cast(0 as bit) as IsDel into #Chg from StockManage.dbo.V_Dpt_StockChg a where (@Chk1=0 or ItemNo=@itemno) and (@Chk2=0 or Exists(select ItemNo from V_Bas_Item_Bes where CPN=@CPN and ItemNo=a.ItemNo)) and (@Chk3=0 or (Chg_Date>=@Str_Dt and Chg_Date<=@cut_date)) and (@Chk4=0 or (Src_Depot=@Depot_Nm or To_Depot=@Depot_Nm)) --存在接收仓时 添加目标仓动作(动作为双向的) Insert Into #Chg(ItemNo,Chg_Qty,Unit,Src_Depot,To_Depot,Chg_Date,Cher,Chg_Type,IsChk,IsDel) select ItemNo,-Chg_Qty,Unit,To_Depot,Src_Depot,Chg_Date,Cher,Chg_Type,IsChk,1 from #Chg where Isnull(To_Depot,'')<>'' and Isnull(Src_Depot,'')<>''--将最近一次的结存加入表中,便于推算 Insert into #Chg(ItemNo,Chg_Qty,Unit,Src_Depot,Chg_Date,Chg_Type,IsChk,Stock_Qty,IsCom) select a.ItemNo,0,Unit,a.Depot,a.Cut_Date,'结存',1,a.Stock_Qty,1 from StockManage.dbo.Base_MonthlyCut a, (select Min(a.Cut_Date) as Cut_Date,a.ItemNo,a.Depot from StockManage.dbo.Base_MonthlyCut a, #Chg v where a.Cut_Date>=@cut_date and v.ItemNo=a.ItemNo and v.Src_Depot=a.Depot group by a.ItemNo,a.Depot) b where a.ItemNo=b.ItemNo and a.Cut_Date=b.Cut_Date and a.Depot=b.Depot --不存在结存表的 从库存表中提取 Insert into #Chg(ItemNo,Chg_Qty,Unit,Src_Depot,Chg_Date,Chg_Type,IsChk,Stock_Qty,IsCom) select a.ItemNo,0,a.Unit,a.Depot,getdate(),'结存',1,a.Stock_Qty,1 from V_Dpt_Stock_List_Bes a, (select distinct ItemNo,Src_Depot from #Chg) b where a.ItemNo=b.ItemNo and a.Depot=b.Src_Depot and a.ItemNo not in(select ItemNo from #Chg where Chg_Type='结存') --库存表中不存在的置库存为0 Insert into #Chg(ItemNo,Chg_Qty,Unit,Src_Depot,Chg_Date,Chg_Type,IsChk,Stock_Qty,IsCom) select distinct ItemNo,0,Unit,Src_Depot,getdate(),'结存',1,0 as Stock_Qty,1 from #Chg a where not Exists(select ItemNo from #Chg where ItemNo=a.ItemNo and Src_Depot=a.Src_Depot and Chg_Type='结存')--进行单位转换 Update #Chg set Chg_Qty=a.Chg_Qty*dbo.BES_CntUnit(a.ItemNo,a.Unit,b.Unit),Unit=b.Unit from #Chg a, V_Bas_Item_Bes b where a.ItemNo=b.ItemNo and a.Unit<>b.Unit--排序 select Identity(int,1,1) as Add_ID,ItemNo,Chg_Qty,Src_Depot,To_Depot,Chg_Date,Cher, Chg_Type,Che_Date,Stock_Qty,Isnull(IsChk,'') as IsChk,Isnull(IsCom,0) as IsCom, Isnull(IsDel,0) as IsDel,cast(null as datetime) as tem_dt into #AbnData from #Chg order by ItemNo,Src_Depot,Chg_Date desc,Chg_Qty--最小未处理(最大日期)ID对应记录结存 = 最大已处理ID对应记录(上一笔)结存 - 本次发生异动数 (出为负 入为正) while @@rowcount<>0 and Exists(select IsCom from #AbnData where IsCom=0) begin Update #AbnData set Stock_Qty=Case when a.IsChk=1 then a.Chg_Qty else v.Stock_Qty-case when v.IsChk=1 then 0 else v.Chg_Qty end end, IsCom=1,tem_dt=getdate() from #AbnData a,(select * from #AbnData) v, (select max(Add_ID) as Add_ID,ItemNo,Src_Depot from #AbnData where IsCom=1 group by ItemNo,Src_Depot) b, (select min(Add_ID) as Add_ID,ItemNo,Src_Depot from #AbnData where IsCom=0 group by ItemNo,Src_Depot) c where a.Add_ID=c.Add_ID and a.ItemNo=v.ItemNo and a.Src_Depot=v.Src_Depot and v.Add_ID=b.Add_ID enddelete #AbnData where (@chk2=1 and Chg_Date>@End_Dt)select a.ItemNo,e.CPN,e.Description,e.MPN,a.Chg_Qty,e.Unit, a.Src_Depot+dbo.Bes_UnionStr()+Isnull(b.Depot_Nm,'') as Src_Depot_Nm,a.To_Depot+dbo.Bes_UnionStr()+Isnull(c.Depot_Nm,'') as To_Depot_Nm, Cast(Convert(varchar(50),a.Chg_Date,111) as Datetime) as Chg_Date,a.Chg_Type, a.Stock_Qty,a.Cher,a.Che_Date from #AbnData a Inner join V_Bas_Item_Bes e on a.ItemNo=e.ItemNo left join V_Bas_Depot_Bes b on a.Src_Depot=b.Depot left join V_Bas_Depot_Bes c on a.To_Depot=c.Depot order by a.ItemNo,a.Src_Depot,a.Chg_Date desc,a.Chg_Qtydrop table #Chg drop table #AbnData GO 哈哈 看得你头晕
CREATE PROCEDURE BES_Q_Dpt_StockChg
@Chk1 bit,@Chk2 bit,@Chk3 bit,@Chk4 bit,
@ItemNo Varchar(50),
@CPN varchar(50),
@Str_Dt datetime,@End_Dt datetime,
@Depot_Nm varchar(255)
ASdeclare @cut_date datetime --用于指出提取数据的最大日期(利用结存表)
select @Chk1=isnull(@Chk1,0),@Chk2=isnull(@Chk2,0),@Chk3=isnull(@Chk3,0),@Chk4=isnull(@Chk4,0),
@Str_Dt=cast(convert(varchar(50),@Str_Dt,111) as datetime),
@End_Dt=cast(convert(varchar(50),@End_Dt,111) as datetime),@Depot_Nm=dbo.Bes_SubStr(@Depot_Nm)--从结存表中提取最近一次结存记录,进行推算
If @chk3=1 and Exists(select Cut_Date from StockManage.dbo.Base_MonthlyCut where Cut_Date>=@End_Dt)
select @cut_date = min(Cut_Date) from StockManage.dbo.Base_MonthlyCut where Cut_Date>=@End_Dt
Else
select @cut_date=getdate()--提取变更数据记录
select ItemNo,Chg_Qty,Unit,Src_Depot,To_Depot,Cast(Convert(varchar(50),Chg_Date,111) as Datetime) as Chg_Date,
Cher,Chg_Type,Che_Date,cast(0 as numeric(18,4)) as Stock_Qty,a.IsChk,cast(0 as bit) as IsCom,cast(0 as bit) as IsDel
into #Chg
from StockManage.dbo.V_Dpt_StockChg a
where (@Chk1=0 or ItemNo=@itemno)
and (@Chk2=0 or Exists(select ItemNo from V_Bas_Item_Bes where CPN=@CPN and ItemNo=a.ItemNo))
and (@Chk3=0 or (Chg_Date>=@Str_Dt and Chg_Date<=@cut_date))
and (@Chk4=0 or (Src_Depot=@Depot_Nm or To_Depot=@Depot_Nm))
--存在接收仓时 添加目标仓动作(动作为双向的)
Insert Into #Chg(ItemNo,Chg_Qty,Unit,Src_Depot,To_Depot,Chg_Date,Cher,Chg_Type,IsChk,IsDel)
select ItemNo,-Chg_Qty,Unit,To_Depot,Src_Depot,Chg_Date,Cher,Chg_Type,IsChk,1
from #Chg where Isnull(To_Depot,'')<>'' and Isnull(Src_Depot,'')<>''--将最近一次的结存加入表中,便于推算
Insert into #Chg(ItemNo,Chg_Qty,Unit,Src_Depot,Chg_Date,Chg_Type,IsChk,Stock_Qty,IsCom)
select a.ItemNo,0,Unit,a.Depot,a.Cut_Date,'结存',1,a.Stock_Qty,1
from StockManage.dbo.Base_MonthlyCut a,
(select Min(a.Cut_Date) as Cut_Date,a.ItemNo,a.Depot from StockManage.dbo.Base_MonthlyCut a, #Chg v
where a.Cut_Date>=@cut_date and v.ItemNo=a.ItemNo and v.Src_Depot=a.Depot
group by a.ItemNo,a.Depot) b
where a.ItemNo=b.ItemNo and a.Cut_Date=b.Cut_Date and a.Depot=b.Depot
--不存在结存表的 从库存表中提取
Insert into #Chg(ItemNo,Chg_Qty,Unit,Src_Depot,Chg_Date,Chg_Type,IsChk,Stock_Qty,IsCom)
select a.ItemNo,0,a.Unit,a.Depot,getdate(),'结存',1,a.Stock_Qty,1
from V_Dpt_Stock_List_Bes a, (select distinct ItemNo,Src_Depot from #Chg) b
where a.ItemNo=b.ItemNo and a.Depot=b.Src_Depot
and a.ItemNo not in(select ItemNo from #Chg where Chg_Type='结存')
--库存表中不存在的置库存为0
Insert into #Chg(ItemNo,Chg_Qty,Unit,Src_Depot,Chg_Date,Chg_Type,IsChk,Stock_Qty,IsCom)
select distinct ItemNo,0,Unit,Src_Depot,getdate(),'结存',1,0 as Stock_Qty,1
from #Chg a
where not Exists(select ItemNo from #Chg where ItemNo=a.ItemNo and Src_Depot=a.Src_Depot and Chg_Type='结存')--进行单位转换
Update #Chg set Chg_Qty=a.Chg_Qty*dbo.BES_CntUnit(a.ItemNo,a.Unit,b.Unit),Unit=b.Unit
from #Chg a, V_Bas_Item_Bes b
where a.ItemNo=b.ItemNo and a.Unit<>b.Unit--排序
select Identity(int,1,1) as Add_ID,ItemNo,Chg_Qty,Src_Depot,To_Depot,Chg_Date,Cher,
Chg_Type,Che_Date,Stock_Qty,Isnull(IsChk,'') as IsChk,Isnull(IsCom,0) as IsCom,
Isnull(IsDel,0) as IsDel,cast(null as datetime) as tem_dt
into #AbnData
from #Chg
order by ItemNo,Src_Depot,Chg_Date desc,Chg_Qty--最小未处理(最大日期)ID对应记录结存 = 最大已处理ID对应记录(上一笔)结存 - 本次发生异动数 (出为负 入为正)
while @@rowcount<>0 and Exists(select IsCom from #AbnData where IsCom=0)
begin
Update #AbnData set Stock_Qty=Case when a.IsChk=1 then a.Chg_Qty
else v.Stock_Qty-case when v.IsChk=1 then 0 else v.Chg_Qty end end,
IsCom=1,tem_dt=getdate()
from #AbnData a,(select * from #AbnData) v,
(select max(Add_ID) as Add_ID,ItemNo,Src_Depot from #AbnData where IsCom=1 group by ItemNo,Src_Depot) b,
(select min(Add_ID) as Add_ID,ItemNo,Src_Depot from #AbnData where IsCom=0 group by ItemNo,Src_Depot) c
where a.Add_ID=c.Add_ID
and a.ItemNo=v.ItemNo and a.Src_Depot=v.Src_Depot
and v.Add_ID=b.Add_ID
enddelete #AbnData where (@chk2=1 and Chg_Date>@End_Dt)select a.ItemNo,e.CPN,e.Description,e.MPN,a.Chg_Qty,e.Unit,
a.Src_Depot+dbo.Bes_UnionStr()+Isnull(b.Depot_Nm,'') as Src_Depot_Nm,a.To_Depot+dbo.Bes_UnionStr()+Isnull(c.Depot_Nm,'') as To_Depot_Nm,
Cast(Convert(varchar(50),a.Chg_Date,111) as Datetime) as Chg_Date,a.Chg_Type,
a.Stock_Qty,a.Cher,a.Che_Date
from #AbnData a
Inner join V_Bas_Item_Bes e on a.ItemNo=e.ItemNo
left join V_Bas_Depot_Bes b on a.Src_Depot=b.Depot
left join V_Bas_Depot_Bes c on a.To_Depot=c.Depot
order by a.ItemNo,a.Src_Depot,a.Chg_Date desc,a.Chg_Qtydrop table #Chg
drop table #AbnData
GO
哈哈 看得你头晕