Production Items

SELECT
    pi.number AS "Production Item",
    m.code AS "Material code",
    m.title AS "Material title",
    pi.quantity AS "Expected quantity",
    pi.QUANTITY_SCRAP AS "Scrap qty",
    pi.QUANTITY_FINISH AS "Quantity declared",
    pi.quantity_discard AS "Discarded qty",
    s.TITLE AS "Production status",
    pl.code AS "Production line code",
    pl.title AS "Production line title",
    sh.title AS "Shift template",
    FORMAT(pi.TIME_START at time zone 'UTC' at time zone 'US Mountain Standard Time', 'yyyy-MM-dd HH:mm:ss') AS "Start date",
    FORMAT(pi.TIME_FINISH at time zone 'UTC' at time zone 'US Mountain Standard Time', 'yyyy-MM-dd HH:mm:ss') AS "Finish date",
    FORMAT(pi.required_production_start at time zone 'UTC' at time zone 'US Mountain Standard Time', 'yyyy-MM-dd HH:mm:ss') AS "Required production start",
    FORMAT(pi.TIME_REQUIRED_FINISH at time zone 'UTC' at time zone 'US Mountain Standard Time', 'yyyy-MM-dd HH:mm:ss') AS "Required production finish",
    pi.actual_duration_time,
    LTRIM(
        CASE WHEN pi.actual_duration_time / 86400 > 0 THEN 
            CAST(pi.actual_duration_time / 86400 AS VARCHAR(10)) + ' days ' 
        ELSE '' END +
        CASE WHEN (pi.actual_duration_time % 86400) / 3600 > 0 THEN 
            CAST((pi.actual_duration_time % 86400) / 3600 AS VARCHAR(10)) + ' hr. ' 
        ELSE '' END +
        CASE WHEN (pi.actual_duration_time % 3600) / 60 > 0 THEN 
            CAST((pi.actual_duration_time % 3600) / 60 AS VARCHAR(10)) + ' min. ' 
        ELSE '' END +
        CASE WHEN pi.actual_duration_time % 60 > 0 THEN 
            CAST(pi.actual_duration_time % 60 AS VARCHAR(10)) + ' sec.' 
        ELSE '' END
    ) AS actual_duration_time_formatted,
    pi.production_efficiency "Production efficiency",
    CAST(CAST(ROUND(pi.production_efficiency, 0) AS INT) AS VARCHAR(10)) + ' %' AS "Production efficiency formatted",
    od.DELIVERY_NUMBER AS "Outbound Delivery",
    u.name + ' ' + u.lastname AS "User finished",
    FORMAT(pi.time_finish at time zone 'UTC' at time zone 'US Mountain Standard Time', 'yyyy') AS "Finished_Year",
    FORMAT(pi.time_finish at time zone 'UTC' at time zone 'US Mountain Standard Time', 'MM') AS "Finished_Month",
    FORMAT(pi.time_finish at time zone 'UTC' at time zone 'US Mountain Standard Time', 'dd') AS "Finished_Day",
    mt.title AS "Material type",
    m.department AS "Department",
    pi.id_master_item AS "Master order",
    STUFF((SELECT DISTINCT ', ' + op.name + ' ' + op.surname
       FROM declaration_history dh
       JOIN declaration_history_operator_rel dhr ON dhr.id_declaration_history = dh.id
       JOIN operator op ON op.id = dhr.id_operator
       WHERE dh.id_production_item = pi.id
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS "Operators",
    STUFF((SELECT DISTINCT ', ' + mg.code
       FROM MATERIAL_GROUP_REL mgr
       JOIN MATERIAL_GROUP mg ON mgr.id_group = mg.id
       WHERE mgr.id_material = pi.ID_MATERIAL
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS "Material group"
FROM production_item pi
JOIN material m ON m.id = pi.id_material
JOIN status s ON s.id = pi.ID_STATUS
LEFT JOIN production_line pl ON pl.id = pi.ID_WORKSPACE
LEFT JOIN OUTBOUND_DELIVERY od ON od.id = pi.ID_OUTBOUND_DELIVERY
LEFT JOIN [user] u ON u.id = pi.ID_USER_FINISHED
JOIN MATERIAL_TYPE mt ON mt.id = m.id_type
LEFT JOIN declaration_history dh ON dh.id_production_item = pi.id
LEFT JOIN shift sh ON sh.id = dh.id_shift
ORDER BY pi.time_created DESC;