Group weekend and holiday values for next business day

0

Well, I have the following problem, I need to group all balance amounts that fall on Saturday, Sunday, or holiday to the next business day.

I have a invoices table with the values of saldo and vencimento , and dim_fered with feriado_data and feriado_descricao (not important) .

I made this query that is apparently functional, but I do not think I'm in the best shape thinking of performance.

SELECT
    vencimento_util
    , SUM(saldo) AS saldo
FROM    
(
    SELECT 
        vencimento AS vencimento_original
        , vencimento_data AS vencimento_nao_fds
        , CASE  
            WHEN EXTRACT ('ISODOW' FROM vencimento_data) = 5 AND feriado_data IS NOT NULL THEN vencimento_data + interval '3 days'
            WHEN EXTRACT ('ISODOW' FROM vencimento_data) <> 5 AND feriado_data IS NOT NULL THEN vencimento_data + interval '1 day'
            ELSE vencimento_data
        END AS vencimento_util
        , saldo
        , feriado_data
        FROM
        (
        SELECT 
            vencimento
            , EXTRACT('isodow' FROM vencimento) AS dia  
            , CASE WHEN 
                EXTRACT('isodow' FROM vencimento) > 5 THEN 1
                ELSE EXTRACT('isodow' FROM vencimento) 
            END AS dia_semana
            ,CASE 
                WHEN EXTRACT('isodow' FROM vencimento) < 6 THEN vencimento 
                WHEN EXTRACT('isodow' FROM vencimento) = 6 THEN vencimento + interval '2 days'
                WHEN EXTRACT('isodow' FROM vencimento) = 7 THEN vencimento + interval '1 day'       
            END AS vencimento_data
            , sum(saldo) AS saldo
        FROM faturas
        GROUP by vencimento
        ORDER by vencimento ASC
        ) t
        LEFT JOIN dim_feriados ON vencimento_data = feriado_data
    ) t2
GROUP BY vencimento_util
ORDER BY vencimento_util

Could someone help me fix it?

SQL Fiddle: link

    
asked by anonymous 12.09.2017 / 23:33

0 answers