add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table cal_
as
select sysdate + level date_
, to_char( sysdate + level, 'Day' ) dname_
, to_char( sysdate + level, 'D' ) dnumber_
from dual
connect by level <= 31 ;
31 rows affected
select * from cal_ ;
DATE_ DNAME_ DNUMBER_
18-MAR-19 Monday 1
19-MAR-19 Tuesday 2
20-MAR-19 Wednesday 3
21-MAR-19 Thursday 4
22-MAR-19 Friday 5
23-MAR-19 Saturday 6
24-MAR-19 Sunday 7
25-MAR-19 Monday 1
26-MAR-19 Tuesday 2
27-MAR-19 Wednesday 3
28-MAR-19 Thursday 4
29-MAR-19 Friday 5
30-MAR-19 Saturday 6
31-MAR-19 Sunday 7
01-APR-19 Monday 1
02-APR-19 Tuesday 2
03-APR-19 Wednesday 3
04-APR-19 Thursday 4
05-APR-19 Friday 5
06-APR-19 Saturday 6
07-APR-19 Sunday 7
08-APR-19 Monday 1
09-APR-19 Tuesday 2
10-APR-19 Wednesday 3
11-APR-19 Thursday 4
12-APR-19 Friday 5
13-APR-19 Saturday 6
14-APR-19 Sunday 7
15-APR-19 Monday 1
16-APR-19 Tuesday 2
17-APR-19 Wednesday 3
create table deliverydays ( partner, deliveryday, dayofweek )
as
select
mod( level, 3 ) + 1
, mod( level, 5 ) + 1
, to_char( sysdate + ( mod( level, 5 ) + 2 ), 'Day' )
from dual
connect by level <= 10 ;
10 rows affected
begin
insert into deliverydays ( partner, deliveryday, dayofweek )
values ( 4, 2, 'Tuesday' ) ;
insert into deliverydays ( partner, deliveryday, dayofweek )
values ( 4, 4, 'Thursday' ) ;
end ;
/
1 rows affected
select * from deliverydays order by partner, deliveryday ;
PARTNER DELIVERYDAY DAYOFWEEK
1 2 Wednesday
1 4 Friday
1 5 Saturday
2 1 Tuesday
2 2 Wednesday
2 3 Thursday
2 5 Saturday
3 1 Tuesday
3 3 Thursday
3 4 Friday
4 2 Tuesday
4 4 Thursday
select
partner, deliveryday , dayofweek
, date_, dname_, dnumber_
from deliverydays D partition by ( partner )
right join cal_ C on D.deliveryday = C.dnumber_
where partner = 4
order by date_ ;
PARTNER DELIVERYDAY DAYOFWEEK DATE_ DNAME_ DNUMBER_
4 null null 18-MAR-19 Monday 1
4 2 Tuesday 19-MAR-19 Tuesday 2
4 null null 20-MAR-19 Wednesday 3
4 4 Thursday 21-MAR-19 Thursday 4
4 null null 22-MAR-19 Friday 5
4 null null 23-MAR-19 Saturday 6
4 null null 24-MAR-19 Sunday 7
4 null null 25-MAR-19 Monday 1
4 2 Tuesday 26-MAR-19 Tuesday 2
4 null null 27-MAR-19 Wednesday 3
4 4 Thursday 28-MAR-19 Thursday 4
4 null null 29-MAR-19 Friday 5
4 null null 30-MAR-19 Saturday 6
4 null null 31-MAR-19 Sunday 7
4 null null 01-APR-19 Monday 1
4 2 Tuesday 02-APR-19 Tuesday 2
4 null null 03-APR-19 Wednesday 3
4 4 Thursday 04-APR-19 Thursday 4
4 null null 05-APR-19 Friday 5
4 null null 06-APR-19 Saturday 6
4 null null 07-APR-19 Sunday 7
4 null null 08-APR-19 Monday 1
4 2 Tuesday 09-APR-19 Tuesday 2
4 null null 10-APR-19 Wednesday 3
4 4 Thursday 11-APR-19 Thursday 4
4 null null 12-APR-19 Friday 5
4 null null 13-APR-19 Saturday 6
4 null null 14-APR-19 Sunday 7
4 null null 15-APR-19 Monday 1
4 2 Tuesday 16-APR-19 Tuesday 2
4 null null 17-APR-19 Wednesday 3
select pid, dday, next_dday, date_, dname_, dnumber_
-- {3} days in between "picking up" and delivery
, case
when dnumber_ > next_dday then ( 7 - dnumber_ + next_dday ) -- next week
else next_dday - dnumber_
end ddiff_
, case -- {4} calculate and format the delivery date
when dnumber_ > next_dday then
to_char( date_ + ( 7 - dnumber_ + next_dday ), 'DD.MM.YYYY' )
else
to_char( date_ + ( next_dday - dnumber_ ), 'DD.MM.YYYY' )
end delivered_on
from (
select
partner as pid, deliveryday as dday
-- {2} lead() : find the next delivery day
, lead( deliveryday ) ignore nulls over ( order by date_ ) as next_dday
, dayofweek
, date_, dname_, dnumber_
-- {1} densify: partner's delivery days/dates plus all days in between
from deliverydays D partition by ( partner )
right join cal_ C on D.deliveryday = C.dnumber_
where partner = 4
)
order by date_ ;
PID DDAY NEXT_DDAY DATE_ DNAME_ DNUMBER_ DDIFF_ DELIVERED_ON
4 null 2 18-MAR-19 Monday 1 1 19.03.2019
4 2 4 19-MAR-19 Tuesday 2 2 21.03.2019
4 null 4 20-MAR-19 Wednesday 3 1 21.03.2019
4 4 2 21-MAR-19 Thursday 4 5 26.03.2019
4 null 2 22-MAR-19 Friday 5 4 26.03.2019
4 null 2 23-MAR-19 Saturday 6 3 26.03.2019
4 null 2 24-MAR-19 Sunday 7 2 26.03.2019
4 null 2 25-MAR-19 Monday 1 1 26.03.2019
4 2 4 26-MAR-19 Tuesday 2 2 28.03.2019
4 null 4 27-MAR-19 Wednesday 3 1 28.03.2019
4 4 2 28-MAR-19 Thursday 4 5 02.04.2019
4 null 2 29-MAR-19 Friday 5 4 02.04.2019
4 null 2 30-MAR-19 Saturday 6 3 02.04.2019
4 null 2 31-MAR-19 Sunday 7 2 02.04.2019
4 null 2 01-APR-19 Monday 1 1 02.04.2019
4 2 4 02-APR-19 Tuesday 2 2 04.04.2019
4 null 4 03-APR-19 Wednesday 3 1 04.04.2019
4 4 2 04-APR-19 Thursday 4 5 09.04.2019
4 null 2 05-APR-19 Friday 5 4 09.04.2019
4 null 2 06-APR-19 Saturday 6 3 09.04.2019
4 null 2 07-APR-19 Sunday 7 2 09.04.2019
4 null 2 08-APR-19 Monday 1 1 09.04.2019
4 2 4 09-APR-19 Tuesday 2 2 11.04.2019
4 null 4 10-APR-19 Wednesday 3 1 11.04.2019
4 4 2 11-APR-19 Thursday 4 5 16.04.2019
4 null 2 12-APR-19 Friday 5 4 16.04.2019
4 null 2 13-APR-19 Saturday 6 3 16.04.2019
4 null 2 14-APR-19 Sunday 7 2 16.04.2019
4 null 2 15-APR-19 Monday 1 1 16.04.2019
4 2 null 16-APR-19 Tuesday 2 null null
4 null null 17-APR-19 Wednesday 3 null null