Friday, May 23, 2008

Oracle Date Dimension

Friday, May 23, 2008 Posted by Andre Broers , 3 comments
In my opinion it is always handy to have a detailed date dimension in a database where you can always join against. This is handy to get the current month or all mondays that aren't holidays.

select datdate from dat where datyyyymm = (select datyyyymm from dat where datdate=trunc(sysdate));

select datdate from dat where datdayeng = 'Monday' and datholiday='Y' ;

in the underlying code the dat dimension is created.
create table dat
(
datkey number not null,
datdate date not null,
dattype varchar2(3) not null,
datyyyy number(4) not null,
datyyyymm number(6) not null,
datyyyymmdd number(8) not null,
datyyyyww number(8) not null,
datmm number(2) not null,
datww number(2) not null,
datdd number(2) not null,
datmmcum number not null,
datwwcum number not null,
datddcum number not null,
datmontheng varchar2(25) not null,
datmonthned varchar2(25) not null,
datdayofweek number(1) not null,
datdayeng varchar2(25) not null,
datdayned varchar2(25) not null,
datweekend varchar2(1) not null,
datholiday varchar2(1) not null,
constraint dat_pkey primary key (datkey)
)
/

drop sequence datkey_seq;

create sequence datkey_seq
start with 1
increment by 1
nomaxvalue
/

create or replace trigger datkey_trigger
before insert on dat
for each row
begin
select datkey_seq.nextval into :new.datkey from dual;
end;
/

create unique index datdate_idx1 on dat (datdate)
/

declare g_startdate date := '01-JAN-1950';
g_enddate date := '31-DEC-2100';
d date := g_startdate;
g_weekcum number := 0;
g_week number := 0;
begin
while d < g_enddate
loop
if (g_week <> to_char(d,'YYYYIW')) then
g_week := to_char(d,'YYYYIW');
g_weekcum := g_weekcum + 1;
end if;
insert into dat (
datdate
,dattype
,datyyyy
,datyyyymm
,datyyyymmdd
,datyyyyww
,datmm
,datww
,datdd
,datmmcum
,datwwcum
,datddcum
,datmontheng
,datmonthned
,datdayofweek
,datdayeng
,datdayned
,datweekend
,datholiday
) values (
d
,'DAT'
,to_char(d,'YYYY')
,to_char(d,'YYYYMM')
,to_char(d,'YYYYMMDD')
,to_char(d,'YYYYIW')
,to_char(d,'MM')
,to_char(d,'IW')
,to_char(d,'DD')
,trunc(months_between(d,g_startdate))
,g_weekcum
,d - to_date(g_startdate) + 1
,to_char(d,'FMMonth','NLS_DATE_LANGUAGE=American')
,to_char(d,'FMMonth','NLS_DATE_LANGUAGE=Dutch')
,to_char(d,'D') -- Monday first weekday
,to_char(d,'FMDay','NLS_DATE_LANGUAGE=American')
,to_char(d,'FMDay','NLS_DATE_LANGUAGE=Dutch')
,case when to_char(d,'FMDAY','NLS_DATE_LANGUAGE=American') = 'SATURDAY' then 'Y'
when to_char(d,'FMDAY','NLS_DATE_LANGUAGE=American') = 'SUNDAY' then 'Y'
else 'N'
end
,'N'
);
d := d + 1;
end loop;
end;
/

