Use Recursive Date to test Holiday and Weekend and Update Record
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.
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 is the right tool.
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 ;
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.
Here's an alternative using the
generate_series table-valued function:
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);