I would like to write a query that returns the total amount of time over a specified period that a particular tool was in use. The records I have show when specific jobs using that tool started and stopped. I can't just add up these times, however, because sometimes the jobs overlap in time, e.g. in the case where a tool has a large capacity and can work on more than one part at a time.
My table is like this:
Given Tool_ID and a Period_Begin_DT and Period_End_DT I would like the query to return the total amount of time the tool was in use.
The best solution I could come up with was to create another table with a row for each slice of time between Period_Begin_DT and Period_End_DT (my slices were 6minutes). I then created a query that counted how many times each slice was used, and then converted all non-zero numbers to 1 and summed up the slices used. This kinda sorts works, but it takes a long time to run and is only as accurate as my time slices.
Any better ideas?