Chuck-
Thanks for replying. After watching all these complicated Selects, CTE's and UDF's suggested throughout the day yesterday, I had decided to actually run my SQL trying to find out that maybe it wasn't good enough performance wise. I totally missed the requirement of couting business days. Actually, it took me re-reading your reply 3 times before I realized my mistake.
Thanks again.
-Tom
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, July 13, 2011 1:02 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Inspiration needed
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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