-- First day of week is monday
alter session set nls_territory = "the netherlands"
/
update dat set datdayofweek=to_char(datdate,'D')
/
-- Kerst
update dat set datholiday='Y' where datmm=12 and (datdd=25 or datdd=26)
/
-- Koninginnedag
update dat set datholiday='Y' where datmm=4 and ((datdd=30 and datdayofweek!=7) or (datdd=29 and datdayofweek=6))
/
-- 5 mei
update dat set datholiday='Y' where datmm=5 and mod(datyyyy,5)=0
/
update dat set datholiday='Y' where datdate=to_date('19500409','YYYYMMDD') or datdate-1=to_date('19500409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500409','YYYYMMDD') or datdate-1=to_date('19500409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510325','YYYYMMDD') or datdate-1=to_date('19510325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520413','YYYYMMDD') or datdate-1=to_date('19520413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530405','YYYYMMDD') or datdate-1=to_date('19530405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540418','YYYYMMDD') or datdate-1=to_date('19540418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550410','YYYYMMDD') or datdate-1=to_date('19550410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560401','YYYYMMDD') or datdate-1=to_date('19560401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570421','YYYYMMDD') or datdate-1=to_date('19570421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580406','YYYYMMDD') or datdate-1=to_date('19580406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590329','YYYYMMDD') or datdate-1=to_date('19590329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600417','YYYYMMDD') or datdate-1=to_date('19600417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610402','YYYYMMDD') or datdate-1=to_date('19610402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620422','YYYYMMDD') or datdate-1=to_date('19620422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630414','YYYYMMDD') or datdate-1=to_date('19630414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640329','YYYYMMDD') or datdate-1=to_date('19640329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650418','YYYYMMDD') or datdate-1=to_date('19650418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660410','YYYYMMDD') or datdate-1=to_date('19660410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670326','YYYYMMDD') or datdate-1=to_date('19670326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680414','YYYYMMDD') or datdate-1=to_date('19680414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690406','YYYYMMDD') or datdate-1=to_date('19690406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700329','YYYYMMDD') or datdate-1=to_date('19700329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710411','YYYYMMDD') or datdate-1=to_date('19710411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720402','YYYYMMDD') or datdate-1=to_date('19720402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730422','YYYYMMDD') or datdate-1=to_date('19730422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740414','YYYYMMDD') or datdate-1=to_date('19740414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750330','YYYYMMDD') or datdate-1=to_date('19750330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760418','YYYYMMDD') or datdate-1=to_date('19760418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770410','YYYYMMDD') or datdate-1=to_date('19770410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780326','YYYYMMDD') or datdate-1=to_date('19780326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790415','YYYYMMDD') or datdate-1=to_date('19790415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800406','YYYYMMDD') or datdate-1=to_date('19800406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810419','YYYYMMDD') or datdate-1=to_date('19810419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820411','YYYYMMDD') or datdate-1=to_date('19820411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830403','YYYYMMDD') or datdate-1=to_date('19830403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840422','YYYYMMDD') or datdate-1=to_date('19840422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850407','YYYYMMDD') or datdate-1=to_date('19850407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860330','YYYYMMDD') or datdate-1=to_date('19860330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870419','YYYYMMDD') or datdate-1=to_date('19870419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880403','YYYYMMDD') or datdate-1=to_date('19880403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890326','YYYYMMDD') or datdate-1=to_date('19890326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900415','YYYYMMDD') or datdate-1=to_date('19900415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910331','YYYYMMDD') or datdate-1=to_date('19910331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920419','YYYYMMDD') or datdate-1=to_date('19920419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930411','YYYYMMDD') or datdate-1=to_date('19930411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940403','YYYYMMDD') or datdate-1=to_date('19940403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950416','YYYYMMDD') or datdate-1=to_date('19950416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960407','YYYYMMDD') or datdate-1=to_date('19960407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970330','YYYYMMDD') or datdate-1=to_date('19970330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980412','YYYYMMDD') or datdate-1=to_date('19980412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990404','YYYYMMDD') or datdate-1=to_date('19990404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000423','YYYYMMDD') or datdate-1=to_date('20000423','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010415','YYYYMMDD') or datdate-1=to_date('20010415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020331','YYYYMMDD') or datdate-1=to_date('20020331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030420','YYYYMMDD') or datdate-1=to_date('20030420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040411','YYYYMMDD') or datdate-1=to_date('20040411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050327','YYYYMMDD') or datdate-1=to_date('20050327','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060416','YYYYMMDD') or datdate-1=to_date('20060416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070408','YYYYMMDD') or datdate-1=to_date('20070408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080323','YYYYMMDD') or datdate-1=to_date('20080323','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090412','YYYYMMDD') or datdate-1=to_date('20090412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100404','YYYYMMDD') or datdate-1=to_date('20100404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110424','YYYYMMDD') or datdate-1=to_date('20110424','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120408','YYYYMMDD') or datdate-1=to_date('20120408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130331','YYYYMMDD') or datdate-1=to_date('20130331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140420','YYYYMMDD') or datdate-1=to_date('20140420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150405','YYYYMMDD') or datdate-1=to_date('20150405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160327','YYYYMMDD') or datdate-1=to_date('20160327','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170416','YYYYMMDD') or datdate-1=to_date('20170416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180401','YYYYMMDD') or datdate-1=to_date('20180401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190421','YYYYMMDD') or datdate-1=to_date('20190421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200412','YYYYMMDD') or datdate-1=to_date('20200412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210404','YYYYMMDD') or datdate-1=to_date('20210404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220417','YYYYMMDD') or datdate-1=to_date('20220417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230409','YYYYMMDD') or datdate-1=to_date('20230409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240331','YYYYMMDD') or datdate-1=to_date('20240331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250420','YYYYMMDD') or datdate-1=to_date('20250420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260405','YYYYMMDD') or datdate-1=to_date('20260405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270328','YYYYMMDD') or datdate-1=to_date('20270328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280416','YYYYMMDD') or datdate-1=to_date('20280416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290401','YYYYMMDD') or datdate-1=to_date('20290401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300421','YYYYMMDD') or datdate-1=to_date('20300421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310413','YYYYMMDD') or datdate-1=to_date('20310413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320328','YYYYMMDD') or datdate-1=to_date('20320328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330417','YYYYMMDD') or datdate-1=to_date('20330417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340409','YYYYMMDD') or datdate-1=to_date('20340409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350325','YYYYMMDD') or datdate-1=to_date('20350325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360413','YYYYMMDD') or datdate-1=to_date('20360413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370405','YYYYMMDD') or datdate-1=to_date('20370405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380425','YYYYMMDD') or datdate-1=to_date('20380425','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390410','YYYYMMDD') or datdate-1=to_date('20390410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400401','YYYYMMDD') or datdate-1=to_date('20400401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410421','YYYYMMDD') or datdate-1=to_date('20410421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420406','YYYYMMDD') or datdate-1=to_date('20420406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430329','YYYYMMDD') or datdate-1=to_date('20430329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440417','YYYYMMDD') or datdate-1=to_date('20440417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450409','YYYYMMDD') or datdate-1=to_date('20450409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460325','YYYYMMDD') or datdate-1=to_date('20460325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470414','YYYYMMDD') or datdate-1=to_date('20470414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480405','YYYYMMDD') or datdate-1=to_date('20480405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490418','YYYYMMDD') or datdate-1=to_date('20490418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500410','YYYYMMDD') or datdate-1=to_date('20500410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510402','YYYYMMDD') or datdate-1=to_date('20510402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520421','YYYYMMDD') or datdate-1=to_date('20520421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530406','YYYYMMDD') or datdate-1=to_date('20530406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540329','YYYYMMDD') or datdate-1=to_date('20540329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550418','YYYYMMDD') or datdate-1=to_date('20550418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560402','YYYYMMDD') or datdate-1=to_date('20560402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570422','YYYYMMDD') or datdate-1=to_date('20570422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580414','YYYYMMDD') or datdate-1=to_date('20580414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590330','YYYYMMDD') or datdate-1=to_date('20590330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600418','YYYYMMDD') or datdate-1=to_date('20600418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610410','YYYYMMDD') or datdate-1=to_date('20610410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620326','YYYYMMDD') or datdate-1=to_date('20620326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630415','YYYYMMDD') or datdate-1=to_date('20630415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640406','YYYYMMDD') or datdate-1=to_date('20640406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650329','YYYYMMDD') or datdate-1=to_date('20650329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660411','YYYYMMDD') or datdate-1=to_date('20660411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670403','YYYYMMDD') or datdate-1=to_date('20670403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680422','YYYYMMDD') or datdate-1=to_date('20680422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690414','YYYYMMDD') or datdate-1=to_date('20690414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700330','YYYYMMDD') or datdate-1=to_date('20700330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710419','YYYYMMDD') or datdate-1=to_date('20710419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720410','YYYYMMDD') or datdate-1=to_date('20720410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730326','YYYYMMDD') or datdate-1=to_date('20730326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740415','YYYYMMDD') or datdate-1=to_date('20740415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750407','YYYYMMDD') or datdate-1=to_date('20750407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760419','YYYYMMDD') or datdate-1=to_date('20760419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770411','YYYYMMDD') or datdate-1=to_date('20770411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780403','YYYYMMDD') or datdate-1=to_date('20780403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790423','YYYYMMDD') or datdate-1=to_date('20790423','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800407','YYYYMMDD') or datdate-1=to_date('20800407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810330','YYYYMMDD') or datdate-1=to_date('20810330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820419','YYYYMMDD') or datdate-1=to_date('20820419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830404','YYYYMMDD') or datdate-1=to_date('20830404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840326','YYYYMMDD') or datdate-1=to_date('20840326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850415','YYYYMMDD') or datdate-1=to_date('20850415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860331','YYYYMMDD') or datdate-1=to_date('20860331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870420','YYYYMMDD') or datdate-1=to_date('20870420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880411','YYYYMMDD') or datdate-1=to_date('20880411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890403','YYYYMMDD') or datdate-1=to_date('20890403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900416','YYYYMMDD') or datdate-1=to_date('20900416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910408','YYYYMMDD') or datdate-1=to_date('20910408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920330','YYYYMMDD') or datdate-1=to_date('20920330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930412','YYYYMMDD') or datdate-1=to_date('20930412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940404','YYYYMMDD') or datdate-1=to_date('20940404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950424','YYYYMMDD') or datdate-1=to_date('20950424','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960415','YYYYMMDD') or datdate-1=to_date('20960415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970331','YYYYMMDD') or datdate-1=to_date('20970331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980420','YYYYMMDD') or datdate-1=to_date('20980420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990412','YYYYMMDD') or datdate-1=to_date('20990412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000328','YYYYMMDD') or datdate-1=to_date('21000328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050505','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080501','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160505','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500528','YYYYMMDD') or datdate-1=to_date('19500528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510513','YYYYMMDD') or datdate-1=to_date('19510513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520601','YYYYMMDD') or datdate-1=to_date('19520601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530524','YYYYMMDD') or datdate-1=to_date('19530524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540606','YYYYMMDD') or datdate-1=to_date('19540606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550529','YYYYMMDD') or datdate-1=to_date('19550529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560520','YYYYMMDD') or datdate-1=to_date('19560520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570609','YYYYMMDD') or datdate-1=to_date('19570609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580525','YYYYMMDD') or datdate-1=to_date('19580525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590517','YYYYMMDD') or datdate-1=to_date('19590517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600605','YYYYMMDD') or datdate-1=to_date('19600605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610521','YYYYMMDD') or datdate-1=to_date('19610521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620610','YYYYMMDD') or datdate-1=to_date('19620610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630602','YYYYMMDD') or datdate-1=to_date('19630602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640517','YYYYMMDD') or datdate-1=to_date('19640517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650606','YYYYMMDD') or datdate-1=to_date('19650606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660529','YYYYMMDD') or datdate-1=to_date('19660529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670514','YYYYMMDD') or datdate-1=to_date('19670514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680602','YYYYMMDD') or datdate-1=to_date('19680602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690525','YYYYMMDD') or datdate-1=to_date('19690525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700517','YYYYMMDD') or datdate-1=to_date('19700517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710530','YYYYMMDD') or datdate-1=to_date('19710530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720521','YYYYMMDD') or datdate-1=to_date('19720521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730610','YYYYMMDD') or datdate-1=to_date('19730610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740602','YYYYMMDD') or datdate-1=to_date('19740602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750518','YYYYMMDD') or datdate-1=to_date('19750518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760606','YYYYMMDD') or datdate-1=to_date('19760606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770529','YYYYMMDD') or datdate-1=to_date('19770529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780514','YYYYMMDD') or datdate-1=to_date('19780514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790603','YYYYMMDD') or datdate-1=to_date('19790603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800525','YYYYMMDD') or datdate-1=to_date('19800525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810607','YYYYMMDD') or datdate-1=to_date('19810607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820530','YYYYMMDD') or datdate-1=to_date('19820530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830522','YYYYMMDD') or datdate-1=to_date('19830522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840610','YYYYMMDD') or datdate-1=to_date('19840610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850526','YYYYMMDD') or datdate-1=to_date('19850526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860518','YYYYMMDD') or datdate-1=to_date('19860518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870607','YYYYMMDD') or datdate-1=to_date('19870607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880522','YYYYMMDD') or datdate-1=to_date('19880522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890514','YYYYMMDD') or datdate-1=to_date('19890514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900603','YYYYMMDD') or datdate-1=to_date('19900603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910519','YYYYMMDD') or datdate-1=to_date('19910519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920607','YYYYMMDD') or datdate-1=to_date('19920607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930530','YYYYMMDD') or datdate-1=to_date('19930530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940522','YYYYMMDD') or datdate-1=to_date('19940522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950604','YYYYMMDD') or datdate-1=to_date('19950604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960526','YYYYMMDD') or datdate-1=to_date('19960526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970518','YYYYMMDD') or datdate-1=to_date('19970518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980531','YYYYMMDD') or datdate-1=to_date('19980531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990523','YYYYMMDD') or datdate-1=to_date('19990523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000611','YYYYMMDD') or datdate-1=to_date('20000611','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010603','YYYYMMDD') or datdate-1=to_date('20010603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020519','YYYYMMDD') or datdate-1=to_date('20020519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030608','YYYYMMDD') or datdate-1=to_date('20030608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040530','YYYYMMDD') or datdate-1=to_date('20040530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050515','YYYYMMDD') or datdate-1=to_date('20050515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060604','YYYYMMDD') or datdate-1=to_date('20060604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070527','YYYYMMDD') or datdate-1=to_date('20070527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080511','YYYYMMDD') or datdate-1=to_date('20080511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090531','YYYYMMDD') or datdate-1=to_date('20090531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100523','YYYYMMDD') or datdate-1=to_date('20100523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110612','YYYYMMDD') or datdate-1=to_date('20110612','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120527','YYYYMMDD') or datdate-1=to_date('20120527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130519','YYYYMMDD') or datdate-1=to_date('20130519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140608','YYYYMMDD') or datdate-1=to_date('20140608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150524','YYYYMMDD') or datdate-1=to_date('20150524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160515','YYYYMMDD') or datdate-1=to_date('20160515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170604','YYYYMMDD') or datdate-1=to_date('20170604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180520','YYYYMMDD') or datdate-1=to_date('20180520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190609','YYYYMMDD') or datdate-1=to_date('20190609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200531','YYYYMMDD') or datdate-1=to_date('20200531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210523','YYYYMMDD') or datdate-1=to_date('20210523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220605','YYYYMMDD') or datdate-1=to_date('20220605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230528','YYYYMMDD') or datdate-1=to_date('20230528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240519','YYYYMMDD') or datdate-1=to_date('20240519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250608','YYYYMMDD') or datdate-1=to_date('20250608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260524','YYYYMMDD') or datdate-1=to_date('20260524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270516','YYYYMMDD') or datdate-1=to_date('20270516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280604','YYYYMMDD') or datdate-1=to_date('20280604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290520','YYYYMMDD') or datdate-1=to_date('20290520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300609','YYYYMMDD') or datdate-1=to_date('20300609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310601','YYYYMMDD') or datdate-1=to_date('20310601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320516','YYYYMMDD') or datdate-1=to_date('20320516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330605','YYYYMMDD') or datdate-1=to_date('20330605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340528','YYYYMMDD') or datdate-1=to_date('20340528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350513','YYYYMMDD') or datdate-1=to_date('20350513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360601','YYYYMMDD') or datdate-1=to_date('20360601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370524','YYYYMMDD') or datdate-1=to_date('20370524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380613','YYYYMMDD') or datdate-1=to_date('20380613','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390529','YYYYMMDD') or datdate-1=to_date('20390529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400520','YYYYMMDD') or datdate-1=to_date('20400520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410609','YYYYMMDD') or datdate-1=to_date('20410609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420525','YYYYMMDD') or datdate-1=to_date('20420525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430517','YYYYMMDD') or datdate-1=to_date('20430517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440605','YYYYMMDD') or datdate-1=to_date('20440605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450528','YYYYMMDD') or datdate-1=to_date('20450528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460513','YYYYMMDD') or datdate-1=to_date('20460513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470602','YYYYMMDD') or datdate-1=to_date('20470602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480524','YYYYMMDD') or datdate-1=to_date('20480524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490606','YYYYMMDD') or datdate-1=to_date('20490606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500529','YYYYMMDD') or datdate-1=to_date('20500529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510521','YYYYMMDD') or datdate-1=to_date('20510521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520609','YYYYMMDD') or datdate-1=to_date('20520609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530525','YYYYMMDD') or datdate-1=to_date('20530525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540517','YYYYMMDD') or datdate-1=to_date('20540517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550606','YYYYMMDD') or datdate-1=to_date('20550606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560521','YYYYMMDD') or datdate-1=to_date('20560521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570610','YYYYMMDD') or datdate-1=to_date('20570610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580602','YYYYMMDD') or datdate-1=to_date('20580602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590518','YYYYMMDD') or datdate-1=to_date('20590518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600606','YYYYMMDD') or datdate-1=to_date('20600606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610529','YYYYMMDD') or datdate-1=to_date('20610529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620514','YYYYMMDD') or datdate-1=to_date('20620514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630603','YYYYMMDD') or datdate-1=to_date('20630603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640525','YYYYMMDD') or datdate-1=to_date('20640525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650517','YYYYMMDD') or datdate-1=to_date('20650517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660530','YYYYMMDD') or datdate-1=to_date('20660530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670522','YYYYMMDD') or datdate-1=to_date('20670522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680610','YYYYMMDD') or datdate-1=to_date('20680610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690602','YYYYMMDD') or datdate-1=to_date('20690602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700518','YYYYMMDD') or datdate-1=to_date('20700518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710607','YYYYMMDD') or datdate-1=to_date('20710607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720529','YYYYMMDD') or datdate-1=to_date('20720529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730514','YYYYMMDD') or datdate-1=to_date('20730514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740603','YYYYMMDD') or datdate-1=to_date('20740603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750526','YYYYMMDD') or datdate-1=to_date('20750526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760607','YYYYMMDD') or datdate-1=to_date('20760607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770530','YYYYMMDD') or datdate-1=to_date('20770530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780522','YYYYMMDD') or datdate-1=to_date('20780522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790611','YYYYMMDD') or datdate-1=to_date('20790611','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800526','YYYYMMDD') or datdate-1=to_date('20800526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810518','YYYYMMDD') or datdate-1=to_date('20810518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820607','YYYYMMDD') or datdate-1=to_date('20820607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830523','YYYYMMDD') or datdate-1=to_date('20830523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840514','YYYYMMDD') or datdate-1=to_date('20840514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850603','YYYYMMDD') or datdate-1=to_date('20850603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860519','YYYYMMDD') or datdate-1=to_date('20860519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870608','YYYYMMDD') or datdate-1=to_date('20870608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880530','YYYYMMDD') or datdate-1=to_date('20880530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890522','YYYYMMDD') or datdate-1=to_date('20890522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900604','YYYYMMDD') or datdate-1=to_date('20900604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910527','YYYYMMDD') or datdate-1=to_date('20910527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920518','YYYYMMDD') or datdate-1=to_date('20920518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930531','YYYYMMDD') or datdate-1=to_date('20930531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940523','YYYYMMDD') or datdate-1=to_date('20940523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950612','YYYYMMDD') or datdate-1=to_date('20950612','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960603','YYYYMMDD') or datdate-1=to_date('20960603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970519','YYYYMMDD') or datdate-1=to_date('20970519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980608','YYYYMMDD') or datdate-1=to_date('20980608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990531','YYYYMMDD') or datdate-1=to_date('20990531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000516','YYYYMMDD') or datdate-1=to_date('21000516','YYYYMMDD');

