SQLite Forum

Use Recursive Date to test Holiday and Weekend and Update Record
Login

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

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