SQLite Forum

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

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
;
```

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.
```