asked on

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

Guy Hengel [angelIII / a3]

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

Torrwin

ASKER

That only returns one record per meter

Torrwin

ASKER

sorry, per ID

ASKER CERTIFIED SOLUTION