MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE master_apps_issue
(
  id               int     NOT NULL
, l2_respond_by    varchar(10) NULL
, l2_respond_date  datetime    NULL
, l2_return_by     varchar(10) NULL
, l2_return_date   datetime    NULL
, l2_resolved_by   varchar(10) NULL
, l2_resolved_date datetime    NULL
);
INSERT INTO
  master_apps_issue
(
  id
, l2_respond_by
, l2_respond_date
, l2_return_by
, l2_return_date
, l2_resolved_by
, l2_resolved_date
)
VALUES
  (1, 'Alice', '2021-07-02 09:30', 'Bob'  , '2021-07-02 10:00', 'Carol', '2021-07-03 12:00')
, (2, 'Carol', '2021-07-05 09:30', 'Bob'  , '2021-07-06 10:00', 'Bob'  , '2021-07-06 12:00')
, (2, 'David', '2021-07-07 09:30', 'Alice', '2021-07-08 10:00', 'David', '2021-07-10 12:00')
;
SELECT
  rrr.name
, SUM(rrr.action = 'respond' ) AS total_l2_respond_by
, SUM(rrr.action = 'return'  ) AS total_l2_return_by
, SUM(rrr.action = 'resolved') AS total_l2_resolved_by
FROM
  master_apps_issue AS mai
  CROSS JOIN LATERAL
  (
    SELECT mai.l2_respond_by , 'respond'  WHERE mai.l2_respond_date  BETWEEN '2021-07-01 00:00:00' AND '2021-07-09 23:59:00'
    UNION ALL
    SELECT mai.l2_return_by  , 'return'   WHERE mai.l2_return_date   BETWEEN '2021-07-01 00:00:00' AND '2021-07-09 23:59:00'
    UNION ALL
    SELECT mai.l2_resolved_by, 'resolved' WHERE mai.l2_resolved_date BETWEEN '2021-07-01 00:00:00' AND '2021-07-09 23:59:00'
  ) AS rrr (name, action)
GROUP BY
  rrr.name
;


name
total_l2_respond_by
total_l2_return_by
total_l2_resolved_by


Alice
1
1
0

Bob
0
2
1

Carol
1
0
1

David
1
0
0