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
function lsmb__next_business_day(in_date date, in_calendar integer,
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
function lsmb__next_business_day_modified(in_date date, in_calendar integer,
in_direction integer)
returns date
language plpgsql
as $$
declare
t_date date;
begin
select lsmb__next_business_day(in_date, in_calendar, in_direction)
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
function lsmb__closest_business_day(in_date date, in_calendar integer,
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
select lsmb__next_business_day(in_date, in_calendar, 1)
into t_date;
elseif in_type = 3 then -- modified following
select lsmb__next_business_day_modified(in_date, in_calendar, 1)
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
select lsmb__next_business_day_modified(in_date, in_calendar, -1)
into t_date;
else
raise
end if;
return t_date;
end;
$$;
On Sun, Apr 28, 2013 at 8:50 PM, Erik Huelsmann <[email protected]> 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
> function lsmb__adjust_to_next_workday(in_date date)
> 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
> function lsmb__next_business_day(in_date date, in_calendar integer,
> 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
> function lsmb__next_business_day_modified(in_date date, in_calendar
> integer,
> in_direction integer)
> returns date
> language pgplsql
> as $$
> declare
> t_date date;
> begin
> select lsmb__next_business_day(in_date, in_calendar, in_direction)
> 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
> function lsmb__closest_business_day(in_date date, in_calendar integer,
> 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
> select lsmb__next_business_day(in_date, in_calendar, 1)
> into t_date;
> elseif in_type = 3 then -- modified following
> select lsmb__next_business_day_modified(in_date, in_calendar, 1)
> 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
> select lsmb__next_business_day_modified(in_date, in_calendar, -1)
> into t_date;
> else
> raise <some error>
> end if;
>
> return t_date;
> end;
> $$;
>
>
------------------------------------------------------------------------------
Try New Relic Now & We'll Send You this Cool Shirt
New Relic is the only SaaS-based application performance monitoring service
that delivers powerful full stack analytics. Optimize and monitor your
browser, app, & servers with just a few lines of code. Try New Relic
and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr
_______________________________________________
Ledger-smb-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
