DECLARE @Date_Init AS DateTime
DECLARE @Date_End AS DateTime
DECLARE @No_Master AS VARCHAR(5)
DECLARE @Pivot_Dates AS VARCHAR(MAX)
DECLARE @Dates_Suma AS VARCHAR(MAX)
DECLARE @Pivot_OPS AS VARCHAR(MAX)
DECLARE @Pivot_TIMES AS VARCHAR(MAX)
DECLARE @The_Query AS VARCHAR(MAX)
SET @No_Master='1205'
SELECT @Date_Init=fechaI FROM Weeks WHERE Week=@No_Master
SET @Date_End=DATEADD(DAY,6,@Date_Init)

-- Get Operations on this master
SELECT  @Pivot_OPS = STUFF((
                        SELECT DISTINCT('],['+DescSp+'_OPS')
                        FROM PH_Master phm
                        INNER JOIN I_Cat_Operaciones_Hab OPS ON OPS.Oper_ID=phm.OPER_ID
                        INNER JOIN Cat_Operaciones cat ON Cat.Campo = OPS.Proceso
                        WHERE No_Master=@No_Master
                        ORDER BY '],['+DescSp+'_OPS'
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

SET @Pivot_TIMES=REPLACE(@Pivot_OPS,'_OPS','_Time')

-- Get planned dates of this ph_master @No_Master
SELECT  @Pivot_Dates = STUFF(( SELECT
                        DISTINCT('],['+LEFT(CONVERT(VARCHAR, Fecha, 120), 10))
                        FROM PH_Master
                        WHERE NO_Master = @No_Master
                        ORDER BY '],['+LEFT(CONVERT(VARCHAR, Fecha, 120), 10)
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

SET @Dates_Suma=REPLACE(@Pivot_Dates,',','+')



SELECT * FROM
(
    --First Pivot Get The Qty of Hours Needed for the job
    SELECT
    plh.No_Master,plh.JOB_Number, cus.Identifier_Customer, cus.Name
    , pc.Job,pc.Phase, pc.Part_Number, pc.Description
    , bom.Part_No,bom.Qty_L,catop.DescSp+'_Time' AS DescSp,bom.ID,CONVERT(INT,bom.Nivel) Nivel
    , (((plh.pzas_Fab*bom.Qty_req)*std.Factor)/60) TheTime
    FROM
    (
        SELECT No_Master,JOB_Number,pzas_Fab,Fecha FROM PL_Historial
        UNION ALL SELECT @No_Master AS No_Master,JOB_Number,0 AS pzas_Fab,DATE AS Fecha FROM PH_Entries WHERE DATE BETWEEN DATEADD(DAY,-2,@Date_Init) AND DATEADD(DAY,-2,@Date_End)
    ) AS plh
    INNER JOIN Production_Control pc ON plh.JOB_Number = pc.JOB_Number
    INNER JOIN Customer cus ON pc.Identifier_Customer=cus.Identifier_Customer
    INNER JOIN I_Bom bom ON plh.JOB_Number = bom.JOB_Number
    INNER JOIN I_Parts_ENG eng ON bom.Part_no = eng.Part_No
    --INNER JOIN I_cat_Type_Matl Matl ON eng.Type_Matl = Matl.Type_Matl
    INNER JOIN I_Opers_Habilitado ohab ON bom.JOB_Number = ohab.JOB_Number AND bom.Nivel = ohab.Nivel
    INNER JOIN I_Cat_Operaciones_Hab catHBOps ON ohab.Oper_id = catHBOps.Oper_Id AND (eng.Type_Matl = catHBOps.Type_Matl )--OR catHBOps.Type_Matl = 'ALL')
    INNER JOIN Cat_Operaciones catop ON catHBOps.Proceso = catop.Campo
    LEFT JOIN I_Cat_Std_Habilitado std ON ohab.Oper_Id = std.Oper_Id AND bom.Part_No = std.Part_No AND bom.Qty_L>=std.Longitud_Desde AND bom.Qty_L<=std.Longitud_Hasta
    WHERE plh.No_Master= @No_Master --AND plh.JOB_Number=21067--21094
                          AND plh.Fecha BETWEEN @Date_Init AND @Date_End
)AS Time1
PIVOT (SUM(TheTime) FOR DescSp IN ([Corte_Time],[CorteYPonche_Time],[Ponche_Time],[Resaque_Time]) )
AS Time2
LEFT JOIN
(
 --Second Pivot Get The Qty of Operations Needed for the job
 SELECT * FROM
    (
    SELECT bom.JOB_Number,bom.Nivel,ioper.Oper_ID,cat.DescSp+'_OPS' AS DescSp
    FROM
    I_Bom bom
    INNER JOIN I_Opers_Habilitado ioper ON bom.JOB_Number = ioper.JOB_Number AND bom.Nivel=ioper.Nivel
    INNER JOIN I_Cat_Operaciones_Hab ohab ON ioper.Oper_Id = ohab.Oper_id
    INNER JOIN Cat_OPeraciones cat ON ohab.Proceso = cat.Campo
    --WHERE bom.JOB_Number=5883
    ) AS Oper1
    PIVOT(COUNT(Oper_ID) FOR DescSp IN ([Corte_OPS],[CorteYPonche_OPS],[Ponche_OPS],[Resaque_OPS]) )
    AS Oper2
) AS OPS ON OPS.JOB_Number=Time2.JOB_Number AND OPS.Nivel=Time2.Nivel
LEFT JOIN
(
 --Third Pivot Get The qty of pieces by day No_Master
 SELECT *,[2012-01-28]+[2012-01-30]+[2012-01-31]+[2012-02-01]+[2012-02-02]+[2012-02-03] AS Total_Suma FROM
 (
    SELECT
    pm.JOB_Number,pm.No_Master,pm.Fecha,pm.Part_No,pm.Nivel,Pzas_Fab, MAX(ohab.Secuencia) Secuencia
    FROM PH_Master pm
    INNER JOIN I_OPERS_HABILITADO ohab ON ohab.JOB_Number = pm.JOB_Number AND ohab.Nivel = pm.Nivel AND ohab.Oper_Id<>'NOCUT'
    WHERE pm.No_master=1205  --AND pm.JOB_Number=21088
    GROUP BY pm.JOB_Number,pm.No_Master,pm.Fecha,pm.Part_No,pm.Nivel,Pzas_Fab
--select * from i_opers_habilitado
 ) AS Dt1
 PIVOT(SUM(Pzas_Fab) FOR Fecha IN ([2012-01-28],[2012-01-30],[2012-01-31],[2012-02-01],[2012-02-02],[2012-02-03]) )
 AS Dt2
) AS DateI ON DateI.JOB_Number=Time2.JOB_Number AND DateI.Nivel=Time2.Nivel

LEFT JOIN
( -- Produced Shit-tier
 SELECT * FROM
 (
    SELECT
    phe.JOB_Number,phe.DATE AS Fecha,phe.Part_No, phe.Qty, phe.Nivel, MAX(ohab.Secuencia) Secuencia
    FROM PH_Entries phe
    INNER JOIN I_OPERS_HABILITADO ohab ON ohab.JOB_Number = phe.JOB_Number AND ohab.Nivel = phe.Nivel AND ohab.Oper_Id = phe.Oper_Id
    WHERE phe.DATE BETWEEN '2012-01-28' AND '2012-02-03' AND ohab.Oper_Id<>'NOCUT' AND phe.job_number=21088
    GROUP BY phe.JOB_Number,phe.DATE,phe.Part_No,phe.Nivel, phe.Qty
    --ORDER BY Part_No
 ) AS DtProd
 PIVOT(SUM(Qty) FOR Fecha IN ([2012-01-28],[2012-01-30],[2012-01-31],[2012-02-01],[2012-02-02],[2012-02-03]) )
 AS DtProd2
) AS DateProd ON DateProd.JOB_Number=Time2.JOB_Number AND DateProd.Nivel=Time2.Nivel AND DateProd.Secuencia = DateI.Secuencia
WHERE time2.job_number=21088
ORDER BY Name,Time2.Job,Time2.Phase,Time2.Part_Number,Time2.Part_No

--select * from ph_master where job_number=21088 and no_master=1205 and fecha between '2012-01-28' and '2012-02-03' and nivel=1