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?
– Renato Junior
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.
– Motta
@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.
– thiagofred
@Motta I will try to check this function and adapt it to my need! Thanks!
– thiagofred