CODEWITHSUNDEEP
sqlpostgresqlplpgsqlpostgresql-9.4
sentenza
sentenza

Reputation: 1730

How to get end date based on a start date and duration (workdays) in PL/pgSQL?

I need to calculate the end date of a job based on a start date and a duration (an integer representing the number of working days).

I already wrote a function, based on this answer, that computes the number of weekend days between two dates (start and virtual end). 

-- 0 Sunday
-- 1 Monday
-- 2 Tuesday
-- 3 Wednesday
-- 4 Thursday
-- 5 Friday
-- 6 Saturday
CREATE OR REPLACE FUNCTION weekend_days(date, date) RETURNS INT AS
$$
SELECT COUNT(days)::INT
    FROM generate_series($1, $2, '1 day') AS days
    WHERE EXTRACT(DOW FROM days) BETWEEN 1 AND 5;
$$
LANGUAGE 'sql' IMMUTABLE STRICT;

I want to create a trigger ON INSERT or ON UPDATE that will populate the corresponding end_date column. Obviously, adding the weekend days to the given duration does not solve the problem.

Recursion

Is there any way to make a recursive function to continue adding the weekends or holidays?

EDIT: Maybe another good example could be found in this answer, that mixes weekend and holidays.

Upvotes: 1

Views: 682

Answers (1)

tata.leona
tata.leona

Reputation: 1058

CREATE OR REPLACE FUNCTION is_business_day(p_date date)
  RETURNS boolean AS
$BODY$
DECLARE
    is_business_day boolean;
    dow int;
BEGIN
    dow := extract('dow' from p_date);

    IF dow = 6 OR dow = 0
        THEN
        is_business_day := FALSE;
    ELSE
        is_business_day := TRUE;
    END IF;

    RETURN is_business_day ;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;



CREATE OR REPLACE FUNCTION working_date(date start_date, int duration)
  RETURNS date AS
$BODY$
DECLARE 
    ret_date date;
    loop_date date;
BEGIN   
    --add days
    ret_date := start_date + (duration - 1);

    loop_date := start_date + 1; 
    --add extra day for each no business day between start_date and ret_date
    WHILE loop_date <= ret_date LOOP
        IF not is_business_day(loop_date) THEN
            ret_date := ret_date + 1;
        END IF;
        loop_date := loop_date + 1;
    END LOOP;
    --add day if ret_date is no business day
    WHILE not is_business_day(ret_date) LOOP
        ret_date := ret_date + 1;
    END LOOP;

    RETURN ret_date;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

Upvotes: 3

Related Questions