Non-iterative / Non-looping Way To Calculate Effective Date?

Question

I have a table called OffDays, where weekends and holiday dates are kept. I have a table called LeadTime where amount of time (in days) for a product to be manufactured is stored. Finally I have a table called Order where a product and the order date is kept.

Is it possible to query when a product will be finished manufacturing without using stored procedures or loops?

For example:

• OffDays has 2008-01-10, 2008-01-11, 2008-01-14.
• LeadTime has 5 for product 9.
• Order has 2008-01-09 for product 9.

The calculation I'm looking for is this:

• 2008-01-09 1
• 2008-01-10 x
• 2008-01-11 x
• 2008-01-12 2
• 2008-01-13 3
• 2008-01-14 x
• 2008-01-15 4
• 2008-01-16 5

I'm wondering if it's possible to have a query return 2008-01-16 without having to use a stored procedure, or calculate it in my application code.

Edit (why no stored procs / loops): The reason I can't use stored procedures is that they are not supported by the database. I can only add extra tables / data. The application is a third party reporting tool where I can only control the SQL query.

Edit (how i'm doing it now): My current method is that I have an extra column in the order table to hold the calculated date, then a scheduled task / cron job runs the calculation on all the orders every hour. This is less than ideal for several reasons.

Answer 1

Just calculate it in application code ... much easier and you won't have to write a really ugly query in your sql

Answer 2

The best approach is to use a Calendar table.

See http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.

Then your query could look something like:

SELECT c.dt, l.*, o.*, c.*
    FROM [statistics].dbo.[calendar] c, 
    [order] o  JOIN
    lead l ON l.leadId = o.leadId
    WHERE c.isWeekday = 1 
    AND   c.isHoliday =0 
    AND   o.orderId = 1
    AND   l.leadDays = ( 
        SELECT COUNT(*)  
            FROM [statistics].dbo.Calendar c2 
            WHERE c2.dt >= o.startDate
            AND c2.dt <= c.dt 
            AND c2.isWeekday=1 
            AND c2.isHoliday=0 
    )

Hope that helps,

RB.

Answer 3

Why are you against using loops?

//some pseudocode

int leadtime = 5;
date order = 2008-01-09;
date finishdate = order;
while (leadtime > 0) {
finishdate.addDay();
if (!IsOffday(finishdate)) leadtime--;
}
return finishdate;

this seems like a too simple function to try to find a non-looping way.

Answer 4

Are you opposed to using a loop or cursor in your sql statement - do you just want to avoid a stored proc or client side calculation?

Answer 5

Hmm.. one solution could be to store a table of dates with an offset based on a count of non-off days from the beginning of the year. Lets say jan. 2 is an off day. 1/1/08 would have an offset of 1 (or 0 if you like to start from 0). 1/3/08 would have an offset of 2, because the count skips 1/2/08. From there its a simple calculation. Get the offset of the order date, add the lead time, then do a lookup on the calculated offset to get the end date.

Answer 6

You can generate a table of working days in advance.

WDId | WDDate
-----+-----------
4200 | 2008-01-08
4201 | 2008-01-09
4202 | 2008-01-12
4203 | 2008-01-13
4204 | 2008-01-16
4205 | 2008-01-17

Then do a query such as

SELECT DeliveryDay.WDDate FROM WorkingDay OrderDay, WorkingDay DeliveryDay, LeadTime, Order where DeliveryDay.WDId = OrderDay.WDId + LeadTime.LTDays AND OrderDay.WDDate = '' AND LeadTime.ProductId = Order.ProductId AND Order.OrderId = 1234

You would need a stored procedure with a loop to generate the WorkingDays table, but not for regular queries. It's also fewer round trips to the server than if you use application code to count the days.

Answer 7

here's one way - using the dateadd function.

I need to take this answer off the table. This isn't going to work properly for long lead times. It was simply adding the # of off days found in the lead time and pushing the date out. This will cause a problem when more off days show up in the new range.

-- Setup test
create table #odays (offd datetime)
create table #leadtime (pid int , ltime int)
create table [#order] (pid int, odate datetime)


insert into #odays 
select '1/10/8'
insert into #odays 
select '1/11/8'
insert into #odays 
select '1/14/8'


insert into #Leadtime
values (3,5)
insert into #leadtime
values (9, 5)

insert into #order 
values( 9, '1/9/8')

select dateadd(dd, 
(select count(*)-1 
   from #odays 
   where offd between odate and  
    (select odate+ltime 
       from #order o 
       left join #leadtime l 
         on o.pid = l.pid 
       where l.pid = 9
     )
 ),
 odate+ltime) 
 from #order o 
 left join #leadtime l  
   on o.pid = l.pid 
 where o.pid = 9

Answer 8

One way (without creating another table) is using a sort of ceiling function: for each offdate, find out how many "on dates" come before it, relative to the order date, in a subquery. Then take the highest number that's less than the lead time. Use the date corresponding to that, plus the remainder.

This code may be specific to PostgreSQL, sorry if that's not what you're using.

CREATE DATABASE test;
CREATE TABLE offdays
(
  offdate date NOT NULL,
  CONSTRAINT offdays_pkey PRIMARY KEY (offdate)
);
insert into offdays (offdate) values ('2008-01-10');
insert into offdays (offdate) values ('2008-01-11');
insert into offdays (offdate) values ('2008-01-14');
insert into offdays (offdate) values ('2008-01-18'); -- just for testing
CREATE TABLE product
(
  id integer NOT NULL,
  CONSTRAINT product_pkey PRIMARY KEY (id)
);
insert into product (id) values (9);
CREATE TABLE leadtime
(
  product integer NOT NULL,
  leaddays integer NOT NULL,
  CONSTRAINT leadtime_pkey PRIMARY KEY (product),
  CONSTRAINT leadtime_product_fkey FOREIGN KEY (product)
      REFERENCES product (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
insert into leadtime (product, leaddays) values (9, 5);
CREATE TABLE "order"
(
  product integer NOT NULL,
  "start" date NOT NULL,
  CONSTRAINT order_pkey PRIMARY KEY (product),
  CONSTRAINT order_product_fkey FOREIGN KEY (product)
      REFERENCES product (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
insert into "order" (product, "start") values (9, '2008-01-09');

-- finally, the query:

select e.product, offdate + (leaddays - ondays)::integer as "end"
from
(
    select c.product, offdate, (select (a.offdate - c."start") - count(b.offdate) from offdays b where b.offdate < a.offdate) as ondays, d.leaddays
    from offdays a, "order" c
    inner join leadtime d on d.product = c.product
) e
where leaddays >= ondays
order by "end" desc
limit 1;

Answer 9

This is PostgreSQL syntax but it should be easy to translate to other SQL dialect

--Sample data
create table offdays(datum date);

insert into offdays(datum)
select to_date('2008-01-10','yyyy-MM-dd') UNION 
select to_date('2008-01-11','yyyy-MM-dd') UNION 
select to_date('2008-01-14','yyyy-MM-dd') UNION 
select to_date('2008-01-20','yyyy-MM-dd') UNION
select to_date('2008-01-21','yyyy-MM-dd') UNION
select to_date('2008-01-26','yyyy-MM-dd');

create table leadtime (product_id integer , lead_time integer);
insert into leadtime(product_id,lead_time) values (9,5);

create table myorder (order_id integer,product_id integer, datum date);
insert into myorder(order_id,product_id,datum) 
values (1,9,to_date('2008-01-09','yyyy-MM-dd'));
insert into myorder(order_id,product_id,datum) 
values (2,9,to_date('2008-01-16','yyyy-MM-dd'));
insert into myorder(order_id,product_id,datum) 
values (3,9,to_date('2008-01-23','yyyy-MM-dd'));

--Query
select order_id,min(finished_date)
FROM 
    (select mo.order_id,(mo.datum+lead_time+count(od2.*)::integer-1) as finished_date
     from 
         myorder mo
         join leadtime lt on (mo.product_id=lt.product_id)
         join offdays od1 on (mo.datum<od1.datum)
         left outer join offdays od2 on (mo.datum<od2.datum and od2.datum<od1.datum)
     group by  mo.order_id,mo.datum,lt.lead_time,od1.datum
     having (mo.datum+lead_time+count(od2.*)::integer-1) < od1.datum) tmp
group by 1;       

--Results :
1    2008.01.16
2    2008.01.22

This will not return result for orders that would be finished after last date in offdays table (order number 3), so you must take care to insert offdays on time.It is assumed that orders do not start on offdays.