Is there a way in SQL or Power BI where I can define jobs and figure out the time it takes to do a job and then time in between jobs?

In the table below, there are job activities and unique identifiers under lodnum. I’m trying to figure out a way to get the time difference in between a job. A job such as pallet move has two entries. As you can see in rows 1 and 2, that is a pallet move for pallet A. Imagine the first entry as picking up the pallet, and the second entry as setting it down. Rows 3 and 4 are a separate job that are moving the pallet somewhere else. Rows 7 and 9 are a different job as well, sometimes thet skip a few rows. I’d like to figure out the time it takes each user to perform each job. As well as the time in between each job for a user.

How could I do this in SQL or Power BI?

+---------+-----------------+--------+------+-------+-----+---------+--------+-------+
| Row Num |    activity     | usr_id | year | month | day |  time   | lodnum | wh_id |
+---------+-----------------+--------+------+-------+-----+---------+--------+-------+
|       1 | Pallet Move     | User1  | 2020 |     8 |   5 | 8:24:23 | A      | WH1   |
|       2 | Pallet Move     | User1  | 2020 |     8 |   5 | 8:24:30 | A      | WH1   |
|       3 | Pallet Move     | User2  | 2020 |     8 |   5 | 8:25:10 | A      | WH1   |
|       4 | Pallet Move     | User2  | 2020 |     8 |   5 | 8:25:14 | A      | WH1   |
|       5 | Pallet Move     | User1  | 2020 |     8 |   5 | 8:25:27 | B      | WH1   |
|       6 | Pallet Move     | User1  | 2020 |     8 |   5 | 8:25:30 | B      | WH1   |
|       7 | Inbound Putaway | User3  | 2020 |     8 |   6 | 4:19:58 | C      | WH2   |
|       8 | Inbound Putaway | User3  | 2020 |     8 |   6 | 4:19:59 | D      | WH2   |
|       9 | Inbound Putaway | User3  | 2020 |     8 |   6 | 4:22:05 | C      | WH2   |
|      10 | Inbound Putaway | User3  | 2020 |     8 |   6 | 4:22:10 | D      | WH2   |
|      11 | Trailer Load    | User1  | 2020 |     8 |   6 | 6:22:17 | E      | WH3   |
|      12 | Trailer Load    | User1  | 2020 |     8 |   6 | 6:22:23 | F      | WH3   |
|      13 | Trailer Load    | User1  | 2020 |     8 |   6 | 6:22:26 | E      | WH3   |
|      14 | Trailer Load    | User1  | 2020 |     8 |   6 | 6:22:30 | F      | WH3   |
+---------+-----------------+--------+------+-------+-----+---------+--------+-------+

Answer

Some assumptions:

• Job start and ends always on the same day? Important for the caculation.
• Unique combination of activity / usr_id / year / month / lodnum / wh_id defines ‘one’ job

Please let us know if this is correct?

Possible solution:

First, I built a CTE (Common Table Expression) which adds two columns:

1. [Row] time column defining the order of the jobs, based on the combination of unique columns. Results in 1 and 2: start job and end job.
2. [Key] creating a key based on combination of unique columns, for the join later.

Second, I join two versions of the CTE: one with the filter on [Row] = 1 (start job) which joins the second one on [Key] and [Row] = 2. Then caculates the difference in seconds.

WITH CTE AS (
SELECT
       [activity]
      ,[usr_id]
      ,[year]
      ,[month]
      ,[day]
      ,[time]
      ,[lodnum]
      ,[wh_id]
      ,ROW_NUMBER() OVER (PARTITION BY activity, usr_id, year, month, day, lodnum, wh_id ORDER BY [time]) as [Row]
      ,RANK() OVER (ORDER BY activity, usr_id, year, month, day, lodnum, wh_id) as [Key]
  FROM [dbo].[JobActivities] )

  SELECT
     StartTime.usr_id, 
     StartTime.activity, 
     StartTime.day,
     StartTime.time as [Start Time],
     EndTime.time as [End Time],
     DATEDIFF(SECOND, StartTime.time, EndTime.time) as [Difference (s)]
  FROM
  CTE StartTime
  LEFT OUTER JOIN CTE EndTime ON StartTime.[Key] = EndTime.[Key] AND EndTime.Row = 2
  WHERE StartTime.Row = 1
  ORDER BY StartTime.day, StartTime.time

Solution 2 – Using LEAD (without join, better performance):

WITH CTE AS (
SELECT [Row Num]
      ,[activity]
      ,[usr_id]
      ,[year]
      ,[month]
      ,[day]
      ,[time] [Start Time]
      ,[lodnum]
      ,[wh_id]
      ,ROW_NUMBER() OVER (PARTITION BY activity, usr_id, year, month, day, lodnum, wh_id ORDER BY [time]) as [Row]
      ,LEAD([time]) OVER (PARTITION BY activity, usr_id, year, month, day, lodnum, wh_id ORDER BY [time]) AS [End Time]
  FROM [dbo].[JobActivities] )

  SELECT
     usr_id, 
     activity, 
     day,
     [Start Time],
     [End Time],
     DATEDIFF(SECOND, [Start Time], [End Time]) as [Difference (s)]
  FROM
  CTE
  WHERE Row = 1
 ORDER BY day, [Start Time]