At a company, fluids are prepared and held in tanks, following a preparation/recipe. The preparations move from tank to tank depending on needs. The tasks are logged in sequence into a table.

What I need to obtain is the time each recipe spent in each tank, from step 1 to step X. The problem is that I don't have a direct way to identify different batches of the same recipe, nor do I have a clear "END" log entry.

I am certain there is a way to make it happen with CTEs, but I can't seem to get my head around it.

The things I can be certain:

* A recipe always start at step 1 in a tank.
* A tank can only hold one recipe at a time.
* The IDs will not be sequential.
* The number of steps can and will be variable.

Sample data:

```none
ID      Rec_Number  tank  Step_Number  Start_Ts             End_Ts
134754  R8020       RECP  1            2015-03-16 05:40:00  2015-03-16 05:40:00
134755  R8020       RECP  2            2015-03-16 05:40:00  2015-03-16 05:48:00
134756  R8020       RECP  3            2015-03-16 05:48:00  2015-03-16 05:52:00
134757  R8020       RECP  4            2015-03-16 05:52:00  2015-03-16 05:57:00
134758  R8020       RECP  5            2015-03-16 05:57:00  2015-03-16 06:01:00
134759  R8020       RECP  6            2015-03-16 06:01:00  2015-03-16 06:02:00
134760  R8020       RECP  7            2015-03-16 06:02:00  2015-03-16 06:02:00
134761  R8020       RECP  8            2015-03-16 06:02:00  2015-03-16 06:03:00
134762  R8020       RECP  9            2015-03-16 06:03:00  2015-03-16 06:03:00
134763  R8020       RECP  10           2015-03-16 06:03:00  2015-03-16 06:03:00
134764  R8020       RECP  11           2015-03-16 06:03:00  2015-03-16 06:03:00
134765  R8020       RECP  12           2015-03-16 06:03:00  2015-03-16 06:06:00
134766  R8020       RECP  13           2015-03-16 06:06:00  2015-03-16 06:07:00
134767  R8020       RECP  14           2015-03-16 06:07:00  2015-03-16 06:07:00
134769  R8020       RECP  15           2015-03-16 06:07:00  2015-03-16 06:07:00
134778  R8020       RE01  1            2015-03-16 06:11:00  2015-03-16 06:12:00
134779  R8020       RE01  2            2015-03-16 06:12:00  2015-03-16 06:13:00
134780  R8020       RE01  3            2015-03-16 06:13:00  2015-03-16 06:13:00
134781  R8020       RE01  4            2015-03-16 06:13:00  2015-03-16 06:13:00
134782  R8020       RE01  5            2015-03-16 06:13:00  2015-03-16 06:14:00
134784  R8020       RE01  6            2015-03-16 06:14:00  2015-03-16 06:14:00
134785  R8020       RE01  7            2015-03-16 06:14:00  2015-03-16 06:15:00
134786  R8020       RE01  8            2015-03-16 06:15:00  2015-03-16 06:16:00
134788  R8020       RECP  1            2015-03-16 06:16:00  2015-03-16 06:16:00
134789  R8020       RECP  2            2015-03-16 06:16:00  2015-03-16 06:18:00
134803  R8020       RE01  9            2015-03-16 06:16:00  2015-03-16 06:48:00
134790  R8020       RECP  3            2015-03-16 06:18:00  2015-03-16 06:21:00
134791  R8020       RECP  4            2015-03-16 06:21:00  2015-03-16 06:25:00
134792  R8020       RECP  5            2015-03-16 06:25:00  2015-03-16 06:26:00
134793  R8020       RECP  6            2015-03-16 06:26:00  2015-03-16 06:27:00
134794  R8020       RECP  7            2015-03-16 06:27:00  2015-03-16 06:27:00
134795  R8020       RECP  8            2015-03-16 06:27:00  2015-03-16 06:28:00
134796  R8020       RECP  9            2015-03-16 06:28:00  2015-03-16 06:28:00
134797  R8020       RECP  10           2015-03-16 06:28:00  2015-03-16 06:28:00
134798  R8020       RECP  11           2015-03-16 06:28:00  2015-03-16 06:29:00
134799  R8020       RECP  12           2015-03-16 06:29:00  2015-03-16 06:32:00
134800  R8020       RECP  13           2015-03-16 06:32:00  2015-03-16 06:34:00
134802  R8020       RECP  14           2015-03-16 06:34:00  2015-03-16 06:34:00
134804  R8020       RE01  10           2015-03-16 06:48:00  2015-03-16 07:03:00
134805  R8020       RE01  11           2015-03-16 07:03:00  2015-03-16 07:03:00
134806  R8020       RE01  12           2015-03-16 07:03:00  2015-03-16 07:03:00
134808  R8020       RE01  13           2015-03-16 07:03:00  2015-03-16 07:07:00
134811  R8020       RE01  1            2015-03-16 07:07:00  2015-03-16 07:09:00
134812  R8020       RE01  2            2015-03-16 07:09:00  2015-03-16 07:09:00
134813  R8020       RE01  3            2015-03-16 07:09:00  2015-03-16 07:09:00
134814  R8020       RE01  4            2015-03-16 07:09:00  2015-03-16 07:09:00
134815  R8020       RE01  5            2015-03-16 07:09:00  2015-03-16 07:10:00
134817  R8020       RE01  6            2015-03-16 07:10:00  2015-03-16 07:10:00
134820  R8020       RE01  7            2015-03-16 07:10:00  2015-03-16 07:12:00
134821  R8020       RE01  8            2015-03-16 07:12:00  2015-03-16 07:14:00
134823  R8020       RECP  2            2015-03-16 07:12:00  2015-03-16 07:44:00
134819  R8020       RECP  1            2015-03-16 07:12:00  2015-03-16 07:12:00
134822  R8020       RE01  9            2015-03-16 07:14:00  2015-03-16 07:36:00
134826  R8020       RE01  10           2015-03-16 07:36:00  2015-03-16 07:51:00
134824  R8020       RECP  3            2015-03-16 07:44:00  2015-03-16 07:48:00
134825  R8020       RECP  4            2015-03-16 07:48:00  2015-03-16 07:50:00
134827  R8020       RECP  5            2015-03-16 07:50:00  2015-03-16 07:51:00
134828  R8020       RECP  6            2015-03-16 07:51:00  2015-03-16 07:52:00

```

So recipe R8020 starts in tank RECP for 15 steps, then moves to RE01 for 13 steps. Meanwhile, a new R8020 starts in tank RECP for 14 steps. Etc.

I would like to have:

```none
Recipe  Tank  TimeSpentInTank
R8020   RECP  00:27:00
R8020   RE01  00:23:00
R8020   RECP  00:18:00
etc.

```

I am in SQL 2008. A solution can include any number of steps, including views, CTEs, pivoting, or Stored Procedures.