SQLite Forum

Recursive Update on Date field
Login

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 [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 [link] [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;