0

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)
  • Your desired results mentions PRODUCT4 but the sample data has no row for that product. Also product/serial for last row of sample data is strange. Please edit your question to clarify. – Michael Green Nov 19 '14 at 10:55
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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.