Production Lead Time
This report calculates and displays the production lead time for each production item by tracking the time between when the item was first assigned to production and when it was first declared as completed.
Key Details:
Item Assignment:
The report identifies the first time each production item was assigned a status of "Assigned." This is recorded as the "Time assigned."
First Declaration:
It also tracks the first time the production item was declared (first produced piece), capturing this moment as the "Time first declaration."
Lead Time Calculation:
The report calculates the production lead time, which is the difference between the "Time assigned" and the "Time first declaration."
This lead time is presented in two formats:
Human-Readable Format: Expressed in days, hours, minutes, and seconds, with unnecessary units omitted.
Seconds: The lead time is also calculated in total seconds for precise analysis and easier computation.
Data Ordering:
The results are sorted by the time of assignment in descending order, meaning the most recently assigned production items are listed first.
For large DBs it might be necessary to limit the number of rows loaded.
Oracle
SQL
WITH FirstAssigned AS (
SELECT
esc.ID_ENTITY AS ID_PRODUCTION_ITEM,
esc.TIME_CREATED AS TIME_ASSIGNED,
ROW_NUMBER() OVER (PARTITION BY esc.ID_ENTITY
ORDER BY esc.TIME_CHANGE ASC) AS rn
FROM
entity_status_change esc
JOIN
status so ON so.id = esc.id_origin_status
JOIN
status sn ON sn.id = esc.id_new_status
WHERE
esc.entity = 'ProductionItem'
AND esc.ID_NEW_STATUS IN (SELECT s.ID FROM status s WHERE s."name" = 'assigned')
),
FirstDeclaration AS (
SELECT
dh.ID_PRODUCTION_ITEM,
dh.material_code,
dh.TIME_CREATED AS TIME_DECLARED,
ROW_NUMBER() OVER (PARTITION BY dh.ID_PRODUCTION_ITEM
ORDER BY dh.TIME_CREATED ASC) AS rn
FROM
declaration_history dh
)
SELECT
fa.ID_PRODUCTION_ITEM AS "Item number",
fd.MATERIAL_CODE AS "Material Code",
fa.TIME_ASSIGNED AS "Time assigned",
fd.TIME_DECLARED AS "Time first declaration",
-- Výpočet rozdílu v dd hh mm ss formátu s odstraňováním nul
TRIM(
CASE
WHEN EXTRACT(DAY FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) > 0 THEN EXTRACT(DAY FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) || ' days '
ELSE ''
END ||
CASE
WHEN EXTRACT(HOUR FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) > 0 OR EXTRACT(DAY FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) > 0 THEN
LPAD(EXTRACT(HOUR FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)), 2, '0') || ' hours '
ELSE ''
END ||
CASE
WHEN EXTRACT(MINUTE FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) > 0 OR EXTRACT(HOUR FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) > 0 OR EXTRACT(DAY FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) > 0 THEN
LPAD(EXTRACT(MINUTE FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)), 2, '0') || ' minutes '
ELSE ''
END ||
CASE
WHEN EXTRACT(SECOND FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) > 0 OR EXTRACT(MINUTE FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) > 0 OR EXTRACT(HOUR FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) > 0 OR EXTRACT(DAY FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) > 0 THEN
LPAD(EXTRACT(SECOND FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)), 2, '0') || ' seconds'
ELSE ''
END
) AS "Lead time",
-- Výpočet rozdílu v sekundách zaokrouhlených na celé číslo
ROUND(
EXTRACT(DAY FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) * 86400 +
EXTRACT(HOUR FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) * 3600 +
EXTRACT(MINUTE FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED)) * 60 +
EXTRACT(SECOND FROM (fd.TIME_DECLARED - fa.TIME_ASSIGNED))
) AS "Lead time in seconds"
FROM
FirstAssigned fa
JOIN
FirstDeclaration fd ON fa.ID_PRODUCTION_ITEM = fd.ID_PRODUCTION_ITEM
WHERE
fa.rn = 1
AND fd.rn = 1
ORDER BY
fa.TIME_ASSIGNED DESC
--FETCH FIRST 3000 ROWS ONLY;MSSQL
SQL
WITH FirstAssigned AS (
SELECT
esc.ID_ENTITY AS ID_PRODUCTION_ITEM,
esc.TIME_CREATED AS TIME_ASSIGNED,
ROW_NUMBER() OVER (PARTITION BY esc.ID_ENTITY
ORDER BY esc.TIME_CHANGE ASC) AS rn
FROM
entity_status_change esc
JOIN
status so ON so.id = esc.id_origin_status
JOIN
status sn ON sn.id = esc.id_new_status
WHERE
esc.entity = 'ProductionItem'
AND esc.ID_NEW_STATUS IN (SELECT s.ID FROM status s WHERE s."name" = 'assigned')
),
FirstDeclaration AS (
SELECT
dh.ID_PRODUCTION_ITEM,
dh.TIME_CREATED AS TIME_DECLARED,
ROW_NUMBER() OVER (PARTITION BY dh.ID_PRODUCTION_ITEM
ORDER BY dh.TIME_CREATED ASC) AS rn
FROM
declaration_history dh
)
SELECT
TOP 9000
fa.ID_PRODUCTION_ITEM AS [Item number],
fa.TIME_ASSIGNED AS [Time assigned],
fd.TIME_DECLARED AS [Time first declaration],
-- Výpočet rozdílu v dd hh mm ss formátu s odstraňováním nul
CASE
WHEN DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED) > 0 THEN
CAST(DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED) AS VARCHAR) + ' days '
ELSE ''
END +
CASE
WHEN DATEPART(HOUR, DATEADD(DAY, DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED), fa.TIME_ASSIGNED)) > 0 OR
DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED) > 0 THEN
RIGHT('0' + CAST(DATEPART(HOUR, DATEADD(DAY, DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED), fa.TIME_ASSIGNED)) AS VARCHAR), 2) + ' hours '
ELSE ''
END +
CASE
WHEN DATEPART(MINUTE, DATEADD(HOUR, DATEPART(HOUR, DATEADD(DAY, DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED), fa.TIME_ASSIGNED)), fa.TIME_ASSIGNED)) > 0 OR
DATEPART(HOUR, DATEADD(DAY, DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED), fa.TIME_ASSIGNED)) > 0 OR
DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED) > 0 THEN
RIGHT('0' + CAST(DATEPART(MINUTE, DATEADD(HOUR, DATEPART(HOUR, DATEADD(DAY, DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED), fa.TIME_ASSIGNED)), fa.TIME_ASSIGNED)) AS VARCHAR), 2) + ' minutes '
ELSE ''
END +
CASE
WHEN DATEPART(SECOND, DATEADD(MINUTE, DATEPART(MINUTE, DATEADD(HOUR, DATEPART(HOUR, DATEADD(DAY, DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED), fa.TIME_ASSIGNED)), fa.TIME_ASSIGNED)), fa.TIME_ASSIGNED)) > 0 OR
DATEPART(MINUTE, DATEADD(HOUR, DATEPART(HOUR, DATEADD(DAY, DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED), fa.TIME_ASSIGNED)), fa.TIME_ASSIGNED)) > 0 OR
DATEPART(HOUR, DATEADD(DAY, DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED), fa.TIME_ASSIGNED)) > 0 OR
DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED) > 0 THEN
RIGHT('0' + CAST(DATEPART(SECOND, DATEADD(MINUTE, DATEPART(MINUTE, DATEADD(HOUR, DATEPART(HOUR, DATEADD(DAY, DATEDIFF(DAY, fa.TIME_ASSIGNED, fd.TIME_DECLARED), fa.TIME_ASSIGNED)), fa.TIME_ASSIGNED)), fa.TIME_ASSIGNED)) AS VARCHAR), 2) + ' seconds'
ELSE ''
END AS [Lead time],
-- Výpočet rozdílu v sekundách zaokrouhlených na celé číslo
ROUND(
DATEDIFF(SECOND, fa.TIME_ASSIGNED, fd.TIME_DECLARED), 0
) AS [Lead time in seconds]
FROM
FirstAssigned fa
JOIN
FirstDeclaration fd ON fa.ID_PRODUCTION_ITEM = fd.ID_PRODUCTION_ITEM
WHERE
fa.rn = 1
AND fd.rn = 1
ORDER BY
fa.TIME_ASSIGNED DESC