Estimated / Actual production time per piece

This report calculates and compares the average planned (estimated) versus actual production time per unit for different materials over the past two months. The data is grouped by material code and provides two key time frames:

One Month Ago: It shows the average actual production time per unit compared to the estimated time per unit for the most recent month.
Two Months Ago: Similarly, it displays the same comparison for the month before the most recent one.

For each material, the report presents these comparisons in the format: "estimated time / actual time". This allows for quick identification of discrepancies between planned and actual production times over the past two months, helping to monitor production efficiency and planning accuracy.

Oracle

SQL

SELECT 
    material.code AS "Material",
    ROUND(
        COALESCE(SUM(CASE 
                WHEN declaration_history.time_created >= ADD_MONTHS(CURRENT_TIMESTAMP, -1) 
                     AND declaration_history.time_created < CURRENT_TIMESTAMP THEN declaration_history.time_actual_duration
                ELSE 0 
            END) / NULLIF(SUM(CASE 
                WHEN declaration_history.time_created >= ADD_MONTHS(CURRENT_TIMESTAMP, -1) 
                     AND declaration_history.time_created < CURRENT_TIMESTAMP THEN declaration_history.declared_qty 
                ELSE 0 
            END), 0), 0)
    ) 
    || ' / ' ||
    ROUND(
        COALESCE(SUM(CASE 
                WHEN declaration_history.time_created >= ADD_MONTHS(CURRENT_TIMESTAMP, -1) 
                     AND declaration_history.time_created < CURRENT_TIMESTAMP THEN declaration_history.time_estimated_duration
                ELSE 0 
            END) / NULLIF(SUM(CASE 
                WHEN declaration_history.time_created >= ADD_MONTHS(CURRENT_TIMESTAMP, -1) 
                     AND declaration_history.time_created < CURRENT_TIMESTAMP THEN declaration_history.declared_qty 
                ELSE 0 
            END), 0), 0)
    )
    AS "Avg Time per unit 1 month ago (estimated/actual)",
    
    ROUND(
        COALESCE(SUM(CASE 
                WHEN declaration_history.time_created >= ADD_MONTHS(CURRENT_TIMESTAMP, -2) 
                     AND declaration_history.time_created < ADD_MONTHS(CURRENT_TIMESTAMP, -1) THEN declaration_history.time_actual_duration
                ELSE 0 
            END) / NULLIF(SUM(CASE 
                WHEN declaration_history.time_created >= ADD_MONTHS(CURRENT_TIMESTAMP, -2) 
                     AND declaration_history.time_created < ADD_MONTHS(CURRENT_TIMESTAMP, -1) THEN declaration_history.declared_qty 
                ELSE 0 
            END), 0), 0)
    ) 
    || ' / ' ||
    ROUND(
        COALESCE(SUM(CASE 
                WHEN declaration_history.time_created >= ADD_MONTHS(CURRENT_TIMESTAMP, -2) 
                     AND declaration_history.time_created < ADD_MONTHS(CURRENT_TIMESTAMP, -1) THEN declaration_history.time_estimated_duration
                ELSE 0 
            END) / NULLIF(SUM(CASE 
                WHEN declaration_history.time_created >= ADD_MONTHS(CURRENT_TIMESTAMP, -2) 
                     AND declaration_history.time_created < ADD_MONTHS(CURRENT_TIMESTAMP, -1) THEN declaration_history.declared_qty 
                ELSE 0 
            END), 0), 0)
    )
    AS "Avg Time per unit 2 months ago (estimated/actual)"

    
FROM declaration_history
JOIN material ON material.id = declaration_history.id_material
WHERE declaration_history.time_created >= ADD_MONTHS(CURRENT_TIMESTAMP, -3) 
GROUP BY material.code

MSSQL

SQL

SELECT 
    material.code AS [Material],
    
    -- Avg Time per unit 1 month ago (estimated/actual)
    CAST(
        ROUND(
            COALESCE(
                SUM(CASE 
                    WHEN declaration_history.time_created >= DATEADD(MONTH, -1, GETDATE()) 
                         AND declaration_history.time_created < GETDATE() 
                    THEN declaration_history.time_actual_duration 
                    ELSE 0 
                END) * 1.0 / NULLIF(
                    SUM(CASE 
                        WHEN declaration_history.time_created >= DATEADD(MONTH, -1, GETDATE()) 
                             AND declaration_history.time_created < GETDATE() 
                        THEN declaration_history.declared_qty 
                        ELSE 0 
                    END), 0
                ), 0
            ), 2
        ) AS VARCHAR(20)
    ) 
    + ' / ' + 
    CAST(
        ROUND(
            COALESCE(
                SUM(CASE 
                    WHEN declaration_history.time_created >= DATEADD(MONTH, -1, GETDATE()) 
                         AND declaration_history.time_created < GETDATE() 
                    THEN declaration_history.time_estimated_duration 
                    ELSE 0 
                END) * 1.0 / NULLIF(
                    SUM(CASE 
                        WHEN declaration_history.time_created >= DATEADD(MONTH, -1, GETDATE()) 
                             AND declaration_history.time_created < GETDATE() 
                        THEN declaration_history.declared_qty 
                        ELSE 0 
                    END), 0
                ), 0
            ), 2
        ) AS VARCHAR(20)
    ) AS [Avg Time per unit 1 month ago (estimated/actual)],
    
    -- Avg Time per unit 2 months ago (estimated/actual)
    CAST(
        ROUND(
            COALESCE(
                SUM(CASE 
                    WHEN declaration_history.time_created >= DATEADD(MONTH, -2, GETDATE()) 
                         AND declaration_history.time_created < DATEADD(MONTH, -1, GETDATE()) 
                    THEN declaration_history.time_actual_duration 
                    ELSE 0 
                END) * 1.0 / NULLIF(
                    SUM(CASE 
                        WHEN declaration_history.time_created >= DATEADD(MONTH, -2, GETDATE()) 
                             AND declaration_history.time_created < DATEADD(MONTH, -1, GETDATE()) 
                        THEN declaration_history.declared_qty 
                        ELSE 0 
                    END), 0
                ), 0
            ), 2
        ) AS VARCHAR(20)
    ) 
    + ' / ' + 
    CAST(
        ROUND(
            COALESCE(
                SUM(CASE 
                    WHEN declaration_history.time_created >= DATEADD(MONTH, -2, GETDATE()) 
                         AND declaration_history.time_created < DATEADD(MONTH, -1, GETDATE()) 
                    THEN declaration_history.time_estimated_duration 
                    ELSE 0 
                END) * 1.0 / NULLIF(
                    SUM(CASE 
                        WHEN declaration_history.time_created >= DATEADD(MONTH, -2, GETDATE()) 
                             AND declaration_history.time_created < DATEADD(MONTH, -1, GETDATE()) 
                        THEN declaration_history.declared_qty 
                        ELSE 0 
                    END), 0
                ), 0
            ), 2
        ) AS VARCHAR(20)
    ) AS [Avg Time per unit 2 months ago (estimated/actual)]

FROM declaration_history
JOIN material ON material.id = declaration_history.id_material
WHERE declaration_history.time_created >= DATEADD(MONTH, -3, GETDATE())
GROUP BY material.code