Calculating differences in time from multiple rows

Question

EquipmentUseId  CollectionPointId  EmployeeNum  ShopOrder  StartDateTime
366                 69             9999         999999     4/26/13 3:29 PM
373                 69             4878         107321     4/26/13 10:19 PM
385                 69             4971         107321     4/27/13 7:35 AM
393                 69             4179         107325     4/30/13 7:24 AM
394                 69             4179         107325     4/30/13 7:38 AM
395                 69             4179         107325     4/30/13 10:28 AM
398                 69             4179         107325     4/30/13 2:41 PM
399                 69             9999         999999     4/30/13 2:43 PM
400                 69             9999         999999     4/30/13 2:46 PM

Given the above table, I'm left with a unique problem and describing it may be just as difficult. There is a StartDateTime for each ShopOrder per Employee but no StopDateTime, this is by design. However I need to be able to calculate the difference in time between the StartDateTime of one ShopOrder and the StartDateTime of the next ShopOrder. An example: SO # 999999 starts at 15:29 on 4/26 by Employee 9999, then a new SO # of 107321 is started at 22:19 on 4/26 by Employee 4878. I would need to calculate the difference between 4/26/2013 22:19 and 4/26/2013 15:29. This would give me the clock out date for SO# 9999 but it's actually need for a secondary process. For now I just need to be able get the time. One hang-up is if the SO #'s are the same then I would only use the first StartDateTime, and the first StartDateTime of the next SO #. Sorry this is so long, I'm not even sure if I've explained anything at this point.

Please go easy on me...it's been a long day.

Edited for output on 08/19/2013:

After mulling it over during the weekend, I decided it would be best to use an EndDateTime as this query is only the first step in the overall application/report.

Also, the EmployeeNum is not relevant anymore to this portion of the application.

This is how it should look (The EquipmentUseID is PK, the CollectionPointID is always 69 so they don't need to be shown on the output)?
ShopOrder    StartDateTime        EndDateTime
999999       4/26/13 3:29 PM      4/26/13 10:19 PM
107321       4/26/13 10:19 PM     4/30/13 7:24 AM
107325       4/30/13 7:24 AM      4/30/13 2:43 PM
999999       4/30/13 2:43 PM      <next SO# StartDateTime>
To sum up this table, I need the SO#, the StartDateTime per SO# (already in the table), and the EndDateTime which is the actually the StartDateTime for the next SO#. Hopefully this clears it up, sorry for the confusion.

use an inline view where you first get the max date per shop order, then look up LAG functions and loop over those doing the time diff — Randy, Aug 16 '13 at 22:26
@ctmcklowe96 could you provide desired output for given input? — Roman Pekar, Aug 17 '13 at 19:49
@RomanPekar - The output and a bit more explanation begin with the "Edited for output..." in bold. — ctmcklowe96, Aug 19 '13 at 13:49

score 0 · Answer 1 · answered Aug 16 '13 at 22:30

Here is a solution to the first problem, which is getting the next date time on the "next" shop order. This solution uses a correlated subquery:

select t.*, nextStartDateTime - StartDateTime -- or use datediff() here if you want the time in particular units
from (select t.*,
             (select top 1 StartDateTime
              from t t2
              where t2.StartDateTime > t.StartDateTime and
                    t2.ShopOrder <> t.ShopOrder
              order by StartDateTime
             ) as nextStartDateTime
      from t
     ) t;

The second problem is getting the first record for a given shop order. I'm not sure I understand this fully. It would help if you edited the question to explain the results that you want on the data provided in the question.

paparazzo · Accepted Answer · 2013-08-17T19:44:11.050

0

with orderedTickets as 
(
    select ShopOrder, min(StartDateTime) as date 
         , row_number() over (order by min(StartDateTime)) as rownumber
    from table 
    where ShopOrder is not null  
    group by ShopOrder
)
select t1.ShopOrder, datediff(ss, t1.StartDateTime, t2.StartDateTime) 
  from orderedTickets as t1 
  join orderedTickets as t2 
    on t2.rownumber = t1.rownumber + 1

edited Aug 17 '13 at 19:44

answered Aug 16 '13 at 22:58

paparazzo

44,497
23
105
176

score 0 · Answer 3 · edited Aug 18 '13 at 16:48

Use CTE, -

;with Equipment as 
(
select EquipmentUseId, CollectionPointId, EmployeeNum, ShopOrder, StartDateTime, row_number() over (PARTITION BY ShopOrder order by EquipmentUseId) as rownumber
from your_table 
),
Equipment2 AS (
select EquipmentUseId, CollectionPointId, EmployeeNum, ShopOrder, StartDateTime, row_number() over (order by EquipmentUseId) as rownumber
from Equipment WHERE rownumber = 1
)

SELECT t1.EquipmentUseId,
   t1.CollectionPointId,
   t1.EmployeeNum,
   t1.ShopOrder,
   t1.StartDateTime,
   CONVERT(VARCHAR, DATEDIFF(n, t1.StartDateTime, t2.StartDateTime) / 60) + ':' +  RIGHT('0' + CONVERT(VARCHAR,(DATEDIFF(n, t1.StartDateTime, t2.StartDateTime)%60)), 2) time_diff
FROM   Equipment2 AS t1
LEFT JOIN Equipment2 AS t2 ON  t2.rownumber = t1.rownumber + 1

Calculating differences in time from multiple rows

3 Answers3