Menu
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.
391
First, we get the latest date for each user id using GROUP BY. Now that we know the most recent date for each user id, we join this result with our original table to get the latest record by user group.
Overview of SQL Server LAG() function In other words, by using the LAG() function, from the current row, you can access data of the previous row, or the row before the previous row, and so on. The LAG() function can be very useful for comparing the value of the current row with the value of the previous row.
To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ). You assign the row numbers within each group (i.e., year).
Retrieving the last record in each group using GROUP BY There are two solutions explained here using the GROUP BY clause. In both these solutions, we will be using the MAX() function to get the maximum value of id and then retrieving the other columns corresponding to this maximum id.
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.
51
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
