Forum OpenACS Q&A: Response to Postgres feature questions

The query you originally posted also fails to be sufficiently general to adapt to the situation where the previous day might be more than one day off. (Your day_date comparisons in the pseudo-tables only look for adjacent days.) That's why I indicated I would make adjustments depending on the particulars of the situation.

For example, one could make changes like the following to get the previous date for which a row exists:

     SELECT today.id, today.price AS todays_price, 
       yesterday.price AS yesterdays_price,
       (today.price - yesterday.price) AS price_diff,
       DECODE( yesterday.price, 0, NULL, 
           today.price/yesterday.price) as pct_change,
     FROM stocks today, stocks yesterday
     WHERE today.id = yesterday.id
     AND   TRUNC(today.day_date) = TRUNC(:incoming_date)
     AND   TRUNC(yesterday.day_date) = (SELECT MAX(inner.day_date) 
           FROM stocks inner
           WHERE inner.id = today.id
           AND   stocks.day_date < TRUNC(:incoming_date) ) 
     AND   today.id = :incoming_id

or, to get data for the last business day, including accounting for holidays (stored in a table named holiday, something like:

     SELECT today.id, today.price AS todays_price, 
       yesterday.price AS yesterdays_price,
       (today.price - yesterday.price) AS price_diff,
       DECODE( yesterday.price, 0, NULL, 
           today.price/yesterday.price) as pct_change,
     FROM stocks today, stocks yesterday
     WHERE today.id = yesterday.id
     AND   TRUNC(today.day_date) = TRUNC(:incoming_date)
     AND   TRUNC(yesterday.day_date) = (
           SELECT MAX(dt) AS last_business_day
           FROM ( SELECT DECODE( TO_CHAR( :incoming_date, 'dy'), 'fri', 
                  :incoming_date, 
                  (NEXT_DAY(:incoming_date - 7 ,'fri') - ROWNUM + 1) AS dt
             FROM all_objects
             WHERE ROWNUM <= 25 )
           WHERE TO_CHAR(dt,'dy') NOT IN ( 'sat', 'sun' )
           AND NOT EXISTS ( SELECT NULL FROM holiday 
			                   WHERE holiday_date = TRUNC(dt) ) ) ) 
     AND   today.id = :incoming_id

The last-business-day calculation could easily be moved into a view or stored procedure, in both Oracle and PostgreSQL.

(Note: Regarding the last solution, I can't take credit for the date calculation structure. I got it from Tom Kyte's AskTom web site. The rownum/all_objects combination is purely a row-generator to get a list of dates. The page I just referenced gives more details. I'll also plug Tom's new book. It's the best thing I have yet to read about Oracle, and I own some 15 books on Oracle.)

I have concerns, Andrew, about the solution you initially gave simply because it seems both fragile and non-portable, at a conceptual level. It's also using a behavior that Oracle doesn't guarantee (ORDER BY within a view). The solution built on date arithmetic, while syntactically proprietary, moves somewhat easily from database to database. Additionally, for a database like Oracle, the queries I've just given should make good use of the optimizer, and it should recognize that the date arithmetic subquery is invariant for the outer query and need only be done once.