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)