On 12-Jul-2011 11:02 , Tom E Stieger wrote:
Would the following work?
Select order_date,
( select min(date) from calendar_table
where date>= order_date + 6 days
and business_day = 'Y')) as Ship_Date
from ORDERS
That would not work :-( The subselect uses real date arithmetic
versus using a date calculation specific to business days. So even
though the request might appear to limit the results to only business
days, any business date which is still less than the number of business
days later might still be selected incorrectly, because only the
sufficient number of any-type [i.e. both non-business and business] days
were added. That is why some other solutions offered deferred to the
ROW_NUMBER on the business-days rows as a means to enable the arithmetic
to locate the date value six ordered rows [i.e. six business days] later.
If that attempt to explain [being difficult to describe in just
words] is not clear, then perhaps a restatement by example:
Consider an order made on Fri 01-Jul-2011, selecting the minimal date
value from the calendar table which is both a business day and at least
six ordinary calendar days into the future will select 07-Jul-2011
instead of 12-Jul-2011; i.e. six days into the future is a business day,
the date 07-Jul-2011, yet that selection did not ensure all days between
the order date and the selected date were also business days:
create table calendar_table ("DATE" date, business_day char)
insert into calendar_table values
('2011-07-01', 'Y') ,('2011-07-02', 'N') ,('2011-07-03', 'N')
,('2011-07-04', 'N') ,('2011-07-05', 'Y') ,('2011-07-06', 'Y')
,('2011-07-07', 'Y') ,('2011-07-08', 'Y') ,('2011-07-09', 'N')
,('2011-07-10', 'N') ,('2011-07-11', 'Y') ,('2011-07-12', 'Y')
Counting six elements\rows past the first [01-Jul], though counting
only those with the value 'Y', locates the last row; i.e. 12-Jul. That
is the desired outcome. Selecting all rows at least six days later, and
picking the minimum date which is also a business day, incorrectly
selects 07-Jul.
Selecting only those rows with business_day='Y' and assigning a row
number ordered by date produces a table like the following, where the
difference between the first and last [for this small subset of data] is
six rows which is also a difference of six business days:
(1, '2011-07-01', 'Y')
,(2, '2011-07-05', 'Y') ,(3, '2011-07-06', 'Y')
,(4, '2011-07-07', 'Y') ,(5, '2011-07-08', 'Y')
,(6, '2011-07-11', 'Y') ,(7, '2011-07-12', 'Y')
Regards, Chuck
midrange.com
