Get Previous Record by Group SQL

Question

I have the following table:

ID  GROUPID     ODATE       OTIME       OVALUE
1   A           2014-05-31  00:00:00    1207432.6
2   A           2014-05-31  01:00:00    1209064     
3   A           2014-05-31  02:00:00    1210698     
4   A           2014-05-31  03:00:00    1212333.3   
5   A           2014-05-31  04:00:00    1213967.7   
6   B           2014-05-31  00:00:00    2110016     
7   B           2014-05-31  01:00:00    2110016     
8   B           2014-05-31  02:00:00    2110016     
9   B           2014-05-31  03:00:00    2110016     
10  B           2014-05-31  04:00:00    2110016     
11  C           2014-05-31  00:00:00    2326592.6   
12  C           2014-05-31  01:00:00    2328088.8
13  C           2014-05-31  02:00:00    2329590.3   
14  C           2014-05-31  03:00:00    2331094.5   
15  C           2014-05-31  04:00:00    2332598

Then I run this syntax:

SELECT 
A.ID, A.GroupID, A.oDate, A.oTime, 
A.oValue, MAX(B.oValue) AS Prev_oValue, A.oValue - MAX(B.oValue) AS oResult
FROM
Table1 AS A LEFT OUTER JOIN Table1 AS B ON B.GroupID = A.GroupID AND B.oValue < A.oValue
GROUP BY
A.ID, A.GroupID, A.oDate, A.oTime, A.oValue
ORDER BY A.GroupID, A.oDate, A.oTime

I want to have the following result:

ID  GROUPID     ODATE       OTIME       OVALUE      PREV_OVALUE     ORESULT
1   A           2014-05-31  00:00:00    1207432.6   (null)          (null)
2   A           2014-05-31  01:00:00    1209064     1207432.6       1631.4
3   A           2014-05-31  02:00:00    1210698     1209064         1634
4   A           2014-05-31  03:00:00    1212333.3   1210698         1635.3
5   A           2014-05-31  04:00:00    1213967.7   1212333.3        1634.4
6   B           2014-05-31  00:00:00    2110016     (null)          (null)
7   B           2014-05-31  01:00:00    2110016     2110016         0
8   B           2014-05-31  02:00:00    2110016     2110016         0
9   B           2014-05-31  03:00:00    2110016     2110016         0
10  B           2014-05-31  04:00:00    2110016     2110016         0
11  C           2014-05-31  00:00:00    2326592.6   (null)          (null)
12  C           2014-05-31  01:00:00    2328088.8   2326592.6       1496.2
13  C           2014-05-31  02:00:00    2329590.3   2328088.8       1501.5
14  C           2014-05-31  03:00:00    2331094.5   2329590.3       1504.2
15  C           2014-05-31  04:00:00    2332598     2331094.5       1503.5

Check on fiddle

What I want is, get the previous value based on the GroupID column and Order by Date and Time column. After I got the previous value, the current record minus previous value AS RESULT. But something wrong, the result is bad. Some records get the previous value, and some records is not. I couldn't understand.

Does anyone know how to achieve this?

Thank you.

Answer 1

You could use a common table expression with ROW_NUMBER() to number each row in time order within a group. Getting the previous value is then as simple as left joining the cte with itself to get the row with the same groupid and a row number that is one less. Something like;

WITH cte AS (
  SELECT groupid, odate, otime, ovalue,
    ROW_NUMBER() OVER (PARTITION BY groupid ORDER BY odate, otime) rn
  FROM table1
)
SELECT a.groupid, a.odate, a.otime, a.ovalue, b.ovalue Prev_oValue,
       a.ovalue-b.ovalue oResult
FROM cte a
LEFT JOIN cte b
  ON a.groupid = b.groupid
 AND a.rn = b.rn + 1
ORDER BY a.groupid, a.odate, a.otime

An SQLfiddle to test with.