I created a table to put calendar dates. Date, day, month, year, etc...
CREATE TABLE DimDatas
(ID NUMBER PRIMARY KEY, -- ID
FULL_DATA DATE, -- DATA
DIA NUMBER, ---DIA
MES NUMBER, --- MES
ANO NUMBER, --- ANO
NOME_DIA VARCHAR2(50), --- DIA EXTENSO (SEGUNDA, ...)
NOME_MES VARCHAR2(50), --- MES POR EXTENSO
FERIADO NUMBER(1), --- SE É FERIADO (1)
DIA_SEMANA NUMBER(1), -- SE É DIA DE SEMANA (1)
N_DIA NUMBER, -- Nº DO DIA DENTRO DA SEMANA
N_SEMANA NUMBER, -- Nº DA SEMANA DENTRO DO ANO
N_TRIMESTRE NUMBER, -- Nº DO TRIMESTRE
N_SEMESTRE NUMBER -- Nº DO SEMESTRE
);
I used this code to populate the table
with startDate = 01012017 and enddate = 31122017
insert into dimdatas
select * from (
with calendar as (
select TO_DATE(:startdate,'DDMMYYYY') + rownum - 1 as myDate
from dual
connect by rownum <= TO_DATE(:enddate,'DDMMYYYY') - TO_DATE(:startdate,'DDMMYYYY') +1
) select rownum, mydate,
extract (day from mydate) dia, extract (month from mydate) mes, extract (year from mydate) ano,
to_char (myDate, 'Day', 'NLS_DATE_LANGUAGE = portuguese') NOME_DIA ,
to_char(myDate,'Month', 'NLS_DATE_LANGUAGE = portuguese') NOME_MES , 0 FERIADO ,
CASE WHEN TO_CHAR (myDate, 'D') IN (2,3,4,5,6) THEN 1
ELSE 0
END DIA_SEMANA ,
TO_CHAR (myDate, 'D') N_DIA , TO_CHAR (myDate, 'WW') N_SEMANA ,
case when extract (month from mydate) in (1,2,3) then 1
when extract (month from mydate) in (4,5,6) then 2
when extract (month from mydate) in (7,8,9) then 3
when extract (month from mydate) in (10,11,12) then 4
else 0
end N_TRIMESTRE ,
case when extract (month from mydate) <= 6 then 1
else 2
end N_SEMESTRE
from calendar
);
It inserts more than 730K of records, starting with december of 0017(??)
If I run the "calendar" query by itself, it returns 365 records, from 1Jan 2017 to 31 Dec 2017.
Any ideas?
Thank you