overlapping time records from two tables

[miguelfelix93]

Hi all,

I have two tables:

• [production_data]  which has the executed production orders for a line that packs jam.
• [downtime_data] which stores the machine periods of inactivity (not running)

What I'm trying to find is how much downtime is contained within each production order (there can be downtime out of prod orders but I don't care for that one.

Φ = µ - δ = MIN(B,Y)- MAX(A,X)

A <- -> B Duration of Production Event

X <- -> Y Duration of Downtime Event

δ <- -> μ Downtime contained in Production Event

This is an example of production_data table:

prod_start_time	prod_end_time	product_desc	planned_qty	delivered_qty	uom
5/21/2024 6:30:00	5/21/2024 7:18:08	Fig 500g x 12IT	1440	1428	IT
5/21/2024 8:26:14	5/21/2024 10:42:28	Strawberry 200g x 24IT	5600	5279	IT

This is an example of downtime_data table:

dt_start_time	dt_end_time	duration_min	machine_desc
5/21/2024 6:52:24	5/21/2024 6:54:33	2.15	Filler 1
5/21/2024 7:18:08	5/21/2024 8:26:14	68.09999999	Filler 1

the logic I have in mind is something like a for loop:

for i in production_data:

for j in downtime_data:

dt_overlap=  MAX(MIN(dt_end_time, prod_end_time)- MAX(dt_start_time, prod_start_time),0)

prod_order_dt=prod_order_dt + dt_overlap

I just need a new column "prod_order_dt" in the [production_data] table that shows the cummulative sum of the downtimes that overlap with the time range of every process order, but I'm not sure yet if DAX would be the best option (i guess doing this in power query would involve a heavy workload by creating a custom column that invokes a custom function). 

Would any of you be able to help?

[robert199re]

This query assumes that start_time and end_time columns are present in both tables and are of a compatible date-time type. The SUM function calculates the total downtime for each production order by adding up the overlapping intervals. The CASE statement checks if the downtime period falls within the production order’s time frame and calculates the overlapping time. The LEFT JOIN ensures that all production orders are listed, even if there’s no corresponding downtime.                                                                           iadpworkforcenow.com

SELECT
p.order_id,
p.start_time,
p.end_time,
SUM(CASE
WHEN d.start_time BETWEEN p.start_time AND p.end_time THEN
LEAST(p.end_time, d.end_time) - GREATEST(p.start_time, d.start_time)
ELSE 0
END) AS downtime
FROM
production_data p
LEFT JOIN
downtime_data d
ON
d.start_time <= p.end_time AND d.end_time >= p.start_time
GROUP BY
p.order_id, p.start_time, p.end_time;