Recursive Update on Date field
(1) By anonymous on 2020-11-05 19:42:43 [link] [source]
Table 1: Holiday Table Holiday Date 2020-11-11 2020-12-25
Closing Date: 2020-11-10 2020-12-25
Expected Result: 2020-11-10 2020-12-28
How do I write a query to update closing date 2020-12-25 from closing date table to 2020-12-28 while comparing it to holiday table and weekend.
2020-12-25 is holiday 2020-12-26 is saturday 2020-12-27 is sunday
My closing date has to be 2020-12-28. I wrote the update query but it works in sequence i wrote them meaning if I update holiday first it works fine but if I update weekend first it seems to be missing holiday update again. How can I test all three conditions for one date after each update in a recursive manner?
(2) By Keith Medcalf (kmedcalf) on 2020-11-05 21:05:02 in reply to 1 [link] [source]
with moveto(rowid, scheduled)
as (
select rowid, date(scheduled, '+1 day')
from Closing
where strftime('%w', scheduled) in ('0', '6')
or exists (
select *
from holidays
where holiday == scheduled
)
union
select rowid, date(scheduled, '+1 day')
from moveto
where strftime('%w', scheduled) in ('0', '6')
or exists (
select *
from holidays
where holiday == scheduled
)
order by rowid
),
updates (rowid, scheduled)
as (
select rowid, max(scheduled)
from moveto
group by rowid
)
update Closing
set scheduled = updates.scheduled
from updates
where Closing.rowid == updates.rowid
;
which results in the following output:
SQLite version 3.34.0 2020-11-05 19:31:02
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table Closing
...> (
...> scheduled text not null check (date(scheduled, '+0 days') == scheduled)
...> )
...> ;
sqlite> create table Holidays
...> (
...> holiday text not null unique check (date(holiday, '+0 days') == holiday)
...> )
...> ;
sqlite> insert into Closing values ('2020-11-10')
...> ;
sqlite> insert into Closing values ('2020-12-25')
...> ;
sqlite> insert into Holidays values ('2020-11-11')
...> ;
sqlite> insert into Holidays values ('2020-12-25')
...> ;
sqlite> select *
...> from Closing
...> ;
┌────────────┐
│ scheduled │
├────────────┤
│ 2020-11-10 │
│ 2020-12-25 │
└────────────┘
sqlite> select *
...> from Holidays
...> ;
┌────────────┐
│ holiday │
├────────────┤
│ 2020-11-11 │
│ 2020-12-25 │
└────────────┘
sqlite> with moveto(rowid, scheduled)
...> as (
...> select rowid, date(scheduled, '+1 day')
...> from Closing
...> where strftime('%w', scheduled) in ('0', '6')
...> or exists (
...> select *
...> from holidays
...> where holiday == scheduled
...> )
...> union
...> select rowid, date(scheduled, '+1 day')
...> from moveto
...> where strftime('%w', scheduled) in ('0', '6')
...> or exists (
...> select *
...> from holidays
...> where holiday == scheduled
...> )
...> order by rowid
...> ),
...> updates (rowid, scheduled)
...> as (
...> select rowid, max(scheduled)
...> from moveto
...> group by rowid
...> )
...> update Closing
...> set scheduled = updates.scheduled
...> from updates
...> where Closing.rowid == updates.rowid
...> ;
sqlite> select *
...> from Closing
...> ;
┌────────────┐
│ scheduled │
├────────────┤
│ 2020-11-10 │
│ 2020-12-28 │
└────────────┘
sqlite>
(3) By Keith Medcalf (kmedcalf) on 2020-11-05 21:46:19 in reply to 2 [link] [source]
You could do this all in triggers so it occurs automatically except that the WITH clause "is not supported" in triggers and in this particular case "not supported" means, in fact, "does not work" as the recursive with is not compiled as recursive (which is somewhat unfortunate).
(4) By Keith Medcalf (kmedcalf) on 2020-11-06 01:02:19 in reply to 2 [link] [source]
Using Jake's wonderful idea here https://sqlite.org/forum/forumpost/c925c2793f here is how it could be implemented with triggers. Adding triggers to recalculate when a holiday is added or updated left as an excercise.
create table Closing
(
scheduled text not null check (date(scheduled, '+0 days') == scheduled),
fixed_scheduled text
);
create table Holidays
(
holiday text not null unique check (date(holiday, '+0 days') == holiday)
);
insert into Holidays values ('2020-11-11');
insert into Holidays values ('2020-12-25');
create trigger ai_closing after insert on closing
begin
update Closing
set fixed_scheduled = (
select date(value)
from generate_series
where start == julianday(Closing.scheduled) + 1
and strftime('%w', value) not in ('0', '6')
and date(value) not in Holidays
)
where rowid == new.rowid;
end;
create trigger au_closing_scheduled after update of scheduled on closing
begin
update Closing
set fixed_scheduled = (
select date(value)
from generate_series
where start == julianday(Closing.scheduled) + 1
and strftime('%w', value) not in ('0', '6')
and date(value) not in Holidays
)
where rowid == new.rowid;
end;
insert into Closing (scheduled) values ('2020-11-10');
insert into Closing (scheduled) values ('2020-12-25');
select * from closing;
(5) By jake on 2020-11-06 02:09:20 in reply to 4 [source]
If for some reason there is a restriction of using vanilla SQLite, or a pre-generate_series CLI, then here is a less efficient solution using recursive triggers:
create table Closing
(
scheduled text not null check (date(scheduled, '+0 days') == scheduled),
fixed_scheduled text
);
create table Holidays
(
holiday text not null unique check (date(holiday, '+0 days') == holiday)
);
insert into Holidays values ('2020-11-11');
insert into Holidays values ('2020-12-25');
create trigger ai_closing after insert on closing
begin
update Closing
set fixed_scheduled = new.scheduled
where rowid == new.rowid;
end;
create trigger au_closing_scheduled after update of scheduled on closing
begin
update Closing
set fixed_scheduled = new.scheduled
where rowid == new.rowid;
end;
create trigger au_closing_fixed_scheduled after update of fixed_scheduled on closing
begin
update Closing
set fixed_scheduled = date(fixed_scheduled, '+1 day')
where rowid == new.rowid
and (strftime('%w', fixed_scheduled) in ('0', '6') or fixed_scheduled in Holidays);
end;
PRAGMA recursive_triggers = ON;
insert into Closing (scheduled) values ('2020-11-10');
insert into Closing (scheduled) values ('2020-12-25');
select * from closing;