Firebird 3.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;


VERSION
RDB$CHARACTER_SET_NAME


3.0.10
UTF8


create table T_EMPLOYEE (ID_E integer primary key, NAME varchar(50) not null);
create table T_WORK (ID_W integer primary key, DATE_W date not null, ID_E integer not null references T_EMPLOYEE(ID_E));
insert into T_EMPLOYEE (ID_E, NAME) values (1, 'Anne');
1 rows affected

insert into T_EMPLOYEE (ID_E, NAME) values (2, 'Bob');
1 rows affected

insert into T_WORK (ID_W, DATE_W, ID_E) values (1, '2021-01-01', 1);
1 rows affected

insert into T_WORK (ID_W, DATE_W, ID_E) values (2, '2021-01-01', 2);
1 rows affected

insert into T_WORK (ID_W, DATE_W, ID_E) values (3, '2021-01-02', 1);
1 rows affected

insert into T_WORK (ID_W, DATE_W, ID_E) values (4, '2021-01-03', 2);
1 rows affected

select e.ID_E as OID_E, e.NAME as ONAME, 
  count(case when w.DATE_W = date '2021-01-01' then 1 end) as ODATE1,
  count(case when w.DATE_W = date '2021-01-02' then 1 end) as ODATE2,
  count(case when w.DATE_W = date '2021-01-03' then 1 end) as ODATE3
from T_EMPLOYEE e
inner join T_WORK w
  on w.ID_E = e.ID_E
group by e.ID_E, e.NAME
order by e.ID_E;


OID_E
ONAME
ODATE1
ODATE2
ODATE3


1
Anne
1
1
0

2
Bob
1
0
1