DECLARE @start_date AS SMALLDATETIME = '04/01/2019 06:00:00'
, @end_date AS SMALLDATETIME = '04/08/2019 05:59:00'

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    --DECLARE @start_date AS SMALLDATETIME = '04/02/19 06:00:00'
    --      ,@end_date AS SMALLDATETIME = '04/03/19 05:59:00'

    DECLARE @shots TABLE(equipment_location_key NVARCHAR(50), equipment_location NVARCHAR(50), operation NVARCHAR(50), shif_shift_key NVARCHAR(50), shift NVARCHAR(50), scoe_equi_equipment_key NVARCHAR(50), wheel_style NVARCHAR(50)
                        , scoe_whst_wheel_style_part_number NVARCHAR(50), --Mold_Component NVARCHAR(50),
                         Samples NVARCHAR(50)--, Rejected NVARCHAR(50)
                         )

    ;WITH Shots_Scrap_cte AS (

    SELECT equipment_location_key, equipment_location, operation , shif_shift_key, shift,scoe_equi_equipment_key, ISNULL(wheel_style, 'ND') as 'wheel_style',scoe_whst_wheel_style_part_number, --ISNULL(string1, 'N/A') AS 'Mold_Component',
    CASE WHEN opre_result_id = 'PRO' THEN SUM(quantity) ELSE 0 END AS 'Samples'
    --,CASE WHEN opre_result_id = 'SCR' THEN SUM(quantity) ELSE 0 END AS 'Rejected'
    FROM collect_schemas_operations_equipments LEFT JOIN equipments ON scoe_equi_equipment_key = equipment_key
    LEFT JOIN equipment_locations ON eqlo_equipment_location_key = equipment_location_key
    LEFT JOIN operations ON opre_operation_key = operation_key
    LEFT JOIN shifts ON shif_shift_key = shift_key
    LEFT JOIN wheel_styles ON scoe_whst_wheel_style_part_number = wheel_style_part_number
    WHERE effective_date BETWEEN @start_date AND @end_date
    AND record_status = 'ACT'
    AND scoe_operation_key = '015'
    AND scoe_equi_equipment_key <> 'MRB01'
    AND opre_result_id = 'PRO'
    --AND shif_shift_key
    --AND eqlo_equipment_location_key
    --AND scoe_equi_equipment_key
    GROUP BY equipment_location_key, equipment_location, operation , shif_shift_key, shift,scoe_equi_equipment_key, ISNULL(wheel_style, 'ND'),scoe_whst_wheel_style_part_number, string1, opre_result_id

    )

    INSERT INTO @shots
    SELECT equipment_location_key, equipment_location, operation, shif_shift_key, shift, scoe_equi_equipment_key, wheel_style, scoe_whst_wheel_style_part_number, --Mold_Component,
     SUM(Samples) AS 'Samples'--, SUM(Rejected) AS 'Rejected'
    FROM Shots_Scrap_cte
    GROUP BY equipment_location_key, equipment_location, operation, shif_shift_key, shift, scoe_equi_equipment_key, wheel_style, scoe_whst_wheel_style_part_number--, Mold_Component




    DECLARE @defects TABLE(equipment_location NVARCHAR(50), equipment_key NVARCHAR(50), wheel_style NVARCHAR(50), wheel_style_part_number NVARCHAR(50),
                                mold_component NVARCHAR(50),
                                picker_status NVARCHAR(50), defect_key NVARCHAR(50), subdefect_key NVARCHAR(50), defect_location NVARCHAR(50), operation_key NVARCHAR(50), operation NVARCHAR(50),
                                shift_key NVARCHAR(50), shift NVARCHAR(50), Quantity INT)

    INSERT INTO @defects
    SELECT
        EL.equipment_location as equipment_location, CC.scoe_equi_equipment_key as equipment_key, WS.wheel_style, CC.scoe_whst_wheel_style_part_number as wheel_style_part_number, CC.string1 as mold_component,
        CC.opre_result_id AS picker_status, CC.defe_defect_key as defect_key, CC.deds_desu_defect_subcode_key, CC.dedl_delo_defect_location_key, CC.scoe_operation_key AS operation_key
        , O.operation, CC.shif_shift_key, S.shift, SUM(CC.quantity) AS 'Quantity' --, CCC.shots AS shots
    FROM collect_schemas_operations_equipments CC
        LEFT JOIN racks_collect R ON R.csoe_collect_number = CC.collect_number
        LEFT JOIN equipments E ON E.equipment_key=CC.scoe_equi_equipment_key
        LEFT JOIN equipment_locations EL ON EL.equipment_location_key=E.eqlo_equipment_location_key
        LEFT JOIN wheel_styles WS ON WS.wheel_style_part_number=CC.scoe_whst_wheel_style_part_number
        LEFT JOIN operations O ON O.operation_key=CC.scoe_operation_key
        LEFT JOIN shifts S ON S.shift_key=CC.shif_shift_key
    WHERE
        CC.opre_result_id='SCR'
        AND CC.record_status='ACT'
        AND CC.effective_date BETWEEN @start_date AND @end_date
        AND CC.scoe_equi_equipment_key<>'MRB01' AND CC.scoe_operation_key='015'
    GROUP BY EL.equipment_location, CC.scoe_equi_equipment_key, WS.wheel_style, CC.scoe_whst_wheel_style_part_number, CC.string1,
        CC.opre_result_id, CC.defe_defect_key, CC.deds_desu_defect_subcode_key, CC.dedl_delo_defect_location_key, CC.scoe_operation_key
        , O.operation, CC.shif_shift_key, S.shift

    --select * from @pickers_table WHERE equipment_key='CM01';


    ;WITH cte_defects_by_wheel AS (
        SELECT DISTINCT D.equipment_location, D.operation, D.equipment_key, D.wheel_style, D.wheel_style_part_number, D.mold_component, D.quantity,-- D.shots,
                D.defect_key, D.subdefect_key, D.defect_location, D.shift_key,
            STUFF(
            (SELECT DISTINCT  ', ' + D1.defect_key
                    FROM @defects D1
                    WHERE D.wheel_style_part_number = D1.wheel_style_part_number
                        AND D.equipment_key = D1.equipment_key
                        AND D.operation_key = D1.operation_key
                        AND D.shift_key = D1.shift_key
                        AND D.mold_component = D1.mold_component
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)')
                ,1,1,'') defects
        FROM @defects D
    )


    SELECT --S.*, D.defects
        S.equipment_location_key, S.equipment_location, S.operation, S.shif_shift_key AS shift_key, S.shift, S.scoe_equi_equipment_key AS equipment_key,
        S.wheel_style, S.scoe_whst_wheel_style_part_number AS wheel_style_part_number,-- S.mold_component,
        S.samples, ISNULL(D.Quantity, 0) as 'rejected', ISNULL(D.mold_component, 'N/A') AS 'mold_component', D.defects AS found_defects
    FROM @shots S
    LEFT JOIN (
        SELECT equipment_location, operation, equipment_key, wheel_style_part_number, defects, shift_key, Quantity, mold_component--, ISNULL(mold_component, 'N/A') AS mold_component
        FROM cte_defects_by_wheel
        --WHERE equipment_key='CM01'
        ) D
            ON S.equipment_location=D.equipment_location AND S.operation=D.operation AND S.scoe_equi_equipment_key=D.equipment_key
                AND S.scoe_whst_wheel_style_part_number=D.wheel_style_part_number
                AND S.shif_shift_key=D.shift_key /*AND S.Mold_Component=D.mold_component*//*AND S.wheel_style=D.wheel_style */
    --WHERE --scoe_equi_equipment_key='CM09'
    --equipment_location_key='DK1'

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED