pivot

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