Finding date gap between two timeframe

user_10621437 0
Viewed 81

I am required to find out the loss duration when the machine is running but not running any load. The machine is able to run several load concurrently which creates a lot of overlapping datetime.

Gantt Chart illustration of my task:

enter image description here

Sample Table Structure

+-----------+------+-------+---------------------+---------------------+-----------+
| Equipment | Type | State |    Tracked_inTIME   |   Tracked_outTIME   | Load_Type |  
+-----------+------+-------+---------------------+---------------------+-----------+
| A1        | MACH | UP    | 2018-11-13 08:00:00 | 2018-11-13 10:00:00 | NULL      |  
| A1        | LOAD | NULL  | 2018-11-13 08:20:00 | 2018-11-13 09:43:00 | PROD      |  
| A1        | LOAD | NULL  | 2018-11-13 08:22:30 | 2018-11-13 09:55:00 | PROD      |  
| A1        | LOAD | NULL  | 2018-11-13 08:30:00 | 2018-11-13 10:11:00 | PROD      | 
| A1        | MACH | DOWN  | 2018-11-13 10:00:00 | 2018-11-13 10:35:00 | NULL      |  
| A1        | MACH | UP    | 2018-11-13 10:35:00 | 2018-11-13 15:11:00 | NULL      |  
| A1        | LOAD | NULL  | 2018-11-13 11:00:00 | 2018-11-13 11:51:00 | PROD      |  
| A1        | LOAD | NULL  | 2018-11-13 11:25:00 | 2018-11-13 11:55:00 | PROD      |  
| A1        | LOAD | NULL  | 2018-11-13 12:05:00 | 2018-11-13 14:11:00 | PROD      |  
| A1        | LOAD | NULL  | 2018-11-13 12:25:00 | 2018-11-13 13:05:00 | PROD      |  
| A1        | LOAD | NULL  | 2018-11-13 13:45:00 | 2018-11-13 15:11:00 | PROD      |  
| A2        | MACH | UP    | 2018-11-13 08:00:00 | 2018-11-13 14:05:00 | NULL      |  
| A2        | LOAD | NULL  | 2018-11-13 08:00:00 | 2018-11-13 14:05:00 | PROD      | 
+-----------+------+-------+---------------------+---------------------+-----------+

Wrong Output (Column: Gapduration = 40 , should be 0 as the machine is still running a load)

    +----+-------+---------------------+---------------------+---------------------+----------------------+-------------+
    | EQ | State |      EQ_inTIME      |     EQ_outTIME      |     LOAD_inTIME     |     LOAD_outTIME     | GapDuration |
    +----+-------+---------------------+---------------------+---------------------+----------------------+-------------+
    | A1 | UP    | 2018-11-13 08:00:00 | 2018-11-13 10:00:00 | 2018-11-13 08:20:00 |  2018-11-13 09:43:00 |          20 |
    | A1 | UP    | 2018-11-13 08:00:00 | 2018-11-13 10:00:00 | 2018-11-13 08:22:30 |  2018-11-13 09:55:00 |           0 |
    | A1 | UP    | 2018-11-13 08:00:00 | 2018-11-13 10:00:00 | 2018-11-13 08:30:00 |  2018-11-13 10:11:00 |           0 |
    | A1 | DOWN  | 2018-11-13 10:00:00 | 2018-11-13 10:35:00 | 2018-11-13 08:30:00 |  2018-11-13 10:11:00 |           0 |
    | A1 | UP    | 2018-11-13 10:35:00 | 2018-11-13 15:11:00 | 2018-11-13 11:00:00 |  2018-11-13 11:51:00 |          25 |
    | A1 | UP    | 2018-11-13 10:35:00 | 2018-11-13 15:11:00 | 2018-11-13 11:25:00 |  2018-11-13 11:55:00 |           0 |
    | A1 | UP    | 2018-11-13 10:35:00 | 2018-11-13 15:11:00 | 2018-11-13 12:05:00 | *2018-11-13 14:11:00 |          10 |
    | A1 | UP    | 2018-11-13 10:35:00 | 2018-11-13 15:11:00 | 2018-11-13 12:25:00 |  2018-11-13 13:05:00 |           0 |
    | A1 | UP    | 2018-11-13 10:35:00 | 2018-11-13 15:11:00 | 2018-11-13 13:45:00 |  2018-11-13 15:11:00 |          40 |
    | A2 | UP    | 2018-11-13 08:00:00 | 2018-11-13 14:05:00 | 2018-11-13 08:00:00 |  2018-11-13 14:05:00 |           0 |
    +----+-------+---------------------+---------------------+---------------------+----------------------+-------------+
  • Output was derived by doing a self join to bind the load time frame to equipment state.
  • The load_inTIME at the first row of every equipment state change is compared with the equipment_inTIME

  • Subsequent rows of load_inTIME are compared to the previous load_outTIME using the lag() function

  • The main problem that I had was that there was a load timeframe (marked with *) which spans over multiple load duration that my calculation did not manage to account for

Any suggestions?

sql-server-2012 tsql sql-server sql
1 Answers

Almost answered too quickly. Sorry if the other answer gets through. I didn't see your comments below the 2nd table.

Soooo, I think you need to modify your table a bit, not to change it, but get an interim dataset to work with (CTE/temp table/etc...). You need to get your data to represent what you're seeing on your chart with regard to the large black lines that span top to bottom across both load and mach timelines.

Essentially, you should have one contiguous series of time represented. This is what the large black lines are asking for. When load start/stops, those times should be in series with your mach up/down times. For every record you compare the current records "Tracked_outTIME" against the next records (lead function) "Tracked_inTIME". If > then end the current records "out" time with the next records "in" time and create a flag to say "is_loaded". You'll use this in a next/final query to query your new time sequence table to get your datediff when transition from is_loaded = 1 to is_loaded = 0 or vice versa.

So, your first "mach up" records should not go to 10, but should end at 8:20 when load begins.... and so on and so on to create your new/temp table. then query that table.

user_33852160
Related