I'm using Oracle 21C.
My query involves three tables that can be created with the following code:
Create Table vol_att
( prim_key integer NOT NULL,
event_fkey integer,
contact_fkey integer,
attendance_type varchar2(256),
CONSTRAINT vol_att_pk PRIMARY KEY (prim_key)
);
Insert All
Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
Values(1, 1, 801, 'I')
Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
Values(2, 1, 234, 'Z')
Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
Values(3, 2, 258, 'I')
Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
Values(4, 2, 234, 'Z')
Select 1 from DUAL;
Create Table vol_con
( prim_key integer NOT NULL,
last_name varchar2(256),
first_name varchar2(256),
CONSTRAINT vol_con_pk PRIMARY KEY (prim_key)
);
Insert All
Into vol_con(prim_key, last_name, first_name)
Values(234, 'Potter', 'Harry')
Into vol_con(prim_key, last_name, first_name)
Values(258, 'Weasley', 'Ron')
Into vol_con(prim_key, last_name, first_name)
Values(801, 'Granger', 'Hermione')
Into vol_con(prim_key, last_name, first_name)
Values(500, 'Malfoy', 'Draco')
Select 1 from DUAL;
CREATE TABLE vol_evn
( prim_key integer NOT NULL,
event_date date,
CONSTRAINT vol_evn_pk PRIMARY KEY (prim_key)
);
Insert All
Into vol_evn(prim_key, event_date)
Values(1, to_date('30-mar-2023', 'dd-mon-yyyy'))
Into vol_evn(prim_key, event_date)
Values(2, to_date('18-apr-2023', 'dd-mon-yyyy'))
Select 1 from DUAL;
The following code generates a report
select
*
from
(select
vc.last_name
, va.attendance_type
, ve.event_date
from
vol_att va
full outer join vol_evn ve
on va.event_fkey = ve.prim_key
full outer join vol_con vc
on va.contact_fkey = vc.prim_key
)
pivot (
max(attendance_type) for event_date in
(to_date('2023-03-30', 'yyyy-mm-dd') Mar_30_2023,
to_date('2023-04-18', 'yyyy-mm-dd') Apr_18_2023
)
)
order by
last_name;
The above code produces expected results that look like:
LAST_NAME | MAR_30_2023 | APR_18_2023 |
---|---|---|
Granger | I | (null) |
Malfoy | (null) | (null) |
Potter | Z | Z |
Weasley | (null) | I |
I'd like to make a new report that would count the attendance (Anything that isn't null). I'd like the new report to look like:
MAR_30_2023 | APR_18_2023 | |
---|---|---|
2 | 2 |
How can I alter my code so that it produces a report in two rows: The header of dates and the second row containing the counts? Thanks for looking at this.
You could take out the last_name
field, and require that attendance_type
is not null. Then pivot, using the count of distinct records from the vol_con
table (having last_name
) as the data. Because of the constraints, you can replace the full outer joins with an inner join and left outer join respectively:
select *
from
(select ve.event_date,
vc.prim_key
from vol_att va
inner join vol_evn ve
on va.event_fkey = ve.prim_key
left join vol_con vc
on va.contact_fkey = vc.prim_key
where va.attendance_type is not null
)
pivot (
count(distinct prim_key) for event_date in (
to_date('2023-03-30', 'yyyy-mm-dd') Mar_30_2023,
to_date('2023-04-18', 'yyyy-mm-dd') Apr_18_2023
)
);