I'm trying to build a calendar, which appears to be working okay. How can I include a holiday column with 'Y' for a holiday and a 'N' if not a holiday.
In my test CASE below I want Feb 8 holiday 'Y' and all other rows =''N'
CREATE TABLE holidays (holiday_date) AS
SELECT TRUNC(SYSDATE, 'YY') + INTERVAL '39' DAY FROM DUAL
with calendar as (
select rownum - 1 as daynum
from dual
connect by rownum <= to_date('28-feb-2023') - to_date('1-feb-2023') +1
)
select to_date('1-feb-2023') + daynum as monthdate
from calendar;
I'm not sure what is holidays table's purpose as it contains only one row; then, you have a CTE (not perfect, though - if you use to_date, then apply format model as well) for February 2023, and it doesn't use previously created holidays.
Anyway: a simple option is to create calendar as is (for example, for year 2023, up to today) and include cb_holiday column (cb as checkbox whose value is Y or N) which is N by default, and updated later for desired dates.
SQL> create table calendar (datum, cb_holiday) as
2 select trunc(sysdate, 'yy') + level - 1, 'N'
3 from dual
4 connect by level <= trunc(sysdate) - trunc(sysdate, 'yy') + 1;
Table created.
SQL> update calendar set
2 cb_holiday = 'Y'
3 where datum in (date '2023-02-08');
1 row updated.
SQL> select * From calendar
2 where to_char(datum, 'yyyymm') = '202302'
3 order by datum;
DATUM C
--------- -
01-FEB-23 N
02-FEB-23 N
03-FEB-23 N
04-FEB-23 N
05-FEB-23 N
06-FEB-23 N
07-FEB-23 N
08-FEB-23 Y --> here it is
09-FEB-23 N
10-FEB-23 N
11-FEB-23 N
<snip>
26-FEB-23 N
27-FEB-23 N
28-FEB-23 N
28 rows selected.
SQL>
If you don't want to update table in a separate statement, yes - you can do it in create table using e.g. case expression:
SQL> create table calendar (datum, cb_holiday) as
2 select trunc(sysdate, 'yy') + level - 1,
3 case when trunc(sysdate, 'yy') + level - 1 in (date '2023-02-08') then 'Y'
4 else 'N'
5 end
6 from dual
7 connect by level <= trunc(sysdate) - trunc(sysdate, 'yy') + 1;
Table created.