steveW steveW - 7 days ago 9
SQL Question

sql db2 - max date and 2nd max date for rolling 30 days

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.

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?

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 < (select
max(DT_PRICE)
from UDBADM.PRICES))
AND fpr2.CD_PRICE = 'C'
) T2
ON T1.ID = T2.ID


Current output for 1 record:

ID DT_PRICE PRICE LAST_DT_PRICE LAST_PRICE
B5199 11/30/2016 50.3 11/29/2016 50.1


Expected output for 1 record with rolling 30 days:

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

Answer
WITH cte AS (
    SELECT
       fpr.Id
       ,fpr.DT_PRICE
       ,fpr.PRICE
       ,ROW_NUMBER() OVER (PARTITION BY fpr.Id ORDER BY fpr.DT_PRICE) as IdRowNumber
    FROM
       UDBADM.PRICES fpr
    WHERE
       fpr.CE_PRICE = 'C'
)

SELECT
    c1.Id
    ,c1.DT_PRICE
    ,c1.PRICE
    ,c2.DT_PRICE as LAST_PRICE_DATE
    ,c2.PRICE as LAST_PRICE
FROM
    cte c1
    INNER JOIN cte c2
    ON c1.Id = c2.Id
    AND c1.IdRowNumber = c2.IdRowNumber + 1

Because you have gaps in your dates you could generate a row_number based on date and then use the row number in a self join to get the price before. Note the INNER JOIN will give you your exact output but you may want to consider LEFT JOIN so that you will have the initial price date of 11/17/2016 represented but that is up to you.

As far as limiting to a rolling 30 days how to accomplish that will depend on what you mean by that.

If you want any prices in the last 30 days then add a where condition in the Common Table Expression which looks for DT_PRICE > current date - 30 days.

If however you want the last 30 price records regardless of dates then just flip the row_number to be DESCENDING and select the first 30 records like this:

WITH cte AS (
    SELECT
       fpr.Id
       ,fpr.DT_PRICE
       ,fpr.PRICE
       ,ROW_NUMBER() OVER (PARTITION BY fpr.Id ORDER BY fpr.DT_PRICE DESC) as IdRowNumber
    FROM
       UDBADM.PRICES fpr
    WHERE
       fpr.CE_PRICE = 'C'
)

SELECT
    c1.Id
    ,c1.DT_PRICE
    ,c1.PRICE
    ,c2.DT_PRICE as LAST_PRICE_DATE
    ,c2.PRICE as LAST_PRICE
FROM
    cte c1
    INNER JOIN cte c2
    ON c1.Id = c2.Id
    AND c1.IdRowNumber = c2.IdRowNumber - 1
WHERE
    c1.IdRowNumber <= 30