Calculation of Due Dates Based on Number of Plots Chosen

Asked

Viewed 81 times

2

In a function I want to put the parameters that would:

Number of Plots and Total Value

My problem would be how to generate maturity dates of installments

in Postgresql how to work with the dates to generate the plots

Ex: 03 plots

13.11.2019

13.12.2019

13.01.2020

How to generate dates based on the number of plots ?

Thanks for the help

How was your help

CREATE OR REPLACE FUNCTION public.gerar_parcelas (
  p_data_vencimento date,
  p_parcelas integer,
  p_tipo_intervalo integer,
  p_dias_intervalo integer,
  p_dias_para_1_parcela integer,
  p_documento integer
)
RETURNS TABLE (
  data_vencimento date,
  documento varchar
) AS
$body$
declare
    contador integer;
    dia integer;
    mes integer;
    ano integer;
    script text;
    resulta date;
    r record;
BEGIN

    -- Por Dia
    if p_tipo_intervalo = 1 then

        script = 'select '||''''||lpad(cast(p_documento as varchar),7,'0')||''''||'||'||''''||'.'||''''||'||lpad(cast(i+1 as VARCHAR),2,'||''''|| '0'||''''||')||'||''''||'/'||''''||'||'||''''||lpad(cast(p_parcelas as varchar),2,'0')||''''||' as documento,        
            '||''''||p_data_vencimento+p_dias_para_1_parcela||''''||'::date + ((i*'||p_dias_intervalo||')|| '||''''||' days'||''''||')::interval as data_venc
            from generate_series(0,' || p_parcelas - 1 || ') as i;';
    end if;

    -- Por Mes
    if p_tipo_intervalo = 2 then

        script = 'select '||''''||p_data_vencimento+p_dias_para_1_parcela||''''||'::date + ((i)|| '||''''||' Months'||''''||')::interval as data_venc
            from generate_series(0,' || p_parcelas - 1 || ') as i;';
    end if;

    for r in execute script loop  
        documento = r.documento;
        data_vencimento = r.data_venc;
        return next;
    end loop;

END;
  • 1

    You want to use DATEADD (http://www.sqlines.com/postgresql/how-to/dateadd) But be sure to save the dates only after the operation occurs, if you spend a day your date calculation will be one day less, then I recommend to store the number of days in the bank until each maturity, and display the date in the view by calculating this in the code.

  • 1

    For each plot use: sua_data_inicial + numero_da_parcela * (interval '1 month'). Documentation.

  • 1

    uses generate_series: http://sqlfiddle.com/#! 15/9eecb7db59d16c80417c72d1e1f4fbf1/29989

  • I ran a test on him.. generate_series: only that it generated the dates with different days I did the example by putting for example 31.01.2020 with the parameter Interval 1 Month, what happens is that February it generates day 28 until there everything well more in March it also generates day 28 and it would have to be 31, would have to actually spawn on the same day.. and when that day doesn’t exist it would have to be a day later..

  • The Idea of our friend Anomimo worked the way I wanted... I have to adapt to generate plot by plot.. the more did what I wanted to generate always on the same due day, the big problem is when is day 31 off that the generate_series would work perfectly

  • 1

    http://sqlfiddle.com/#! 15/9eecb7db59d16c80417c72d1e1f4fbf1/29996 switched the generate series parameter, and generated 31 / 28 / 31 for 2019 and 31 / 29 / 31 for 2020

  • Solved the Problem.... with generate_seriais.. I already made the function was very good... Thanks to all

Show 2 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.