《BI项目笔记》历年的初烟水分均值变化分析Cube的建立

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[vRoastingPlan]
AS
    SELECT  T2.COL_RP_CODE AS ProductionNo ,
            T1.COL_RRD_CODE AS FormulaNo ,
            T2.COL_RP_YEAR AS Year ,
            T1.OriginID AS OriginID ,
            T1.COL_RPD_RT_AREA AS Origin ,
            T1.I_GBGradeID AS GBGradeID ,
            T1.COL_RPD_RT_QUALITY AS GBGrade ,
            t1.ProdLineID ,
            T1.COL_RPD_ROASTING_DEVICE AS ProdLine ,
            t1.PurchaseID ,
            T1.COL_RPD_FT_PURCHASE_TYPE AS Purchase ,
            t1.TLProcTypeID ,
            T1.COL_RPD_MANUFACTURE_TYPE AS TLProcType ,
            t1.CustCode ,
            T1.COL_RPD_RT_OWNER AS Cust ,
            T2.COL_RP_FINISH_FLAG AS FinishFlag ,
            T1.COL_RPD_EXECUTE_TIME AS ExecuteTime
    FROM    dbo.TB_MRP_ROASTING_PLAN_DETAIL2 AS T1
            LEFT JOIN .dbo.TB_MRP_ROASTING_PLAN2 AS T2 ON T1.COL_RP_PK = T2.COL_RP_PK

GO

--新增字段
ALTER TABLE T_QualMoisture_Middle ADD OriginID INT 
ALTER TABLE T_QualMoisture_Middle ADD GBGradeID INT 
ALTER TABLE T_QualMoisture_Middle ADD ProdLineID VARCHAR(10)  
ALTER TABLE T_QualMoisture_Middle ADD PurchaseID INT 
ALTER TABLE T_QualMoisture_Middle ADD TLProcTypeID INT 
ALTER TABLE T_QualMoisture_Middle ADD CustCode VARCHAR(5) 

--同步数据
UPDATE  T_QualMoisture_Middle
SET     T_QualMoisture_Middle.OriginID = vRoastingPlan.OriginID ,
        T_QualMoisture_Middle.GBGradeID = vRoastingPlan.GBGradeID ,
        T_QualMoisture_Middle.ProdLineID = vRoastingPlan.ProdLineID ,
        T_QualMoisture_Middle.PurchaseID = vRoastingPlan.PurchaseID ,
        T_QualMoisture_Middle.TLProcTypeID = vRoastingPlan.TLProcTypeID ,
        T_QualMoisture_Middle.CustCode = vRoastingPlan.CustCode
FROM    vRoastingPlan
WHERE   T_QualMoisture_Middle.V_Produce_ID = vRoastingPlan.ProductionNo
        AND T_QualMoisture_Middle.V_Batch_ID = vRoastingPlan.FormulaNo
        
SELECT  *
FROM    T_QualMoisture_Middle