Group weekend and holiday values for next business day

Asked

Viewed 90 times

0

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

I have a table invoices with the values of saldo and the vencimento, and dim_holidays with the feriado_data and the feriado_descricao (unimportant).

I made this consultation that apparently is functional, but I believe not to be the best way thinking about 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 correct?

SQL Fiddle: http://sqlfiddle.com/#! 15/09b4d/7

  • What you really need?

  • 1

    http://paposql.blogspot.com.br/2011/08/funcao-para-verifica-dia-util-no.html I would make a Function to retouch the enesimo working day.

  • @RORSCHACH requires that the values of Saturday and Sunday be grouped with those of the Monday (or the next working day) and that the values of the holidays be grouped with those of the next working day. Just know a better way than the query I’m using.

  • @Motta I will try to check this function and adapt it to my need! Thanks!

No answers

Browser other questions tagged

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