All geek questions in one place

Non-looping / Non-looping way to calculate effective date?

I have a table called OffDays where weekends and holidays are stored. I have a table called LeadTime where the time (in days) for the product to be manufactured is stored. Finally, I have a table called “Order” that stores the product and order date.

Can I ask when a product will be completed without using stored procedures or loops?

For instance:

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

The calculation I'm looking for is:

  • 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

, 2008-01-16 .

( procs/loop): , , , . /. , SQL-.

( ): , , , /cron . , .

+3
8

.

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

,

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

WorkDays, . , .

+2

- .

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

:

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 
    )

, ,

RB.

+2

... , sql

+1

- dateadd.

. . , , . , .

-- 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
+1

?

//

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

, .

0

.. , . , jan. 2 - . 1/1/08 1 ( 0, 0). 1/3/08 2, 1/2/08. . , , , .

0

( ) : , " " . , . , , .

This code may be specific to PostgreSQL, sorry if this is not what you are 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;
0
source

This is PostgreSQL syntax, but it’s easy to translate to another 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 the result for orders that are completed after the last date in the offdays table (order number 3), so you should take care to insert offdays on time. It is assumed that orders executed do not start on weekends.

0
source

Source: https://habr.com/ru/post/1697122/

More articles:


All Articles