Hello, 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. Regards
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 ?
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.
Thanks for your advice. This is what I have tried so far: Select t2.Date, t2.Time, 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. E.g: 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.