SQL datediff for row items

Question

I found difficulty with writing SQL statement to calculate the time of each items of row.

My table structure is simple:

TblProduct(Product nvarchar(50), Serial nvarchar(50), Time_Checked datetime)

Below is my sample data:-

PRODUCT1 Serial1 2014-04-02 07:01:21.000
PRODUCT1 Serial2 2014-04-02 07:02:21.000
PRODUCT1 Serial3 2014-04-02 07:03:21.000
PRODUCT2 Serial1 2014-04-02 08:01:21.000
PRODUCT2 Serial2 2014-04-02 08:02:21.000
PRODUCT2 Serial3 2014-04-02 08:03:21.000
PRODUCT3 Serial1 2014-04-02 09:01:21.000
PRODUCT3 Serial2 2014-04-02 09:02:21.000
PRODUCT3 Serial3 2014-04-02 09:03:21.000
PRODUCT1 Serial4 2014-04-02 10:01:21.000
PRODUCT1 Serial5 2014-04-02 10:02:21.000
PRODUCT2 Serial6 2014-04-02 10:03:21.000

I want to calculate the time for changing product by datediff(minute,start time, end time) where start time as the last item of each series of product kinds

Start time: PRODUCT1 2014-04-02 07:03:21.000

End time: PRODUCT2 2014-04-02 08:01:21.000

==>datediff(minute,start time, end time)=58 minutes

Finally, I want to return a result like a table below:-

PRODUCT1 Serial3 PRODUCT2 Serial1 58(minutes)
PRODUCT2 Serial3 PRODUCT3 Serial1 58(minutes)
PRODUCT3 Serial3 PRODUCT4 Serial4 58(minutes)

Answer 1

Use self join with row numbering

SELECT  Productset1.Product,
        Productset1.serial,
        Productset2.Product,
        Productset2.serial,
        CAST(DATEDIFF(minute, Productset1.time_checked, Productset2.time_checked) AS VARCHAR(20)) + ' (minutes)'
FROM    ( SELECT    row_number() OVER ( ORDER BY time_checked ) pid,
                    Product,
                    serial,
                    time_checked,
                    CONVERT(VARCHAR(20), time_checked, 103) datet
          FROM      TblProduct
        ) Productset1
        INNER JOIN ( SELECT row_number() OVER ( ORDER BY time_checked ) pid,
                            Product,
                            serial,
                            time_checked,
                            CONVERT(VARCHAR(20), time_checked, 103) datet
                     FROM   TblProduct
                   ) Productset2 ON Productset1.pid = Productset2.pid - 1
                          AND Productset1.Product <> Productset2.product