On 12-Jul-2011 10:39 , CRPence wrote:
On 12-Jul-2011 09:30 , Jon Paris wrote:
<<SNIP>> 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.
Any ideas? The only ones we come up with are so darn nasty.
The calendar table should have a numeric data-type column which
represents the number of business days since\including the base-date
in that calendar. Then the shipping date being used has a specific
number of business days since the base date, and some specific
[range of] date has the[ir] number of business days six [or more]
days later. This makes maintenance of the calendar table more
complicated, but a simple one-pass programmed update performed after
any maintenance could ensure that column is set appropriately.
Given the calendar table stores a reasonable rather than excessive
coverage of dates and related data\flags, the file should be relatively
small. The join [though worse, a join on expression] should still be
fairly fast. For my convenience I used digits one and zero for the
equivalent of Yes and No, respectively. Hopefully the following script
is clear enough... skipping the ORDERS references outside QTEMP, either
entirely or until the UPDATE to the file in QTEMP is reviewed.
<code>
create table BusCal /* Business Calendar */
( dt date /* date in business calendar range */
, bd char /* business day: ('1'|'0') */
, bds int /* DAYS accumulator */
, constraint BusCal_pk primary key (dt) ) /* index; unique dates */
;
insert into BusCal values
('2011-07-01', 1, 1) ,('2011-07-02', 0, 1) ,('2011-07-03', 0, 1)
,('2011-07-04', 0, 1) ,('2011-07-05', 1, 2) ,('2011-07-06', 1, 3)
,('2011-07-07', 1, 4) ,('2011-07-08', 1, 5) ,('2011-07-09', 0, 5)
,('2011-07-10', 0, 5) ,('2011-07-11', 1, 6) ,('2011-07-12', 1, 7)
,('2011-07-13', 1, 8) ,('2011-07-14', 1, 9) ,('2011-07-15', 1, 10)
,('2011-07-16', 0, 10) ,('2011-07-17', 0, 10) ,('2011-07-18', 1, 11)
;
create index BusCalBDS on BusCal (bds) /* optional DAYS index */
;
/* The order_date in ORDERS should probably reference the above */
/* business calendar; probably best if shipping_date did too. */
/* optional... also consider ON action clauses */
alter table orders
add constraint orders_fk1 foreign key (order_date)
references buscal (dt)
add constraint orders_fk2 foreign key (shipping_date)
references buscal (dt)
;
/* For each date in the business calendar, find the date that is */
/* six business days into the future; i.e. date + 6 business days */
/* A left outer join is an alternative; giving all rows in BusCal */
select c.dt, d.dt, c.bds, d.bds
from BusCal c inner join BusCal d
on c.bds = d.bds - 6 and d.bd = '1'
; /* report from above select for a visual; (d.BDS-c.BDS)=6: */
DT DT BDS BDS
2011-07-01 2011-07-12 1 7
2011-07-02 2011-07-12 1 7
2011-07-03 2011-07-12 1 7
2011-07-04 2011-07-12 1 7
2011-07-05 2011-07-13 2 8
2011-07-06 2011-07-14 3 9
2011-07-07 2011-07-15 4 10
2011-07-08 2011-07-18 5 11
2011-07-09 2011-07-18 5 11
2011-07-10 2011-07-18 5 11
******** End of data ********
;
/* setup an ORDERS file in QTEMP for an example of UPDATE */
create table qtemp/orders
( order_date for od date
, shipping_date for sd date )
;
insert into qtemp/orders (od) values
('2011-07-01') ,('2011-07-02') ,('2011-07-03') ,('2011-07-04')
,('2011-07-05') ,('2011-07-06') ,('2011-07-07') ,('2011-07-08')
/* The first eight days of Jul, all shipping dates are all NULL */
;
update qtemp/ORDERS o
set o.shipping_date =
( select d.DT
from BusCal c inner join BusCal d
on c.bds = d.bds - 6 and d.bd = '1'
where c.dt = o.order_date )
where o.shipping_date is null
;
select * from qtemp/ORDERS
; /* report from above select for a visual: */
ORDER_DATE SHIPPING_DATE
2011-07-01 2011-07-12
2011-07-02 2011-07-12
2011-07-03 2011-07-12
2011-07-04 2011-07-12
2011-07-05 2011-07-13
2011-07-06 2011-07-14
2011-07-07 2011-07-15
2011-07-08 2011-07-18
******** End of data ********
;
</code>
Regards, Chuck
midrange.com
