USE [jhDW]
GO
/****** 对象: StoredProcedure [dbo].[Pro_ExecP] 脚本日期: 04/09/2009 09:50:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Pro_ExecP]
AS
-- =============================================
-- Author: Bitl
-- Create date: 2009-3-27
-- Description: 用于计算P1至P5的值
-- =============================================
--select *,DENSE_RANK() OVER (PARTITION BY material order by (时间)Qty) 'Row Number' from fact_SaleP
--select * from dbo.fact_SalePP pivot(sum(Amt) for P in ([P1],[P2],[P3],[P4],[P5])) as pvt
declare @M numeric(38,3)--总销售量
declare @Amount numeric(38,3)
declare @MP20 float -- M销售量的百分之20
declare @Price float --存储在位于MP20的上限的单价
declare @UpperLimit float --存储在位于MP20的上限的数量
declare @Rows int --总记录条数
declare @Row int -- 单行记录
declare @FQty numeric(38,3) -- 存储每次LOOP的值
declare @FTotalQty numeric(38,3) --区间期销售总量
declare @FFactoryAmount numeric(38,3) --存储每次LOOP的值
declare @FFactoryTotalAmount numeric(30,3) --区间销售总额
declare @PValue char(2) --最外层WHILE 最大值是5
declare @m_rows int
declare @m_row int
declare @years int
declare @weeks int
declare @FBusinessDate datetime
declare @m_material varchar(240)
declare @m_no int
declare @Rang table (m_Rows int,m_material varchar(240),years int,weeks int,m_no int IDENTITY(1,1)) ---内存表,用于多种物料时分析所用
insert into @Rang(m_Rows,m_material,years,weeks) select count(RowNumber),L3FLNAME,years,weeks from dbo.fact_SaleP group by years,weeks,L3FLNAME order by years,weeks,L3FLNAME
set @PValue='P1'
set @Row=1
set @FQty =0.00
set @FFactoryAmount=0.000
set @FFactoryTotalAmount=0.000
set @FTotalQty=0.00
--exec pro_ExecP
set @m_row = 1
set @m_Rows=1
set @m_no=1
while @m_row <= (select count(*) from @Rang)
begin
---
select * from @Rang where m_no=@m_row --print of ....
select @m_rows=m_rows,@m_material=m_material,@m_no=m_no,@years=years,@weeks=weeks from @Rang where m_no=@m_row
---
select @m_rows,@m_material,@m_no,@years,@weeks --print of ....
select @M= sum(FQty),@Amount=sum(FFactoryAmount) from dbo.fact_SaleP where L3FLNAME=@m_material and Years=@years and Weeks=@weeks
set @MP20=@M*0.2
---
select @Row,@m_rows,@M,@Amount,@MP20 --print of ....
while @Row<=@m_rows ---m_rows是取@Rang中的每一条记录的m_rows的值
begin
lap:
select @FFactoryAmount=FFactoryAmount,@FQty=FQty from dbo.fact_SaleP where RowNumber=@Row and L3FLNAME=@m_material and Years=@Years and Weeks=@weeks
set @Row=@Row+1
---
select @FFactoryAmount A,@FQty,@Row-1,@years,@weeks --print of ....
set @FFactoryTotalAmount=@FFactoryTotalAmount+@FFactoryAmount
set @FTotalQty=@FTotalQty+@FQty
---
select @FFactoryTotalAmount,@FTotalQty --print of ....
if @FTotalQty >= @MP20
begin
set @UpperLimit=@FTotalQty-@MP20
set @FFactoryTotalAmount=@FFactoryTotalAmount-(@FFactoryAmount/@FQty)*@UpperLimit
---
select @UpperLimit LIMIT,(@FFactoryAmount/@FQty)*@UpperLimit as LIMIT_Amt,@FTotalQty,@FFactoryAmount,@FQty,@MP20,@FFactoryTotalAmount--print of ....
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount,@PValue,@M,@Amount)
if @UpperLimit/@MP20=4
begin
select '4次'
set @FFactoryTotalAmount=(@FFactoryAmount/@FQty)*@UpperLimit/4
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount,@PValue,@M,@Amount)
set @UpperLimit = 0
set @FFactoryTotalAmount=0
end
if @UpperLimit/@MP20=3
begin
select '3次'
set @FFactoryTotalAmount=(@FFactoryAmount/@FQty)*@UpperLimit/3
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount,@PValue,@M,@Amount)
set @UpperLimit = 0
set @FFactoryTotalAmount=0
end
if @UpperLimit/@MP20=2
begin
select '2次'
set @FFactoryTotalAmount=(@FFactoryAmount/@FQty)*@UpperLimit/2
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount,@PValue,@M,@Amount)
set @UpperLimit = 0
set @FFactoryTotalAmount=0
end
if @UpperLimit/@MP20=1
begin
select '1次'
set @FFactoryTotalAmount=(@FFactoryAmount/@FQty)*@UpperLimit/1
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount,@PValue,@M,@Amount)
set @UpperLimit = 0
set @FFactoryTotalAmount=0
end
select @UpperLimit LIMIT,@FFactoryTotalAmount TotalAmt
set @FTotalQty=@UpperLimit
set @FFactoryTotalAmount=(@FFactoryAmount/@FQty)*@UpperLimit
select @FTotalQty FTotalQty,@FFactoryTotalAmount FFactoryTotalAmount
if @FTotalQty/@MP20=4
begin
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount/4,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount/4,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount/4,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount/4,@PValue,@M,@Amount)
set @FTotalQty=0
set @FFactoryTotalAmount=0
end
if @FTotalQty/@MP20=3
begin
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount/3,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount/3,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount/3,@PValue,@M,@Amount)
set @FTotalQty=0
set @FFactoryTotalAmount=0
end
if @FTotalQty/@MP20=2
begin
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount/2,@PValue,@M,@Amount)
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount/2,@PValue,@M,@Amount)
set @FTotalQty=0
set @FFactoryTotalAmount=0
end
if @FTotalQty/@MP20=1
begin
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
insert into dbo.fact_SalePP (Years,Weeks,FLName,[M20],FFactoryAmount,P,M,Amount) values(@years,@weeks,@m_material,@MP20,@FFactoryTotalAmount/1,@PValue,@M,@Amount)
set @FTotalQty=0
set @FFactoryTotalAmount=0
end
end
else
goto Lap
set @FTotalQty=@UpperLimit
set @FFactoryTotalAmount=(@FFactoryAmount/@FQty)*@UpperLimit
set @PValue='P'+cast(cast(substring(@PValue,2,1) as int)+1 as char(1))
end
set @m_row=@m_row+1
set @Row=1
set @PValue='P1'
set @FTotalQty=0
set @FFactoryTotalAmount=0
end
--exec pro_ExecP
select
years,
Weeks,
FLName,
L3FID,
M,
M20,
Amount/M as P,
P1/M20 as P1,
(isnull(P2,P1))/M20 as P2,
(isnull(P3,P2))/M20 as P3,
(isnull(isnull(P4,P3),P2))/M20 as P4,
(isnull(isnull(isnull(P5,P4),P3),P2))/M20 as P5
from
dbo.fact_SalePP ap join (select distinct L3FID,L3FLNAME_C from dbo.dim_Material) m
on ap.FLName=m.L3FLNAME_C
pivot(sum(FFactoryAmount) for P in ([P1],[P2],[P3],[P4],[P5])) as pvt