SQL Server Performance

Find the difference between 2 rows in 1 column...

Discussion in 'T-SQL Performance Tuning for Developers' started by anhchuong, Jul 13, 2009.

  1. anhchuong New Member

    I'm using SQL2000 and I am new to it. I am try to find the difference between rows in a column and store the values to another table or another column within that table.
    For example:
    Date Time Products
    20/05/09 01:25 35
    20/05/09 02:35 45
    21/05/09 03:25 25
    21/05/09 04:35 65
    I want to find the different amount of products produced between 01:25 to 02:35 on 20/05/09 and between 03:25 to 04:35 on 21/05/09. Simply, is it possible for me to run any query that can find the differences between rows in column Products without worrying about Date and Time.
    I'm looking forward to your answers. Thanks in advance.
  2. khtan New Member

    is it always in pairs ? Will there be situation where there are more than 2 records within the time range that you specified ? And in such case how do you want to calculate the different amount of products ?
  3. Adriaan New Member

    The basic syntax for comparing two rows in the same table is to use a self-join, like this:
    SELECT t1.Date, t1.Time, t2.Time, t1.Products - t2.Products
    FROM MyTable t1
    INNER JOIN MyTable t2
    ON t1.Date = t2.Date
    WHERE t1.Date = '21/05/09'
    AND t1.Time = '01:25' AND t2.Time = '02:35'
    If you need to calculate this for large numbers of rows, you could use a correlated subquery in the WHERE clause to pair the current and previous Time entries for the same day. However, in most cases you will find that you will get better performance by using a cursor, storing the results in a temporary table or table variable.
  4. anhchuong New Member

    Thanks for your advice. This is what I have tried so far:
    t2.Products_from_Rommelag - t1.Products_from_Rommelag [Diff],
    From ROM2 t1 Inner Join
    ROM2 t2 On t2.Date = t1.Date And t2.Time = (Select Min(t3.Time)
    From ROM2 t3
    Where t3.Date = t1.Date And t3.ROM = t1.ROM And t3.Time > t1.Time)
    Here rom2 is that table i store the data
    However, there is a small problem. I cannot get the difference between row 1 and the row above it, which is null row.
    Date; Time;Products_from_Rommelag
    null; null; null
    02.07.2009; 00:04:01;17
    02.07.2009; 00:19:01;82;
    02.07.2009; 00:34:01;150;
    02.07.2009; 00:49:01;216;
    I got the answer:
    Date; Time;Diff
    57/2/200912:19:01 AM 65

    57/2/200912:34:01 AM68

    57/2/200912:49:01 AM66

    57/2/20091:04:01 AM67

    which is missing the difference between row 2 and row null at 00:04:01
    The idea is that the machine will add up all of the products that have been produce into Products_from_Rommelag every 15 mins, and I would like to find the difference between the rows so that I can get the number of products that have been produced within that 15mins interval. The table start with a null row.
    Please excuse my bad explanation. I have tried my best. And thank you for all of ur help.

Share This Page