--直接执行下面几个命令即可生成所有数据(注意:019开头的数据小于10条的情况)
--V03头的数据Category有问题,需注意
--896和876开头有问题
--EXEC Compare_Qty 0
--EXEC Compare_Qty 1
--EXEC Class_Loc_Result JMS
--EXEC Class_Loc_Result JEF
--EXEC Class_Loc_Result JSG
--EXEC Class_Loc_Result WIP
--EXEC COUNT_TOTAL
CREATE PROCEDURE dbo.Compare_Qty
--@flag 取值为0代表计算上月的仓存数据(table:Last_Mon_Result);1代表计算本月的仓存数据(talbe:This_Mon_Result)
(@flag bit)
AS
set nocount on
declare @Last_Mon table(Id int Identity(1,1) not null,PartNo nvarchar(20),Location nvarchar(10),Max_Qty numeric(12,0),Max_Amt numeric(12,4),Type nvarchar(10),Description nvarchar(20))
declare @table table(PartNo nvarchar(20),Location nvarchar(10),Onhand numeric(18, 0),Type nvarchar(10),Cost float,Category nvarchar(10))
delete from @table
if @flag=0
begin
insert into @table
select PartNo,Location,Onhand,Type,Cost,Category
from Last_Mon
--where onhand<>0
order by Location,PartNo
end
else if @flag=1
begin
insert into @table
select PartNo,Location,Onhand,Type,Cost,Category
from This_Mon
--where onhand<>0
order by Location,PartNo
end
----JEF仓
begin
--JEF成品最大数量10个
insert into @Last_Mon(PartNo,Location) values('十个最高数量物料','JEF')
insert into @Last_Mon
select top 10 PartNo,'JEF',Onhand,Onhand*Cost,'最大数量','JEF成品'
from @table
where Location='JEF' and Category between 'F' and 'FZZZ'
order by Onhand desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JEF成品十个最高数量汇总','JEF',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JEF' and Category between 'F' and 'FZZZ'
order by Onhand desc,PartNo) a
--JEF成品最大金额10个
insert into @Last_Mon(PartNo,Location) values('十个最高金额物料','JEF')
insert into @Last_Mon
select top 10 PartNo,'JEF',Onhand,Onhand*Cost,'最大金额','JEF成品'
from @table
where Location='JEF' and Category between 'F' and 'FZZZ'
order by Onhand*Cost desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JEF成品十个最高金额汇总','JEF',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JEF' and Category between 'F' and 'FZZZ'
order by Onhand*Cost desc,PartNo) a
----JEF仓所有成品汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'JEF成品汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='JEF' and Category between 'F' and 'FZZZ'
--JEF胶料最大数量10个
insert into @Last_Mon(PartNo,Location) values('十个最高数量胶料','JEF')
insert into @Last_Mon
select top 10 PartNo,'JEF',Onhand,Onhand*Cost,'最大数量','JEF胶料'
from @table
where Location='JEF' and Category ='P-RESI'
order by Onhand desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JEF胶料十个最高数量汇总','JEF',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JEF' and Category ='P-RESI'
order by Onhand desc,PartNo) a
--JEF胶料最大金额10个
insert into @Last_Mon(PartNo,Location) values('十个最高金额胶料','JEF')
insert into @Last_Mon
select top 10 PartNo,'JEF',Onhand,Onhand*Cost,'最大金额','JEF胶料'
from @table
where Location='JEF' and Category ='P-RESI'
order by Onhand*Cost desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JEF胶料十个最高金额汇总','JEF',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JEF' and Category ='P-RESI'
order by Onhand*Cost desc,PartNo) a
----JEF仓所有胶料汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'JEF胶料汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='JEF' and Category ='P-RESI'
--JEF半制品最大数量10个
insert into @Last_Mon(PartNo,Location) values('十个最高数量物料','JEF')
insert into @Last_Mon
select top 10 PartNo,'JEF',Onhand,Onhand*Cost,'最大数量','JEF半制品'
from @table
where Location='JEF' and Type='A'
order by Onhand desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JEF半制品十个最高数量汇总','JEF',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JEF' and Type='A'
order by Onhand desc,PartNo) a
--JEF半制品最大金额10个
insert into @Last_Mon(PartNo,Location) values('十个最高金额物料','JEF')
insert into @Last_Mon
select top 10 PartNo,'JEF',Onhand,Onhand*Cost,'最大金额','JEF半制品'
from @table
where Location='JEF' and Type='A'
order by Onhand*Cost desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JEF半制品十个最高金额汇总','JEF',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JEF' and Type='A'
order by Onhand*Cost desc,PartNo) a
----JEF仓所有半制品汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'JEF半制品汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='JEF' and Type='A'
------JEF物料计算
declare @Part1 nvarchar(10)
declare Cur_Value1 cursor for
select Left(PartNo,3) from @table
where Location='JEF' and Type='P' and Category<>'P-RESI'
Group by left(PartNo,3)
order by left(PartNo,3)
Open Cur_Value1
fetch next from Cur_Value1 into @Part1
while @@fetch_status=0
begin
--JEF最大数量
insert into @Last_Mon(PartNo,location) values('十个最高数量物料','JEF')
insert into @Last_Mon
select top 10 PartNo,'JEF',Onhand,Onhand*Cost,'最大数量','JEF物料'
from @table
where Location='JEF' and Type='P' and Category<>'P-RESI' and PartNo like @Part1+'%'
order by Onhand desc
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JEF仓'+@Part1+'开头数量汇总','JEF',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JEF' and Type='P' and Category<>'P-RESI' and PartNo like @Part1+'%'
order by Onhand desc,PartNo) a
--JEF最大金额
insert into @Last_Mon(PartNo,Location) values('十个最高金额物料','JEF')
insert into @Last_Mon
select top 10 PartNo,'JEF',Onhand,Onhand*Cost,'最大金额','JEF物料'
from @table
where Location='JEF' and Type='P' and Category<>'P-RESI' and PartNo like @Part1+'%'
order by Onhand*Cost desc
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JEF仓'+@Part1+'开头金额汇总','JEF',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JEF' and Type='P' and Category<>'P-RESI' and PartNo like @Part1+'%'
order by Onhand*Cost desc,PartNo) a
----JEF仓物料分类汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'JEF仓'+@Part1+'开头汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='JEF' and Type='P' and Category<>'P-RESI' and PartNo like @Part1+'%'
fetch next from Cur_Value1 into @Part1
end
close Cur_Value1
deallocate Cur_Value1
end
----JEF仓所有物料汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'JEF物料汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='JEF' and Type='P' and Category<>'P-RESI'
----JMS仓
begin
--JMS成品最大数量10个
insert into @Last_Mon(PartNo,Location) values('十个最高数量物料','JMS')
insert into @Last_Mon
select top 10 PartNo,'JMS',Onhand,Onhand*Cost,'最大数量','JMS成品'
from @table
where Location='JMS' and Category between 'A' and 'FZZZ'
order by Onhand desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JMS成品十个最高数量汇总','JMS',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JMS' and Category between 'A' and 'FZZZ'
order by Onhand desc,PartNo) a
--JMS成品最大金额10个
insert into @Last_Mon(PartNo,Location) values('十个最高金额物料','JMS')
insert into @Last_Mon
select top 10 PartNo,'JMS',Onhand,Onhand*Cost,'最大金额','JMS成品'
from @table
where Location='JMS' and Category between 'A' and 'FZZZ'
order by Onhand*Cost desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JMS成品十个最高金额汇总','JMS',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JMS' and Category between 'A' and 'FZZZ'
order by Onhand*Cost desc,PartNo) a
----JMS仓所有成品汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'JMS成品汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='JMS' and Category between 'A' and 'FZZZ'
--JMS胶料最大数量10个
insert into @Last_Mon(PartNo,Location) values('十个最高数量物料','JMS')
insert into @Last_Mon
select top 10 PartNo,'JMS',Onhand,Onhand*Cost,'最大数量','JMS胶料'
from @table
where Location='JMS' and Category ='P-RESI'
order by Onhand desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JMS胶料十个最高数量汇总','JMS',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JMS' and Category ='P-RESI'
order by Onhand desc,PartNo) a
--JMS胶料最大金额10个
insert into @Last_Mon(PartNo,Location) values('十个最高金额物料','JMS')
insert into @Last_Mon
select top 10 PartNo,'JMS',Onhand,Onhand*Cost,'最大金额','JMS胶料'
from @table
where Location='JMS'and Category ='P-RESI'
order by Onhand*Cost desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JMS胶料十个最高金额汇总','JMS',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JMS' and Category ='P-RESI'
order by Onhand*Cost desc,PartNo) a
----JMS仓所有胶料汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'JMS胶料汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='JMS' and Category ='P-RESI'
-----JMS仓物料计算
declare @Part2 nvarchar(10)
declare Cur_Value2 cursor for
select Left(PartNo,3) from @table
where Location='JMS' and Type='P' and Category<>'P-RESI'
Group by left(PartNo,3)
order by left(PartNo,3)
Open Cur_Value2
fetch next from Cur_Value2 into @Part2
while @@fetch_status=0
begin
--JMS最大数量
insert into @Last_Mon(PartNo,Location) values('十个最高数量物料','JMS')
insert into @Last_Mon
select top 10 PartNo,'JMS',Onhand,Onhand*Cost,'最大数量','JMS物料'
from @table
where Location='JMS'and Type='P' and Category<>'P-RESI' and PartNo like @Part2+'%'
order by Onhand desc
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JMS仓'+@Part2+'开头数量汇总','JMS',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JMS'and Type='P' and Category<>'P-RESI' and PartNo like @Part2+'%'
order by Onhand desc) a
--JMS最大金额
insert into @Last_Mon(PartNo,Location) values('十个最高金额物料','JMS')
insert into @Last_Mon
select top 10 PartNo,'JMS',Onhand,Onhand*Cost,'最大金额','JMS物料'
from @table
where Location='JMS' and Type='P' and Category<>'P-RESI' and PartNo like @Part2+'%'
order by Onhand*Cost desc
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JMS仓'+@Part2+'开头金额汇总','JMS',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JMS'and Type='P' and Category<>'P-RESI' and PartNo like @Part2+'%'
order by Onhand*Cost desc) a
----JMS仓物料分类汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'JMS仓'+@Part2+'开头汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='JMS'and Type='P' and Category<>'P-RESI' and PartNo like @Part2+'%'
fetch next from Cur_Value2 into @Part2
end
close Cur_Value2
deallocate Cur_Value2
end
----JMS仓所有物料汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'JMS物料汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='JMS' and Type='P' and Category<>'P-RESI'
----JSG仓
begin
--JSG成品最大数量10个
insert into @Last_Mon(PartNo,Location) values('十个最高数量物料','JSG')
insert into @Last_Mon
select top 10 PartNo,'JSG',Onhand,Onhand*Cost,'最大数量','JSG仓'
from @table
where Location='JSG'
order by Onhand desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JSG仓数量汇总','JSG',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JSG'
order by Onhand desc,PartNo) a
--JSG成品最大金额10个
insert into @Last_Mon(PartNo,Location) values('十个最高金额物料','JSG')
insert into @Last_Mon
select top 10 PartNo,'JSG',Onhand,Onhand*Cost,'最大金额','JSG仓'
from @table
where Location='JSG'
order by Onhand*Cost desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'JSG仓金额汇总','JSG',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='JSG'
order by Onhand*Cost desc,PartNo) a
----JSG仓汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'JSG仓汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='JSG'
end
----WIP仓
begin
--WIP成品最大数量10个
insert into @Last_Mon(PartNo,Location) values('十个最高数量物料','WIP')
insert into @Last_Mon
select top 10 PartNo,'WIP',Onhand,Onhand*Cost,'最大数量','WIP成品'
from @table
where Location='WIP'and Category between 'F' and 'FZZZ'
order by Onhand desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'WIP成品十个最高数量汇总','WIP',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='WIP'and Category between 'F' and 'FZZZ'
order by Onhand desc,PartNo) a
--WIP成品最大金额10个
insert into @Last_Mon(PartNo,Location) values('十个最高金额物料','WIP')
insert into @Last_Mon
select top 10 PartNo,'WIP',Onhand,Onhand*Cost,'最大金额','WIP成品'
from @table
where Location='WIP' and Category between 'F' and 'FZZZ'
order by Onhand*Cost desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'WIP成品十个最高金额汇总','WIP',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='WIP'and Category between 'F' and 'FZZZ'
order by Onhand*Cost desc,PartNo) a
----WIP仓成品汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'WIP仓成品汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='WIP'and Category between 'F' and 'FZZZ'
--WIP胶料最大数量10个
insert into @Last_Mon(PartNo,Location) values('十个最高数量物料','WIP')
insert into @Last_Mon
select top 10 PartNo,'WIP',Onhand,Onhand*Cost,'最大数量','WIP胶料'
from @table
where Location='WIP' and Category ='P-RESI'
order by Onhand desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'WIP胶料十个最高数量汇总','WIP',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='WIP' and Category ='P-RESI'
order by Onhand desc,PartNo) a
--WIP胶料最大金额10个
insert into @Last_Mon(PartNo,Location) values('十个最高金额物料','WIP')
insert into @Last_Mon
select top 10 PartNo,'WIP',Onhand,Onhand*Cost,'最大金额','WIP胶料'
from @table
where Location='WIP'and Category ='P-RESI'
order by Onhand*Cost desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'WIP胶料十个最高金额汇总','WIP',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='WIP' and Category ='P-RESI'
order by Onhand*Cost desc,PartNo) a
----WIP仓胶料汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'WIP仓胶料汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='WIP' and Category ='P-RESI'
--WIP半制品最大数量10个
insert into @Last_Mon(PartNo,Location) values('十个最高数量物料','WIP')
insert into @Last_Mon
select top 10 PartNo,'WIP',Onhand,Onhand*Cost,'最大数量','WIP半制品'
from @table
where Location='WIP' and Type='A'
order by Onhand desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'WIP半制品十个最高数量汇总','WIP',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='WIP' and Type='A'
order by Onhand desc,PartNo) a
--WIP半制品最大金额10个
insert into @Last_Mon(PartNo,Location) values('十个最高金额物料','WIP')
insert into @Last_Mon
select top 10 PartNo,'WIP',Onhand,Onhand*Cost,'最大金额','WIP半制品'
from @table
where Location='WIP' and Type='A'
order by Onhand*Cost desc,PartNo
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'WIP半制品十个最高金额汇总','WIP',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='WIP' and Type='A'
order by Onhand*Cost desc,PartNo) a
----WIP仓半制品汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'WIP仓半制品汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='WIP' and Type='A'
----WIP仓物料计算
declare @Part3 nvarchar(10)
declare Cur_Value3 cursor for
select Left(PartNo,3) from @table
where Location='WIP' and Type='P' and Category<>'P-RESI'
Group by left(PartNo,3)
order by left(PartNo,3)
Open Cur_Value3
fetch next from Cur_Value3 into @Part3
while @@fetch_status=0
begin
--WIP最大数量
insert into @Last_Mon(PartNo,Location) values('十个最高数量物料','WIP')
insert into @Last_Mon
select top 10 PartNo,'WIP',Onhand,Onhand*Cost,'最大数量','WIP物料'
from @table
where Location='WIP' and Type='P' and Category<>'P-RESI' and PartNo like @Part3+'%'
order by Onhand desc
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'WIP仓'+@Part3+'开头数量汇总','WIP',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='WIP' and Type='P' and Category<>'P-RESI' and PartNo like @Part3+'%'
order by Onhand desc) a
--WIP最大金额
insert into @Last_Mon(PartNo,Location) values('十个最高金额物料','WIP')
insert into @Last_Mon
select top 10 PartNo,'WIP',Onhand,Onhand*Cost,'最大金额','WIP物料'
from @table
where Location='WIP' and Type='P' and Category<>'P-RESI' and PartNo like @Part3+'%'
order by Onhand*Cost desc
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt)
select 'WIP仓'+@Part3+'开头金额汇总','WIP',sum(a.onhand) as Qty,sum(a.amt) as Amt
from (select top 10 PartNo,Onhand,Onhand*Cost as amt
from @table
where Location='WIP' and Type='P' and Category<>'P-RESI' and PartNo like @Part3+'%'
order by Onhand*Cost desc) a
----WIP仓物料分类汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'WIP仓'+@Part3+'开头汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='WIP' and Type='P' and Category<>'P-RESI' and PartNo like @Part3+'%'
fetch next from Cur_Value3 into @Part3
end
close Cur_Value3
deallocate Cur_Value3
end
----WIP仓物料汇总
insert into @Last_Mon(PartNo,Location,Max_Qty,Max_Amt,Type)
select 'WIP仓物料汇总','Totals',sum(onhand) as Qty,sum(onhand*cost) as Amt,'Sum'
from @table
where Location='WIP' and Type='P' and Category<>'P-RESI'
if @flag=0
begin
truncate table Last_Mon_Result
update @Last_Mon set Description='' where Description is null
update @Last_Mon set Max_Qty=0 where Max_Qty is null
insert into Last_Mon_Result(PartNo,Location,Max_Qty,Max_Amt,Type,Description)
select PartNo,Location,Max_Qty,Max_Amt,Type,Description
from @Last_Mon
order by Id
end
else if @flag=1
begin
truncate table This_Mon_Result
update @Last_Mon set Description='' where Description is null
update @Last_Mon set Max_Qty=0 where Max_Qty is null
insert into This_Mon_Result(PartNo,Location,Max_Qty,Max_Amt,Type,Description)
select PartNo,Location,Max_Qty,Max_Amt,Type,Description
from @Last_Mon
order by Id
--truncate table Sum_Total
--insert into Sum_Total
--select a.PartNo,a.Location,a.Max_Qty,a.Max_Amt,b.PartNo as PartNo1,b.Location as Location1,b.Max_Qty as Max_Qty1,b.Max_Amt as Max_Amt1,
--Per_Qty=convert(decimal(12,4),isnull(case when a.Max_qty is null then 0
--when a.Max_Qty=0 then 0
--when a.Max_Qty<>0 then Round((b.Max_Qty-a.Max_Qty)/a.Max_Qty*100,2)
--else 0 end,0)),
--Per_Amt=convert(decimal(12,4),isnull(case when a.Max_Amt is null then 0
--when a.Max_Amt=0 then 0
--when a.Max_Amt<>0 then Round((b.Max_Amt-a.Max_Amt)/a.Max_Amt*100,2)
--else 0 end,0)),
--a.Description
--from Last_Mon_Result a,This_Mon_Result b
--where a.Id=b.Id and a.type='sum' and a.Max_Qty<>0 and b.Max_Qty<>0
--order by a.Id
--truncate table WIP
--insert into WIP
--select a.PartNo,a.Location,a.Max_Qty,a.Max_Amt,b.PartNo as PartNo1,b.Location as Location1,b.Max_Qty as Max_Qty1,b.Max_Amt as Max_Amt1,a.Description
--from Last_Mon_Result a,This_Mon_Result b
--where a.Id=b.Id and a.location='WIP' and a.Max_Qty<>0 and b.Max_Qty<>0
--order by a.Id
end
delete from @table
delete from @Last_Mon
print '执行完毕'
GO
--EXEC COUNT_TOTAL
CREATE PROCEDURE COUNT_TOTAL
AS
truncate table Sum_Total
insert into Sum_Total
select a.PartNo,a.Location,a.Max_Qty,a.Max_Amt,b.PartNo as PartNo1,b.Location as Location1,b.Max_Qty as Max_Qty1,b.Max_Amt as Max_Amt1,
Per_Qty=convert(decimal(12,4),isnull(case when a.Max_qty is null then 0
when a.Max_Qty=0 then 0
when a.Max_Qty<>0 then Round((b.Max_Qty-a.Max_Qty)/a.Max_Qty*100,2)
else 0 end,0)),
Per_Amt=convert(decimal(12,4),isnull(case when a.Max_Amt is null then 0
when a.Max_Amt=0 then 0
when a.Max_Amt<>0 then Round((b.Max_Amt-a.Max_Amt)/a.Max_Amt*100,2)
else 0 end,0)),
a.Description
from Last_Mon_Result a,This_Mon_Result b
where a.Id=b.Id and a.type='sum' and a.Max_Qty<>0 and b.Max_Qty<>0
order by a.Id
PRINT '执行完毕'
GO
--功能：对上月及本月计算结果，进行分仓处理及比较
--EXEC Class_Loc_Result JMS
--EXEC Class_Loc_Result JEF
--EXEC Class_Loc_Result JSG
--EXEC Class_Loc_Result WIP
CREATE PROCEDURE Class_Loc_Result
(@Location Nvarchar(10)) --@Location取值分别为：JMS、JEF、JSG、WIP
AS
SET NOCOUNT OFF
Declare @StringSql nvarchar(4000)
Exec('truncate table '+ @Location)
SET @StringSql=' insert into '+ @Location
SET @StringSql=@StringSql+ ' select a.PartNo,a.Location,a.Max_Qty,a.Max_Amt,b.PartNo as PartNo1,b.Location as Location1,b.Max_Qty as Max_Qty1,b.Max_Amt as Max_Amt1,a.Description'
SET @StringSql=@StringSql+ ' from Last_Mon_Result a,This_Mon_Result b'
SET @StringSql=@StringSql+ ' where a.Id=b.Id and a.location='''+@Location+''''
SET @StringSql=@StringSql+ ' order by a.Id'
EXEC(@StringSql)
SET NOCOUNT OFF
PRINT '执行完毕'
GO
--功能：对上月与本月分组计算后进行分组比较
CREATE PROCEDURE Sum_Total_Table
AS
SET NOCOUNT ON
truncate table Sum_Total
insert into Sum_Total
select a.PartNo,a.Location,a.Max_Qty,a.Max_Amt,b.PartNo as PartNo1,b.Location as Location1,b.Max_Qty as Max_Qty1,b.Max_Amt as Max_Amt1,
Per_Qty=convert(decimal(12,4),isnull(case when a.Max_qty is null then 0
when a.Max_Qty=0 then 0
when a.Max_Qty<>0 then Round((b.Max_Qty-a.Max_Qty)/a.Max_Qty*100,2)
else 0 end,0)),
Per_Amt=convert(decimal(12,4),isnull(case when a.Max_Amt is null then 0
when a.Max_Amt=0 then 0
when a.Max_Amt<>0 then Round((b.Max_Amt-a.Max_Amt)/a.Max_Amt*100,2)
else 0 end,0)),
a.Description
from Last_Mon_Result a,This_Mon_Result b
where a.Id=b.Id and a.type='sum' and a.Max_Qty<>0 and b.Max_Qty<>0
order by a.Id
PRINT '执行完毕'
SET NOCOUNT OFF
GO