Working days

[jerry-sc]

Good afternoon guys,

I need to bring in a single line, the amount of business days in a single month and with a certain "weight". For example: 02/05/2017 (Tuesday) the weight is 1 (considers 1 day). Already for 06/05/2017 (Saturday) I need you to consider 0.34 day.

Logo, I need through the result of a single line to go "adding" the weight of each day that passed. I did this SQL below, which brings the weight of the day, but it does not bring the accumulated. Maybe someone has an idea to do this. Thanks!

Select all

 
select 1 AS FILIAL, 
       to_char(sysdate,'mm') AS mês, 
       to_char(sysdate,'yyyy') AS ANO, 
       --to_char(sysdate,'dd/mm/yyyy'), 
CASE 
       WHEN to_char(sysdate,'d') IN (1) THEN 0 
       WHEN to_char(sysdate,'d') IN (7) THEN 0.34 
       WHEN to_char(sysdate,'d') IN (2,3,4,5,6) THEN 1 
END AS QUANT_DIAS_PRODUCAO 
from dual 
 

[spernega]

Good afternoon,

Thus:

Select all

with calendario as(select trunc(sysdate) - Level Dt_Emis 
                   From   dual 
                   CONNECT BY LEVEL <= 7200 
                   union all 
                   select trunc(sysdate)  Dt_Emis 
                   From   dual 
                   union all 
                   select trunc(sysdate) + Level Dt_Emis 
                   From   dual 
                   CONNECT BY LEVEL <= 7200) 
select to_char(dt_emis,'mm') mês, 
       to_char(dt_emis,'yyyy') ano, 
sum(CASE WHEN to_char(dt_emis,'d') IN (1) THEN 0 
         WHEN to_char(dt_emis,'d') IN (7) THEN 0.34 
         WHEN to_char(dt_emis,'d') IN (2,3,4,5,6) THEN 1 
    END) QUANT_DIAS_PRODUCAO 
from   calendario 
where  dt_emis between to_date('01/04/2017','dd/mm/yyyy') and to_date('31/12/2017','dd/mm/yyyy') 
group by to_char(dt_emis,'mm'), to_char(dt_emis,'yyyy') 
order by to_char(dt_emis,'mm'), to_char(dt_emis,'yyyy') 
/

[jerry-sc]

Good afternoon.

grateful for his collaboration.

It worked, however, maybe I have not expressed myself well.

I need him to consider the days they have passed. For example, today he would put for month 05, only 1, tomorrow he would put 2, and so on. Only on Saturday he would consider 0.34 and Sunday 0.

Is it possible to do this?

Thank you very much.

[spernega]

Can you change in the WHERE

Select all

... 
from calendario 
where dt_emis between to_date('01/04/2017','dd/mm/yyyy') and trunc(sysdate)

or

Select all

... 
from calendario 
where dt_emis between to_date('01/04/2017','dd/mm/yyyy') and trunc(sysdate)-1

would solve?

[jerry-sc]

Solve yes!

I do not want to abuse, but as you are ninja ... What would I need to implement, not to consider the holidays?

Thank you again!

[spernega]

There is more fun ...

You would have to create holiday tables, remembering that there are municipal, state and national holidays.
and that there are part-time holidays.


DAI creates a function in these tables to return 0 (zero) when it is a holiday or that the SELECT does not bring the dates in these tables ...

99] ex.

Select all

from   calendario 
where  dt_emis between to_date('01/04/2017','dd/mm/yyyy') and to_date('31/12/2017','dd/mm/yyyy') 
and    dt_emis not in( select nas tabelas de feriado)