I need to write a query that pulls the current date's value as well as the value was the date before (NOT always yesterday's value) from the same table.
The three important colums are: ID, READING_DATE, and READING_VALUE.
Here was my first pass, but I really don't like it at all and it takes forever to run.
SELECT A.ID, A.READING_DATE, A.READING_VALUE AS CURRENT_VALUE, A.PRIOR_READING_DATE, B.VALUE AS PRIOR_VALUE
FROM (SELECT ID,
(SELECT MAX(READING_DATE) AS PRIOR_READING_DATE FROM TABLE1 WHERE ID = TEMP.ID AND READING_DATE < TEMP.READING_DATE) AS PRIOR_READING_DATE,
FROM TABLE1 TEMP) A INNER JOIN TABLE1 B ON A.ID = B.ID AND A.PRIOR_READING_DATE = B.READING_DATE