Spare parts consumption

This report provides a detailed overview of spare parts consumption during maintenance activities. It captures essential information such as the ID and code of the spare part, the exact time when the part was consumed, and the quantity used. Additionally, it identifies the specific equipment where the spare part was utilized, including the equipment's ID and number.

The report also links the spare part consumption to the corresponding maintenance order, providing both the ID and description of the order. Finally, it identifies the technician responsible for the maintenance, including their ID and full name. The data is organized chronologically, with the most recent consumption records displayed first.

Oracle

SQL

WITH spare_part_data AS (
    SELECT
        material.id AS "Spare Part ID",
        material.code AS "Spare Part Code",
        completion_confirmation.finish_system_time AS "Consumption Time",
        complet_confirmation_his.spare_part_quantity AS "Consumed Spare Part quantity",
        equipment.equipment_number AS "By what Equipment it was consumed by",
        equipment.id AS "Equipment ID",
        maintenance_order.id AS "Maintenance Order ID",
        maintenance_order.description AS "Maintenance Order Description",
        completion_confirmation.id AS "Completion Confirmation ID",
        complet_confirmation_his.id_user AS "Technician ID",
        u."name" ||' '|| u.lastname AS "Technician"
    FROM material
    JOIN complet_confirmation_his ON complet_confirmation_his.id_spare_part = material.id
    JOIN completion_confirmation ON completion_confirmation.id = complet_confirmation_his.id_completion_confirmation
    JOIN maintenance_order ON maintenance_order.id = completion_confirmation.id_maintenance_order
    JOIN equipment ON equipment.id = maintenance_order.id_equipment
    JOIN "user" u ON u.id = complet_confirmation_his.id_user
    WHERE completion_confirmation.finish_system_time is not null
)
SELECT * FROM spare_part_data
ORDER BY "Consumption Time" DESC

MSSQL

SQL

WITH spare_part_data AS (
    SELECT
        material.id AS [Spare Part ID],
        material.code AS [Spare Part Code],
        completion_confirmation.finish_system_time AS [Consumption Time],
        complet_confirmation_his.spare_part_quantity AS [Consumed Spare Part quantity],
        equipment.equipment_number AS [By what Equipment it was consumed by],
        equipment.id AS [Equipment ID],
        maintenance_order.id AS [Maintenance Order ID],
        maintenance_order.description AS [Maintenance Order Description],
        completion_confirmation.id AS [Completion Confirmation ID],
        complet_confirmation_his.id_user AS [Technician ID],
        u.[name] + ' ' + u.lastname AS [Technician]
    FROM material
    JOIN complet_confirmation_his ON complet_confirmation_his.id_spare_part = material.id
    JOIN completion_confirmation ON completion_confirmation.id = complet_confirmation_his.id_completion_confirmation
    JOIN maintenance_order ON maintenance_order.id = completion_confirmation.id_maintenance_order
    JOIN equipment ON equipment.id = maintenance_order.id_equipment
    JOIN [user] u ON u.id = complet_confirmation_his.id_user
    WHERE completion_confirmation.finish_system_time IS NOT NULL
)
SELECT * 
FROM spare_part_data
ORDER BY [Consumption Time] DESC