Use Recursive Date to test Holiday and Weekend and Update Record

(1) By anonymous on 2020-11-05 18:26:19 [link]

I am trying to validate a date against a Holiday table and update if the end day falls on holiday or weekend to next business day. How can I test 3 different (date) conditions in SQLite. Thank You.

(2) By Warren Young (wyoung) on 2020-11-05 18:30:35 in reply to 1 [link]

My advice: don't. You're using the wrong tool for the job. Holidays are *complicated,* messy human constructs, not something that submits well to deterministic tools like SQL.

[This][1] is the right tool.

[1]: https://metacpan.org/pod/distribution/Date-Manip/lib/Date/Manip/Holidays.pod

(3.6) By Keith Medcalf (kmedcalf) on 2020-11-05 20:07:28 edited from 3.5 in reply to 1

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

(4) By jake on 2020-11-06 00:01:57 in reply to 3.6 [link]

Here's an alternative using the [`generate_series`](https://sqlite.org/series.html) table-valued function:

```sql
UPDATE A
   SET scheduled = (SELECT Date(value)
                      FROM generate_series
                     WHERE start = JulianDay(A.scheduled)+1
                       AND strftime('%w', value) NOT IN ('0', '6')
                       AND Date(value) NOT IN Holidays);
```

(5) By Keith Medcalf (kmedcalf) on 2020-11-06 00:47:05 in reply to 4 [link]

Now that is brilliant!  And it will work in a trigger!