Query to Pull Current Date and Value with Previous Date and Value from Table

Hello,

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.

Thoughts?

Thanks,
-Torrwin
SELECT A.ID, A.READING_DATE, A.READING_VALUE AS CURRENT_VALUE, A.PRIOR_READING_DATE, B.VALUE AS PRIOR_VALUE
FROM (SELECT ID, 
           READING_DATE, 
           (SELECT MAX(READING_DATE) AS PRIOR_READING_DATE FROM TABLE1 WHERE ID = TEMP.ID AND READING_DATE < TEMP.READING_DATE) AS PRIOR_READING_DATE,
           READING_VALUE
      FROM TABLE1 TEMP) A INNER JOIN TABLE1 B ON A.ID = B.ID AND A.PRIOR_READING_DATE = B.READING_DATE

Open in new window

LVL 13
TorrwinAsked:
Who is Participating?
 
sdstuberCommented:
SELECT id,
       reading_date,
       reading_value AS current_value,
       LAG(reading_date) OVER (PARTITION BY id ORDER BY reading_date)
           prior_reading_date,
       LAG(reading_value) OVER (PARTITION BY id ORDER BY reading_date)
           AS prior_value
  FROM table1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:
SELECT A.ID
  , MAX(CASE WHEN RN = 1 THEN A.READING_DATE END) READING_DATE
  , MAX(CASE WHEN RN = 1 THEN A.READING_VALUE END )AS CURRENT_VALUE
  , MAX(CASE WHEN RN = 2 THEN A.READING_DATE END) PRIOR_READING_DATE
  , MAX(CASE WHEN RN = 2 THEN A.READING_VALUE END )AS PRIOR_VALUE
FROM (  SELECT ID
             , READING_DATE
             , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY READING_DATE DESC) RN
          FROM TABLE1 TEMP
      ) A
WHERE RN <= 2
GROUP BY A.ID

Open in new window

0
 
TorrwinAuthor Commented:
That only returns one record per meter
0
 
TorrwinAuthor Commented:
sorry, per ID
0
 
TorrwinAuthor Commented:
Works great, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.