[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Business day calculations

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 <..hidden..> 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;
$$;