CODEWITHSUNDEEP
sqlselectdb2
Dritt Skalle
Dritt Skalle

Reputation: 23

Select: add a column based on sub select

I have the following scenario:

Using SQLDbx - database: DB2

The following SQL

WITH priceanddates AS 
(
    SELECT a.*
    FROM FPRICE a
    INNER JOIN
    (
        SELECT edate, MAX(idx) maxIdx
        FROM FPRICE
        WHERE price>0
        GROUP BY edate
    ) b ON a.edate = b.edate AND a.idx = b.maxIdx
)

SELECT *
FROM priceanddates 
WHERE fcode='XYZ'
ORDER BY edate

yields:

fcode    edate       idx    price
XYZ      2010-09-17    2    34,3
XYZ      2010-09-20    2    34,3
XYZ      2010-09-21    2    34,3
XYZ      2010-09-22    2    34,3
XYZ      2010-09-23    2    35,7
XYZ      2010-09-24    2    34,5
XYZ      2010-09-27    2    35,5

BUT - I need something more...I would like to add a column in the final select which says something about the date's validity, like this:

fcode    edate_from    edate_to       idx    price
XYZ      2010-09-17    2010-09-20       2    34,3
XYZ      2010-09-20    2010-09-21       2    34,3
XYZ      2010-09-21    2010-09-22       2    34,3
XYZ      2010-09-22    2010-09-23       2    34,3
XYZ      2010-09-23    2010-09-24       2    35,7
XYZ      2010-09-24    2010-09-27       2    34,5
XYZ      2010-09-27    2010-09-30       2    35,5

So, edate_to is based on the "next" edate_from (as the view is currently ordered by date), or (as I hopefully deduced correctly) edate_to is simply the MIN(edate larger than "current" edate)

The final cutoff date for all dates in the DB is 2010-09-30, alas the last edate_to must always be 2010-09-30.

Obviously, I am not very skilled when it comes to writing SQL, hopefully someone can point me in the right direction - or even better - provide a solution :) I did search around SO for a while, but I couldn't really find what I was looking for...

Cheers, Dritt.

Upvotes: 2

Views: 89

Answers (2)

Justin
Justin

Reputation: 9724

Maybe:

WITH priceanddates AS 
(
    SELECT a.*,
           (SELECT COALESCE(MIN(aa.edate), '2010-09-30')
            FROM FPRICE aa 
            WHERE aa.edate > a.edate) AS edate_to       
    FROM FPRICE a
    INNER JOIN
    (
        SELECT edate, MAX(idx) maxIdx
        FROM FPRICE
        WHERE price>0
        GROUP BY edate
    ) b ON a.edate = b.edate AND a.idx = b.maxIdx
)

SELECT *
FROM priceanddates 
WHERE fcode='XYZ'
ORDER BY edate

Upvotes: 1

Rachcha
Rachcha

Reputation: 8816

Use the new LEAD function. More on it here.

WITH priceanddates AS 
(
    SELECT a.*
    FROM FPRICE a
    INNER JOIN
    (
        SELECT edate, MAX(idx) maxIdx
        FROM FPRICE
        WHERE price>0
        GROUP BY edate
    ) b ON a.edate = b.edate AND a.idx = b.maxIdx
)

SELECT fcode
       , edate AS edate_from
       , LEAD (edate) OVER (ORDER BY edate) AS edate_to
       , idx
       , price
FROM priceanddates 
WHERE fcode='XYZ'
ORDER BY edate

Upvotes: 0

Related Questions