Re: SQL Inspiration needed

[Pete Hall <pete@xxxxxxxxxxxxxx>]

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;