Declared / Demanded quantity

This report provides a comparison between the quantity of materials that were produced (Declared) and the quantity that was required or demanded (ordered by customers) over the last three months.

Key Information:

Material Code:
- The report starts by listing the unique code for each material.
Declared vs. Demanded Quantities:
- For each material, the report shows a comparison of the declared (produced) quantity versus the demanded (ordered) quantity over the past three months.
- The quantities are shown side by side in the format: "Declared Quantity / Demanded Quantity."
Time Periods:
- The comparison is broken down into three time periods:
  - 1 Month Ago: The declared and demanded quantities within the last month.
  - 2 Months Ago: The declared and demanded quantities from two months ago.
  - 3 Months Ago: The declared and demanded quantities from three months ago.

This report is useful for understanding how well production has met the demand for each material over time. It can help identify discrepancies where production may be falling short of or exceeding the required quantities. By reviewing these trends, you can make more informed decisions about production planning and inventory management.

Oracle

SQL

SELECT 
    material.code AS "Material",
    COALESCE(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)
    || ' / ' ||
    COALESCE(SUM(CASE 
                WHEN outbound_delivery_material.REQUIRED_FINISH_TIME >= ADD_MONTHS(CURRENT_TIMESTAMP, -1) 
                     AND outbound_delivery_material.REQUIRED_FINISH_TIME < CURRENT_TIMESTAMP THEN outbound_delivery_material.QUANTITY 
                ELSE 0 
            END), 0)
    AS "Declared/Demanded quantity 1 month ago",
    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.declared_qty 
                ELSE 0 
            END), 0)
    || ' / ' ||
    COALESCE(SUM(CASE 
                WHEN outbound_delivery_material.REQUIRED_FINISH_TIME >= ADD_MONTHS(CURRENT_TIMESTAMP, -2) 
                     AND outbound_delivery_material.REQUIRED_FINISH_TIME < ADD_MONTHS(CURRENT_TIMESTAMP, -1) THEN outbound_delivery_material.QUANTITY 
                ELSE 0 
            END), 0)
    AS "Declared/Demanded quantity 2 months ago",
    COALESCE(SUM(CASE 
                WHEN declaration_history.time_created >= ADD_MONTHS(CURRENT_TIMESTAMP, -3) 
                     AND declaration_history.time_created < ADD_MONTHS(CURRENT_TIMESTAMP, -2) THEN declaration_history.declared_qty 
                ELSE 0 
            END), 0)
    || ' / ' ||
    COALESCE(SUM(CASE 
                WHEN outbound_delivery_material.REQUIRED_FINISH_TIME >= ADD_MONTHS(CURRENT_TIMESTAMP, -3) 
                     AND outbound_delivery_material.REQUIRED_FINISH_TIME < ADD_MONTHS(CURRENT_TIMESTAMP, -2) THEN outbound_delivery_material.QUANTITY 
                ELSE 0 
            END), 0)
    AS "Declared/Demanded quantity 3 months ago"
FROM declaration_history
JOIN production_item ON production_item.id = declaration_history.id_production_item
JOIN material ON material.id = production_item.id_material
LEFT JOIN outbound_delivery_material ON material.id = outbound_delivery_material.ID_MATERIAL
WHERE declaration_history.time_created >= ADD_MONTHS(CURRENT_TIMESTAMP, -3)
GROUP BY material.code

MSSQL

SQL

SELECT 
    material.code AS Material,
    
    CAST(COALESCE(SUM(CASE 
                WHEN declaration_history.time_created >= DATEADD(MONTH, -1, CURRENT_TIMESTAMP) 
                     AND declaration_history.time_created < CURRENT_TIMESTAMP THEN declaration_history.declared_qty 
                ELSE 0 
            END), 0) AS VARCHAR(50))
    + ' / ' +
    CAST(COALESCE(SUM(CASE 
                WHEN outbound_delivery_material.REQUIRED_FINISH_TIME >= DATEADD(MONTH, -1, CURRENT_TIMESTAMP) 
                     AND outbound_delivery_material.REQUIRED_FINISH_TIME < CURRENT_TIMESTAMP THEN outbound_delivery_material.QUANTITY 
                ELSE 0 
            END), 0) AS VARCHAR(50))
    AS [Declared/Demanded quantity 1 month ago],
    
    CAST(COALESCE(SUM(CASE 
                WHEN declaration_history.time_created >= DATEADD(MONTH, -2, CURRENT_TIMESTAMP) 
                     AND declaration_history.time_created < DATEADD(MONTH, -1, CURRENT_TIMESTAMP) THEN declaration_history.declared_qty 
                ELSE 0 
            END), 0) AS VARCHAR(50))
    + ' / ' +
    CAST(COALESCE(SUM(CASE 
                WHEN outbound_delivery_material.REQUIRED_FINISH_TIME >= DATEADD(MONTH, -2, CURRENT_TIMESTAMP) 
                     AND outbound_delivery_material.REQUIRED_FINISH_TIME < DATEADD(MONTH, -1, CURRENT_TIMESTAMP) THEN outbound_delivery_material.QUANTITY 
                ELSE 0 
            END), 0) AS VARCHAR(50))
    AS [Declared/Demanded quantity 2 months ago],

    CAST(COALESCE(SUM(CASE 
                WHEN declaration_history.time_created >= DATEADD(MONTH, -3, CURRENT_TIMESTAMP) 
                     AND declaration_history.time_created < DATEADD(MONTH, -2, CURRENT_TIMESTAMP) THEN declaration_history.declared_qty 
                ELSE 0 
            END), 0) AS VARCHAR(50))
    + ' / ' +
    CAST(COALESCE(SUM(CASE 
                WHEN outbound_delivery_material.REQUIRED_FINISH_TIME >= DATEADD(MONTH, -3, CURRENT_TIMESTAMP) 
                     AND outbound_delivery_material.REQUIRED_FINISH_TIME < DATEADD(MONTH, -2, CURRENT_TIMESTAMP) THEN outbound_delivery_material.QUANTITY 
                ELSE 0 
            END), 0) AS VARCHAR(50))
    AS [Declared/Demanded quantity 3 months ago]
    
FROM declaration_history
JOIN production_item ON production_item.id = declaration_history.id_production_item
JOIN material ON material.id = production_item.id_material
LEFT JOIN outbound_delivery_material ON material.id = outbound_delivery_material.ID_MATERIAL
WHERE declaration_history.time_created >= DATEADD(MONTH, -3, CURRENT_TIMESTAMP)
GROUP BY material.code