Production Item - time in redbox
This report provides a detailed overview of the duration that production items spend in a Redbox, tracking their entry and exit times.
Key Components:
ID and Time Information:
ID: Unique identifier for each production item.
Time in: Timestamp when the production item entered the "Redbox" status.
Time out: Timestamp when the production item exited the "Redbox" status.
Formatted Duration:
Time in Redbox (dd hh:mm
): Shows the duration the item spent in the "Redbox" status formatted in days, hours, minutes, and seconds. If the item is still in "Redbox," this will indicate the duration up to the current time.
Time in Redbox - Technical: Displays the duration in a more compact format (days, hours, minutes, and seconds) for technical purposes. This format also handles cases where the item is still in the "Redbox" status.
The report helps track how long each production item remains in the "Redbox" status. This can be useful for monitoring production processes, identifying bottlenecks, and improving operational efficiency. By providing both human-readable and technical time formats, the report accommodates different needs for analysis and reporting.
Oracle
WITH StatusChanges AS (
SELECT
id_entity,
time_change,
id_origin_status,
id_new_status,
LEAD(time_change) OVER (PARTITION BY id_entity ORDER BY time_change) AS next_time_change
FROM
entity_status_change
WHERE
entity = 'ProductionItem'
AND (id_new_status = 5 OR id_origin_status = 5)
),
StatusDuration AS (
SELECT
id_entity,
time_change AS time_in,
next_time_change AS time_out,
CASE
WHEN id_new_status = 5 THEN 'In'
WHEN id_origin_status = 5 THEN 'Out'
END AS status_change
FROM
StatusChanges
),
FormattedDurations AS (
SELECT
id_entity,
time_in,
time_out,
CASE
WHEN status_change = 'In' AND time_out IS NOT NULL THEN
TRIM(
CASE
WHEN EXTRACT(DAY FROM time_out - time_in) > 0 THEN
TO_CHAR(EXTRACT(DAY FROM time_out - time_in)) || ' dd '
ELSE ''
END ||
LPAD(EXTRACT(HOUR FROM time_out - time_in), 2, '0') || ':' ||
LPAD(EXTRACT(MINUTE FROM time_out - time_in), 2, '0') || ':' ||
LPAD(ROUND(EXTRACT(SECOND FROM time_out - time_in), 2), 5, '0')
) || ' (in Redbox)'
WHEN status_change = 'In' AND time_out IS NULL THEN
'Still in Redbox - currently: ' ||
TRIM(
CASE
WHEN EXTRACT(DAY FROM SYSTIMESTAMP - time_in) > 0 THEN
TO_CHAR(EXTRACT(DAY FROM SYSTIMESTAMP - time_in)) || ' dd '
ELSE ''
END ||
LPAD(EXTRACT(HOUR FROM SYSTIMESTAMP - time_in), 2, '0') || ':' ||
LPAD(EXTRACT(MINUTE FROM SYSTIMESTAMP - time_in), 2, '0') || ':' ||
LPAD(ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP - time_in), 2), 5, '0')
)
ELSE
NULL
END AS time_in_redbox_dd_hh_mm_ss,
CASE
WHEN status_change = 'In' AND time_out IS NOT NULL THEN
TRIM(
CASE
WHEN EXTRACT(DAY FROM time_out - time_in) > 0 THEN
TO_CHAR(EXTRACT(DAY FROM time_out - time_in)) || ' '
ELSE '0 '
END ||
LPAD(EXTRACT(HOUR FROM time_out - time_in), 2, '0') || ':' ||
LPAD(EXTRACT(MINUTE FROM time_out - time_in), 2, '0') || ':' ||
LPAD(ROUND(EXTRACT(SECOND FROM time_out - time_in), 2), 5, '0')
)
WHEN status_change = 'In' AND time_out IS NULL THEN
TRIM(
CASE
WHEN EXTRACT(DAY FROM SYSTIMESTAMP - time_in) > 0 THEN
TO_CHAR(EXTRACT(DAY FROM SYSTIMESTAMP - time_in)) || ' '
ELSE '0 '
END ||
LPAD(EXTRACT(HOUR FROM SYSTIMESTAMP - time_in), 2, '0') || ':' ||
LPAD(EXTRACT(MINUTE FROM SYSTIMESTAMP - time_in), 2, '0') || ':' ||
LPAD(ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP - time_in), 2), 5, '0')
)
ELSE
NULL
END AS time_in_redbox_technical
FROM
StatusDuration
WHERE
status_change = 'In'
)
SELECT
id_entity AS "ID",
time_in AS "Time in",
time_out AS "Time out",
time_in_redbox_dd_hh_mm_ss AS "Time in Redbox",
time_in_redbox_technical AS "Time in Redbox - technical"
FROM
FormattedDurations
ORDER BY
time_in DESCMSSQL
WITH StatusChanges AS (
SELECT
id_entity,
time_change,
id_origin_status,
id_new_status,
LEAD(time_change) OVER (PARTITION BY id_entity ORDER BY time_change) AS next_time_change
FROM
entity_status_change
WHERE
entity = 'ProductionItem' AND (id_new_status = 5 OR id_origin_status = 5)
),
StatusDuration AS (
SELECT
id_entity,
time_change AS time_in,
next_time_change AS time_out,
CASE
WHEN id_new_status = 5 THEN 'In'
WHEN id_origin_status = 5 THEN 'Out'
END AS status_change
FROM
StatusChanges
),
FormattedDurations AS (
SELECT
id_entity,
time_in,
time_out,
CASE
WHEN status_change = 'In' AND time_out IS NOT NULL THEN
TRIM(
COALESCE(
NULLIF(
CAST(DATEDIFF(DAY, time_in, time_out) AS VARCHAR(10)) + ' dd ',
''
) +
RIGHT('0' + CAST(DATEPART(HOUR, DATEADD(SECOND, DATEDIFF(SECOND, time_in, time_out), 0)) AS VARCHAR(2)), 2) + ':' +
RIGHT('0' + CAST(DATEPART(MINUTE, DATEADD(SECOND, DATEDIFF(SECOND, time_in, time_out), 0)) AS VARCHAR(2)), 2) + ':' +
RIGHT('0' + CAST(DATEPART(SECOND, DATEADD(SECOND, DATEDIFF(SECOND, time_in, time_out), 0)) AS VARCHAR(2)), 2),
'0:00:00'
)
)
WHEN status_change = 'In' AND time_out IS NULL THEN
'Still in Redbox - currently: ' + TRIM(
COALESCE(
NULLIF(
CAST(DATEDIFF(DAY, time_in, SYSDATETIME()) AS VARCHAR(10)) + ' dd ',
''
) +
RIGHT('0' + CAST(DATEPART(HOUR, DATEADD(SECOND, DATEDIFF(SECOND, time_in, SYSDATETIME()), 0)) AS VARCHAR(2)), 2) + ':' +
RIGHT('0' + CAST(DATEPART(MINUTE, DATEADD(SECOND, DATEDIFF(SECOND, time_in, SYSDATETIME()), 0)) AS VARCHAR(2)), 2) + ':' +
RIGHT('0' + CAST(DATEPART(SECOND, DATEADD(SECOND, DATEDIFF(SECOND, time_in, SYSDATETIME()), 0)) AS VARCHAR(2)), 2),
'0:00:00'
)
)
ELSE
NULL
END AS time_in_redbox_technical
FROM
StatusDuration
WHERE
status_change = 'In'
)
SELECT
id_entity AS "ID",
time_in AS "Time in",
time_out AS "Time out",
time_in_redbox_technical AS "Time in Redbox - technical"
FROM
FormattedDurations
ORDER BY
time_in DESC