Only specific days of the year

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Sun, 06 Oct 2013 12:37 pm

I am beginner in the area of pl/sql was wondering how do I create an anonymous block that displays during 365 days (1 year) only dates that fall on Saturday and Sunday, after the entry of a date. After the request date entry, display sequentially the date and day of the week that represents that date. I know I can do only with a loop but I'm not hitting, the values would have this in order of date
Eric Araujo

Poston Mon, 07 Oct 2013 11:27 am

Boy, did a sql statement that generates the dates from the desired date to the last day of the year of the desired date.
This block is assembled dynamically and can select only the end of days of week (1-7 and Sunday-Saturday).
I believe that will help:
Code: Select all
SELECT *
  FROM (SELECT dia
             , TO_CHAR(dia, 'd') dia_semana
          FROM (SELECT data_inicial + LEVEL - 1 dia
                  FROM (SELECT TO_DATE(&MYDATE, 'DD/MM/RRRR') data_inicial
                          FROM dual)
                CONNECT BY LEVEL <=  (
                                      TO_DATE('31/12/' || (extract(YEAR FROM TO_DATE(&MYDATE, 'DD/MM/RRRR'))) , 'DD/MM/RRRR') -
                                      TO_DATE(&MYDATE, 'DD/MM/RRRR')
                                      ) + 1))
WHERE 1 = 1
  AND dia_semana IN (1,7)
;
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.

Poston Thu, 10 Oct 2013 4:27 pm

Thanks for your help, get develop this loop that displays what I need.
Code: Select all
DECLARE
v_DATA DATE := '&Digite_Data' ;
v_DATA_FIM DATE;
BEGIN
v_DATA_FIM := ADD_MONTHS(V_DATA, 12);
  FOR I IN 1..365 LOOP
    IF (TO_CHAR(v_DATA, 'D') IN (1,7) ) THEN
    DBMS_OUTPUT.PUT_LINE(v_DATA ||' - ' || TO_CHAR(V_DATA,'DAY'));
    END IF;
  v_DATA := v_DATA + 1;
  END LOOP;
END;
need to evolve this program to view the holidays and their description. I created a holiday table with date and description but I can't bring this information, any idea of bringing This information.

Code: Select all
create table feriado
( dt date,
descricao varchar2(50)
);
insert into feriado values ( to_date('01/01/2011','dd/mm/yyyy'), 'Confraternização Universal');
insert into feriado values ( to_date('25/01/2011','dd/mm/yyyy'), 'Aniversário de São Paulo'  );
insert into feriado values ( to_date('08/03/2011','dd/mm/yyyy'), 'Carnaval');
insert into feriado values ( to_date('21/04/2011','dd/mm/yyyy'), 'Tiradentes');
insert into feriado values ( to_date('22/04/2011','dd/mm/yyyy'), 'Sexta-feira da Paixão');
insert into feriado values ( to_date('01/05/2011','dd/mm/yyyy'), 'Dia do Trabalho');
insert into feriado values ( to_date('23/06/2011','dd/mm/yyyy'), 'Corpus Christi');
insert into feriado values ( to_date('07/09/2011','dd/mm/yyyy'), 'Independência do Brasil');
insert into feriado values ( to_date('12/10/2011','dd/mm/yyyy'), 'Nossa Senhora Aparecida');
insert into feriado values ( to_date('02/11/2011','dd/mm/yyyy'), 'Finados');
insert into feriado values ( to_date('25/12/2011','dd/mm/yyyy'), 'Natal');
Eric Araujo

Poston Thu, 10 Oct 2013 5:51 pm

Oops, I had understood that count from certain date until the end of the year.
Your procedure, including some problems, there is a problem of man: leap years.

You are " programming " in plsql without using sql. To do what you want you will have to consult your holiday table and verify that date.
You could use the structure that I put at the beginning making a outter join with your holiday table. If it exists then você displays.
But também you can within your loop check table if there are holidays that date. If there is, then displays.
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.

Poston Fri, 11 Oct 2013 12:27 pm

Tinho
Location: São Paulo - SP


  • See also
    Replies
    Views
    Last Post