Re: SQL Inspiration needed

On Tue, 12 Jul 2011 12:30:45 -0400, Jon Paris wrote:

I'm collecting alternative solutions for a couple of SQL "problems" -
things that would be relatively easy (if laborious) in native RPG.

So ... given that I have a table ORDERS which contains order and
shipping dates, I need to populate the shipping dates such that the
shipping date is 6 "business days" after the order date.

Business days can be determined from a calendar table keyed on date and
which contains a "Y" or "N" to indicate whether that date is a business
day or not.

Jon,

I think it needs two passes, but maybe something like this would do it...

WITH A (CALDATE) AS (
SELECT CALDATE FROM CALENDAR
WHERE CALDATE BETWEEN :OrderDate AND :OrderDate + 11 DAYS
AND BUSINESS_DAY = 'Y' ),
B (CALDATE) AS (
SELECT A.CALDATE
FROM A
ORDER BY A.CALDATE
FETCH FIRST 7 ROWS ONLY )
SELECT MAX(B.CALDATE) FROM B;