1

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:

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:

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.

  • 1
    Do you have any data element that represents a batch as it moves through the tanks? To me, that seems like the flaw in your data processing is that you need to be able to tie each individual batch together. – Mike Fal Mar 31 '15 at 22:22
  • @MikeFal No, and this is part of the "bummer" of this task. The only way to know what is the last task of a tank is to get the TStamp of the next "step 1" of the tank, and from there, find the previous TStamp. That would be the end TStamp. (as there can be only 1 recipe in a tank at any time). I found this that probably has part of the solution: dba.stackexchange.com/questions/68770/time-elapsed-calculation – Philippe Apr 1 '15 at 12:13
0

didn't know your table name so I created my own name. change the table name and it should work. This gives the difference in minutes.

select A.rec_number,A.tank,
CONVERT(varchar(5), DATEADD(minute, datediff (minute,A.start_ts,B.end_ts), 0), 114)   as differenceTime from
(
select a.rec_number,a.tank, start_ts from dbo.TEST a
inner join(
select MIN(step_number) as minstep,rec_number,tank from dbo.TEST
group by rec_number,tank)b on b.minstep = a.step_number and b.rec_number = a.rec_number and b.tank = a.tank
)A
INNER JOIN
(
select a.rec_number,a.tank,end_ts from dbo.TEST a
inner join(
select MAX(step_number) as maxstep,rec_number,tank from dbo.TEST
group by rec_number,tank)b on b.maxstep = a.step_number and b.rec_number = a.rec_number and b.tank = a.tank
)B
ON A.rec_number =B.rec_number and A.tank = B.tank
| improve this answer | |
  • I see where you are going. Thanks for the effort. The nuance is not to get the time elapsed in each step, but total in each tank, where the number of step is variable. In short, to get the "end date", I need to find the start time of the next batch (step 1 in same tank), and use the previous end time (step1 startdate - 1 second, get the previous MAX(end date), or something similar) as the previous batch's end time. That's where the twist comes in :-) – Philippe Apr 2 '15 at 13:24
  • So you need the time a rec_number has spent in a certain tank? – Stijn Wynants Apr 2 '15 at 13:37
0

I finally managed to solve my problem.

I started by adding a unique ID to every "step 1" I could, then used an OUTER APPLY to fill in the rest of the gap of everything with a lower START_TS than it.

-- Add a unique ID to every Step 1
DECLARE @myVar INT = 0
UPDATE #start SET @myvar = JobID = @myVar + 1 WHERE Step_Number=1

-- Fill in all the blanks to group with the new ID
UPDATE  #start
    SET     JobID = T3.JobID
    FROM    #start T1
        OUTER APPLY
        (   SELECT  MAX(JobID) JobID
            FROM    #start T2
            WHERE T2.Start_Ts < T1.Start_Ts
                AND t2.Step_Number=1
                AND T2.Rec_Number=t1.Rec_Number AND t2.Tank=t1.Tank
        ) T3
    WHERE T1.jobid IS NULL

-- Final Selection
SELECT JobID, rec_number, tank, MIN(start_ts) DTSTART, MAX(end_ts) DTEND, DATEDIFF(MINUTE,MIN(start_ts),MAX(end_ts)) Temps 
    FROM #start
    WHERE Rec_Number <> '' AND tank <> '' AND Step_Number <> '' AND jobid IS NOT NULL
    GROUP BY JobID, rec_number, tank
    ORDER BY rec_number, tank, MIN(start_ts)

Thanks to all thoses that gave it a shot!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.