Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table CALENDAR(
    DEPT_ID VARCHAR2(8) NOT NULL,
    QUARTER VARCHAR2(8) NOT NULL,
    CALENDAR_DATE DATE NOT NULL,
    IS_OPEN VARCHAR2(1) NOT NULL
);
insert into calendar
with t as (
  select 'ACCTING | 2019Q1  | 2019-03-02 | '' ''
MFGING  | 2019Q1  | 2019-03-02 | x
MRKTING | 2019Q1  | 2019-03-02 | '' ''
ACCTING | 2019Q1  | 2019-03-03 | x
MFGING  | 2019Q1  | 2019-03-03 | x
MRKTING | 2019Q1  | 2019-03-03 | x
ACCTING | 2019Q1  | 2019-03-04 | x
MFGING  | 2019Q1  | 2019-03-04 | x
MRKTING | 2019Q1  | 2019-03-04 | x' str from dual
)
select regexp_substr(t.str, '^(\S+).*?([0-9A-Z]+).*?([0-9-]+)\s\|\s(.*)$', 1, LEVEL, 'm', 1) DEPT_ID,
       regexp_substr(t.str, '^(\S+).*?([0-9A-Z]+).*?([0-9-]+)\s\|\s(.*)$', 1, LEVEL, 'm', 2) QUARTER,
       to_DATE(regexp_substr(t.str, '^(\S+).*?([0-9A-Z]+).*?([0-9-]+)\s\|\s(.*)$', 1, LEVEL, 'm', 3), 'yyyy-mm-dd') CALDR_DATE,
       CASE WHEN regexp_substr(t.str, '^(\S+).*?([0-9A-Z]+).*?([0-9-]+)\s\|\s(.*)$', 1, LEVEL, 'm', 4) = 'x' THEN 'x' ELSE ' ' END IS_OPEN  
from t
CONNECT BY regexp_substr( t.str,'^(\S+).*?([0-9A-Z]+).*?([0-9-]+)\s\|\s(.*)$', 1, LEVEL, 'm') IS NOT NULL
9 rows affected

create table TRANSACTIONS(
    DEPT_ID VARCHAR2(8) NOT NULL,
    QUARTER VARCHAR2(8) NOT NULL,
    TRANSACTION_DATE DATE NOT NULL,
    TRANSACTION_TYPE NUMBER(1,0) NOT NULL,
    EMPLOYEE_ID VARCHAR2(13) NOT NULL
)
insert into TRANSACTIONS
with t as (
  select 'MFGING  | 2019Q1  | 2019-01-07 |          4 | 123
MRKTING | 2019Q1  | 2019-01-28 |          4 | 456
MFGING  | 2019Q1  | 2019-02-01 |          5 | 123' str from dual
)
select regexp_substr(t.str, '^(\S+).*?([0-9A-Z]+).*?([0-9-]+).*?(\d).*?(\d+)$', 1, LEVEL, 'm', 1) DEPT_ID,
       regexp_substr(t.str, '^(\S+).*?([0-9A-Z]+).*?([0-9-]+).*?(\d).*?(\d+)$', 1, LEVEL, 'm', 2) QUARTER,
       to_DATE(regexp_substr(t.str, '^(\S+).*?([0-9A-Z]+).*?([0-9-]+).*?(\d).*?(\d+)$', 1, LEVEL, 'm', 3), 'yyyy-mm-dd') TRANS_DATE ,
       to_NUMBER(regexp_substr(t.str, '^(\S+).*?([0-9A-Z]+).*?([0-9-]+).*?(\d).*?(\d+)$', 1, LEVEL, 'm', 4)) TRANS_TYPE,
       regexp_substr(t.str, '^(\S+).*?([0-9A-Z]+).*?([0-9-]+).*?(\d).*?(\d+)$', 1, LEVEL, 'm', 5) EMPLOYEE_ID   
from t
CONNECT BY regexp_substr( t.str,'^(\S+).*?([0-9A-Z]+).*?([0-9-]+).*?(\d).*?(\d+)$', 1, LEVEL, 'm') IS NOT NULL;
3 rows affected

select c.DEPT_ID, c.QUARTER, 
       c.CALENDAR_DATE, 
       e.EMPLOYEE_ID, 
       case when c.CALENDAR_DATE between 
                    e.TRANSACTION_DATE and NVL(t.TRANSACTION_DATE, c.CALENDAR_DATE)  
        then 1 else 0 end ATTENDANCE_EXPECTED, 
       CASE when c.IS_OPEN = 'x' then 1 else 0 end is_open
from calendar c 
LEFT JOIN transactions e 
       ON c.dept_id = e.dept_id
      and c.quarter = e.quarter
      and e.transaction_type = 4
left JOIN transactions t 
       ON t.transaction_type = 5
      and t.EMPLOYEE_ID = e.EMPLOYEE_ID;


DEPT_ID
QUARTER
CALENDAR_DATE
EMPLOYEE_ID
ATTENDANCE_EXPECTED
IS_OPEN


MFGING
2019Q1
02-MAR-19
123
0
1

MFGING
2019Q1
03-MAR-19
123
0
1

MFGING
2019Q1
04-MAR-19
123
0
1

ACCTING
2019Q1
02-MAR-19
null
0
0

ACCTING
2019Q1
03-MAR-19
null
0
1

ACCTING
2019Q1
04-MAR-19
null
0
1

MRKTING
2019Q1
02-MAR-19
456
1
0

MRKTING
2019Q1
03-MAR-19
456
1
1

MRKTING
2019Q1
04-MAR-19
456
1
1

DEPT_ID	QUARTER	CALENDAR_DATE	EMPLOYEE_ID	ATTENDANCE_EXPECTED	IS_OPEN
MFGING	2019Q1	02-MAR-19	123	0	1
MFGING	2019Q1	03-MAR-19	123	0	1
MFGING	2019Q1	04-MAR-19	123	0	1
ACCTING	2019Q1	02-MAR-19	null	0	0
ACCTING	2019Q1	03-MAR-19	null	0	1
ACCTING	2019Q1	04-MAR-19	null	0	1
MRKTING	2019Q1	02-MAR-19	456	1	0
MRKTING	2019Q1	03-MAR-19	456	1	1
MRKTING	2019Q1	04-MAR-19	456	1	1