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 [link] [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 [source]
... 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] [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!