commit
/

I think it is handy to create this dimension in it's own schema:

create user dat identified by dat;
grant connect,resource to dat;
grant create public synonym to dat;
connect dat/dat
run the above script
grant select on dat to public;
create public synonym dat for dat;

Now everyone can use the dat dimension.

3 comments:

  1. Today's apple is spinning at the replica watches uk acceleration of ablaze and it seems infiltrated by deadlines on top of deadlines. In this anarchic ambiance the watch is absolutely in demand! For some their time section is even calmness at moments. There are arrangement to accept from in abounding styles and personalities. Aloft that there are a amount of brands with ample capricious amount ranges. Abounding do accept a few watches for altered occasions, generally analytic priced. Admitting there are aswell those enthusiasts who avidly aggregate abounding watches and generally they are absolutely cher as well. It all began at a Paris jewellery branch run by Adept Adolphe Picard. In 1847 if Picard anesthetized away, Louis-Francios Cartier took over afterwards his master. It became a ancestors business and affiliated through to the next century. Louis-Francios's son Alfred Cartier was circuitous in 1904 if he abutting minds with allegorical watchmaker Edmond Jaeger to accomplish the aboriginal Cartier watch to be exhausted by pilot Santos-Dumont if flying. Today the replica rolex name Cartier has maintained authority for about 160 years and alone associated with quality, elegance, and performance! If it comes to the Cartier watch, alone the best is used. The accomplished cast jewellery and alone the a lot of absolute automated functionality are activated with the accomplished standards as annihilation beneath will do.Among the alternative you will aswell see the college end Cartier watch alternative including abounding adored metal bands of gold and platinum. Taking affliction in authentic alternative of these will amount you a bundle, but should go down in your ancestors history well. The ultimate would accept to be the Cartier D'art bound copy models. In this band you will acquisition the Cartier watch of all Cartier alignment from fractional to complete architecture accoutrement and the Cartier signature: the panther.If you accept any catechism for purchasing replica watches,the administrative ambience absurd in Jonson's play that Pope could not envisage, although his announcement does accept Curll acquaint Pemberton about an approaching case for publishing Rochester's poems. Admitting his loyal credentials, Curll's bartering absorption in the accursed Jacobite had put him in the position of an base and accusable criminal, affected to abjure in one of the uk replica watches accomplished accessible courts alone a few weeks afterwards Pope had fabricated him abjure in his bedroom.

    ReplyDelete
  2. Hi guys, hier is een nieuwe video review van een oud replica horloges Tag Gekocht bij watchinc.net qui Ondertussen veranderde in perfectwatches.to. Deze replica Tag Heuer Grote Carrera Calibre 17 is een zeer goed gekloond stukken en de gebruikte om het te laten zijn van goede kwaliteit materialen.

    Het
    replica horloges tag heuer is solide, is: heeft een zeer mooi krasvast kristal dat gloeit precies de juiste hoeveelheid waardoor het een echte look. Roestvrij stalen behuizing is van zeer goede kwaliteit en het is de huisvesting van een zeer nauwkeurige Japanse automatisch mechanisme qui Ik ben replica horloges breitling helemaal weg want ik heb een heleboel nep horloges die Carry le même beweging en had nooit enige vorm van problemen with'any van 'em.

    Zwart lederen band is van goede kwaliteit als goed, logo op de vouwsluiting is precies goed, evenals de gravures op de doorzichtige zaak terug. Dit is een van de beste, zo niet de beste van alle replica horloges rolex die ik heb gezien die er is dit model. Het heeft alle ingrediënten te passen voor originele jaar ten minste 80% van de tijd.

    ReplyDelete