sql db2 - max date and 2nd max date for rolling 30 days
<p>My current statement pulls prices from a table where the population for the current price is associated with the max or most recent date found in the table. The last price for the record is the price from the 2nd max day found in the table. </p>

<p>How do I create a statement what pulls a rolling 30 days of past dates where the last date is always the previous day found?</p>

<pre><code>SELECT
T1.ID
T1.DT_PRICE
T1.PRICE
T2.LAST_DT_PRICE,   
T2.LAST_PRICE

FROM    
 (SELECT    
  fpr.ID,   
  fpr.DT_PRICE, 
  fpr.PRICE 
  FROM UDBADM.PRICES fpr    

  WHERE fpr.DT_PRICE = (select max(DT_PRICE) 
                       from UDBADM.PRICES)  
    AND fpr.CD_PRICE = 'C'
 ) T1

LEFT JOIN   
  (SELECT   
   fpr2.ID, 
   fpr2.DT_PRICE AS LAST_DT_PRICE,  
   fpr2.PRICE AS LAST_PRICE 
   FROM UDBADM.PRICES fpr2  

   WHERE fpr2.DT_PRCE = (select max(DT_PRICE) 
                         from UDBADM.PRICES 
                         where DT_PRICE &lt; (select 
                                          max(DT_PRICE) 
                                          from UDBADM.PRICES))  
   AND fpr2.CD_PRICE = 'C'
  ) T2
ON T1.ID = T2.ID
</code></pre>

<p>Current output for 1 record:</p>

<pre class="lang-none prettyprint-override"><code>ID     DT_PRICE    PRICE   LAST_DT_PRICE  LAST_PRICE
B5199  11/30/2016  50.3    11/29/2016     50.1
</code></pre>

<p>Expected output for 1 record with rolling 30 days:</p>

<pre class="lang-none prettyprint-override"><code>ID     DT_PRICE    PRICE   LAST_DT_PRICE  LAST_PRICE
B5199  10/18/2016  50.1    10/17/2016     50
B5199  10/19/2016  50      10/18/2016     50.1
B5199  10/20/2016  49.75   10/19/2016     50
B5199  10/21/2016  49.8    10/20/2016     49.75
B5199  10/24/2016  50.12   10/21/2016     49.8
B5199  10/25/2016  50.2    10/24/2016     50.12
B5199  10/26/2016  50.25   10/25/2016     50.2
B5199  10/27/2016  50.1    10/26/2016     50.25
B5199  10/28/2016  49.8    10/27/2016     50.1
B5199  10/31/2016  49.5    10/28/2016     49.8
B5199  11/1/2016   49.2    10/31/2016     49.5
B5199  11/2/2016   50      11/1/2016      49.2
B5199  11/3/2016   50.1    11/2/2016      50
B5199  11/4/2016   50      11/3/2016      50.1
B5199  11/7/2016   49.75   11/4/2016      50
B5199  11/8/2016   49.8    11/7/2016      49.75
B5199  11/9/2016   50      11/8/2016      49.8
B5199  11/10/2016  50      11/9/2016      50
B5199  11/14/2016  50.1    11/10/2016     50
B5199  11/15/2016  50.1    11/14/2016     50.1
B5199  11/16/2016  49.8    11/15/2016     50.1
B5199  11/17/2016  49.5    11/16/2016     49.8
B5199  11/18/2016  49.2    11/17/2016     49.5
B5199  11/21/2016  48.9    11/18/2016     49.2
B5199  11/22/2016  49.8    11/21/2016     48.9
B5199  11/23/2016  50.2    11/22/2016     49.8
B5199  11/25/2016  50.2    11/23/2016     50.2
B5199  11/28/2016  50.2    11/25/2016     50.2
B5199  11/29/2016  50.1    11/28/2016     50.2
B5199  11/30/2016  50.3    11/29/2016     50.1
</code></pre>