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.

Answer 1

This gives you the desired output with a self join on the result of the cte. Also, I added a PARTITION BY to the ROW_NUMBER():

CREATE TABLE #mytable
    (
      id INT ,
      size INT ,
      batchCode INT ,
      productCode NVARCHAR(30) ,
      additiontime DATETIME
    );


INSERT  INTO #mytable
        ( id, size, batchCode, productCode, additiontime )
VALUES  ( 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' );
WITH    rows
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY y1.batchCode 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  t1.id ,
            t1.size ,
            t1.batchCode ,
            t1.productCode ,
            DATEDIFF(SECOND, t1.additiontime, t2.additiontime) SecondsDiff
    FROM    rows t1
            INNER JOIN rows t2 ON t2.batchCode = t1.batchCode
                                  AND t1.id != t2.id
    WHERE   t1.rn = 1; 

DROP TABLE #mytable

Produces:

id  size    batchCode   productCode  SecondsDiff
8   91      33333       BigD Orange  10
1   91      55555       BigD Red     5

Note, you should test with a larger data set to ensure accuracy and take account for any other scenarios.

Answer 2

You are actually quite close! :) The only missing thing is to add another condition on the JOIN of the CTE, so that you compare only rows with the same product code as below:

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  mc.*, DATEDIFF(second, mc.AdditionTime, mp.AdditionTime) AS Diff
FROM    rows mc JOIN rows mp ON mc.rn = mp.rn - 1 AND mc.ProductCode = mp.ProductCode

PS. Assuming that you know you have two rows per Product.