Use Recursive Date to test Holiday and Weekend and Update Record
(1) By anonymous on 2020-11-05 18:26:19 [link] [source]
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 [source]
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.
(3.6) By Keith Medcalf (kmedcalf) on 2020-11-05 20:07:28 edited from 3.5 in reply to 1 [link] [source]
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.
(4) By jake on 2020-11-06 00:01:57 in reply to 3.6 [link] [source]
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);
(5) By Keith Medcalf (kmedcalf) on 2020-11-06 00:47:05 in reply to 4 [link] [source]
Now that is brilliant! And it will work in a trigger!