SQLite Forum

Use Recursive Date to test Holiday and Weekend and Update Record
Login
... WHERE `<condition1>` conjunction `<condition2>` conjunction `<condition3>`

where condition1,2,3 are your conditions  
and conjunction is the conjunction of those conditions you want (ie, AND or OR)

For example if you have a table of dates in A, and a table of Holidays (called Holiday) then you might do, assuming the following table definitions as follows and that the dates are "wall clock local time":

```
create table A
(
  scheduled text not null check (date(scheduled, '+0 days') == scheduled)
);
create table Holidays
(
  holiday text not null unique check (date(holiday, '+0 days') == holiday)
);
```

and you populate those tables with data, then you might do so as follows:

```
with moveto(rowid, scheduled)
  as (
        select rowid, date(scheduled, '+1 day') -- move to next day if
          from A
         where strftime('%w', scheduled) in ('0', '6') -- falls on Sunday or Saturday
            or exists (select * from holidays where holiday == scheduled) -- or on a holiday
     union     -- use a b-tree since we need one anyway
        select rowid, date(scheduled, '+1 day') -- move to next day if
          from moveto
         where strftime('%w', scheduled) in ('0', '6') -- falls on Sunday or Saturday
            or exists (select * from holidays where holiday == scheduled) -- or a holiday
      order by rowid -- put in correct order to optimize b-tree
     ),
     updates (rowid, scheduled) -- find the latest date for each row to update
  as (
         select rowid, max(scheduled)
           from moveto
       group by rowid
      )
update A 
   set scheduled = updates.scheduled
  from updates
 where A.rowid == updates.rowid
;
```

Example,

```
insert into A values ('2020-11-06');
insert into Holidays values ('2020-11-06');
select * from A;
-- run the recursive CTE above
select * from A;
```

Note -- recursive CTE fixed so it actually works properly. Also added comments and slight optimizations.