I have a table like this:
Index , PersonID , ItemCount , UnixTimeStamp
1 , 1 , 1 , 1296000000
2 , 1 , 2 , 1296000100
3 , 2 , 4 , 1296003230
4 , 2 , 6 , 1296093949
5 , 1 , 0 , 1296093295
Time and index always go up. Its basically a logging table to log the itemcount each time it changes. I get the most recent ItemCount for each Person like this:
SELECT *
FROM table a
INNER JOIN
(
SELECT MAX(index) as i
FROM table
GROUP BY PersonID) b
ON a.index = b.i;
What I want to do is get get the most recent record for each PersonID that is at least 24 hours older than the most recent record for each Person ID. Then I want to take the difference in ItemCount between these two to get a change in itemcount for each person over the last 24 hours:
personID ChangeInItemCountOverAtLeast24Hours
1 3
2 -11
3 6
Im sort of stuck with what to do next. How can I join another itemcount based on latest adjusted timestamp of individual rows?
