SQL to return seconds difference between two rows

Question

This question is related to SQL Server. I have the following table:

id    size   batch code   product code      additiontime
--------------------------------------------------------
1       91      55555       BigD Red        2017-05-15 13:00:00
2       91      55555       BigD Red        2017-05-15 13:00:05
3       94      55555       BigD Red        2017-05-15 13:00:15
4       91      44444       BigD Blue       2017-05-15 14:10:00
5       92      44444       BigD Blue       2017-05-15 14:15:00
6       93      44444       BigD Blue       2017-05-15 14:20:00
7       94      44444       BigD Blue       2017-05-15 14:30:00
8       91      33333       BigD Orange     2017-05-15 15:00:00
9       91      33333       BigD Orange     2017-05-15 15:00:10
10      94      33333       BigD Orange     2017-05-15 15:00:15

When I execute this SQL statement:

select *
from mytable y1
where size = 91 
  and not exists (select 1
                  from mytable y2
                  where y1.productcode = y2.productcode and y2.size = 92)

I get the following results:

id    size   batch code   product code      additiontime
--------------------------------------------------------
1       91      55555       BigD Red        2017-05-15 13:00:00
2       91      55555       BigD Red        2017-05-15 13:00:05
8       91      33333       BigD Orange     2017-05-15 15:00:00
9       91      33333       BigD Orange     2017-05-15 15:00:10

I'd like to modify the above query so that ONE row is shown per batch code, with an additional column added to show the difference in seconds between the first and second records of each batch code group, for example:

id    size   batch code   product code      additiontime            seconds difference
--------------------------------------------------------------------------------------
1       91      55555       BigD Red        2017-05-15 13:00:00     5
8       91      33333       BigD Orange     2017-05-15 15:00:00     10

I have tried the following SQL, which almost does the trick, but it returns multiple rows instead of just one per batch code:

WITH rows AS
(
    SELECT 
        *, 
        ROW_NUMBER() OVER (ORDER BY AdditionTime) AS rn 
    FROM 
        mytable y1
    WHERE
        size = 91 
        AND NOT EXISTS (SELECT *
                        FROM mytable y2
                        WHERE y1.productcode = y2.productcode AND y2.size = 92)
)
SELECT DATEDIFF(second, mc.AdditionTime, mp.AdditionTime)
FROM rows mc
JOIN rows mp ON mc.rn = mp.rn - 1

My SQL knowledge is not great. I'm sure it's possible to produce the right results but I've tried all ways with no success. Thanks in advance for any pointers.

SQL to return seconds difference between two rows

4 Answers