• Subject: Re: Business day calculations
• From: Erik Huelsmann <..hidden..>
• Date: Sun, 28 Apr 2013 21:04:50 +0200

Now with the most obvious errors fixed:

create or replace
function lsmb__is_workday(in_date date, in_calendar integer)
returns boolean
language sql
as \$\$
select extract(isodow from \$1) <= 5;
\$\$;

create table holidays (
calendar integer,
holidate date,
primary key (calendar, holidate)
);

create or replace
function lsmb__is_holiday(in_date date, in_calendar integer)
returns boolean
language plpgsql
as \$\$
begin
if exists(select *
from holidays
where holidate = in_date
and calendar = in_calendar) then
return true;
else
return false;
end if;
end;
\$\$;

create or replace
in_direction integer)
returns date
language plpgsql
as \$\$
declare
t_date date;
begin
t_date := in_date;
loop
exit when lsmb__is_workday(t_date, in_calendar)
and (not lsmb__is_holiday(t_date, in_calendar));

t_date := t_date + in_direction;
end loop;
return t_date;
end;
\$\$;

create or replace
in_direction integer)
returns date
language plpgsql
as \$\$
declare
t_date date;
begin
into t_date;

if extract(month from in_date) != extract(month from t_date) then
select lsmb__next_business_day(in_date, in_calendar, -1 * in_direction)
into t_date;
end if;

return t_date;
end;
\$\$;

create or replace
in_type integer)
returns date
language plpgsql
as \$\$
declare
t_date date;
begin
if in_type = 1 then -- no adjustment
t_date := in_date;
elseif in_type = 2 then -- following
into t_date;
elseif in_type = 3 then -- modified following
into t_date;
elseif in_type = 4 then -- previous
select lsmb__next_businss_day(in_date, in_calendar, -1)
into t_date;
elseif in_type = 5 then -- modified previous
into t_date;
else
raise
end if;

return t_date;
end;
\$\$;

On Sun, Apr 28, 2013 at 8:50 PM, Erik Huelsmann wrote:
Hi Chris, all,

For some reason since forever I wanted to write business day calculating routines. Today I took the time to do so. I've not "compiled" nor tested them, but they're a start. Do we have a corner in the repository to store these kinds of half-done, well meant contributions?

The idea is to have one table with holiday dates for separate calendars. I imagine different regions using their own calendars. For ultimate localization, the function lsmb__is_workday could be changed to query a table which lists all non-workdays (to minimize the size of the table) -- this could also be region based. I've yet to decide if this should be part of the same calendar or not. It probably should, but that's a gut feeling now.

Regards,

Erik.

create or replace
function lsmb__is_workday(in_date date)
returns boolean
language sql
as \$\$
select extract(isodow from \$1) <= 5;
\$\$;

create or replace
returns date
language pgplsql
as \$\$
declare
t_date date;
begin
t_date := in_date;
loop
exit when lsmb__is_workday(t_date);
t_date := t_date + 1;
end loop;
return t_date;
end
\$\$;

create or replace
function lsmb__is_holiday(in_date date, in_calendar integer)
returns boolean
language plpgsql
as \$\$
if exists(select *
from holidays
where holidate = in_date
and calendar = in_calendar) then
return true;
else
return false;
end if;
end;
\$\$

create or replace
in_direction integer)
returns date
language pgplsql
as \$\$
declare
t_date date;
begin
t_date := in_date;
loop
exit when (not lsmb__is_workday(t_date, in_calendar))
and (not lsmb__is_holiday(t_date, in_calendar));

t_date := t_date + in_direction;
end loop;
return t_date;
end;
\$\$;

create or replace
in_direction integer)
returns date
language pgplsql
as \$\$
declare
t_date date;
begin
into t_date;

if extract(month from in_date) != extract(month from t_date) then
select lsmb__next_business_day(in_date, in_calendar, -1 * in_direction)
into t_date;
end if;

return t_date;
end;
\$\$;

create or replace
in_type integer)
returns date
language pgplsql
as \$\$
declare
t_date date;
begin
if in_type = 1 then -- no adjustment
t_date := in_date;
elseif in_type = 2 then -- following
into t_date;
elseif in_type = 3 then -- modified following
into t_date;
elseif in_type = 4 then -- previous
select lsmb__next_businss_day(in_date, in_calendar, -1)
into t_date;
elseif in_type = 5 then -- modified previous