Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have the following table:
| location | eventdate | value |
|---|---|---|
| loc1 | 2021-11-28 | 1 |
| loc2 | 2021-11-28 | 2 |
| loc1 | 2021-11-29 | 1 |
| loc2 | 2021-11-29 | 3 |
| loc1 | 2021-11-30 | 2 |
| loc2 | 2021-11-30 | 5 |
And I'd like to compare the values from the oldest availavle day and the day before that to get this result:
| location | diff |
|---|---|
| loc1 | 1 |
| loc2 | 2 |
What's the SQL statement to get this?
As others have pointed out in the comments, you can JOIN your table to itself, also known as a self join. You can achieve this like so:
SELECT
CurrentDay.location,
CurrentDay.value - PreviousDay.value AS diff
FROM YourTable CurrentDay
INNER JOIN YourTable PreviousDay
ON CurrentDay.location = PreviousDay.location
AND CurrentDay.eventdate = DATE_SUB(PreviousDay.eventdate, INTERVAL 1 DAY);
This self join takes your table and joins it to itself on the same location but where the eventdate differs by 1 day.
