CREATE TABLE cases
    ([caseno] int, [case_detail] varchar(8), [date_received] datetime)
;

INSERT INTO cases
    ([caseno], [case_detail], [date_received])
VALUES
    (1, 'DETAIL A', '2018-04-01 00:00:00'),
    (2, 'DETAIL B', '2018-05-01 00:00:00'),
    (3, 'DETAIL C', '2018-06-01 00:00:00')
;

CREATE TABLE activities
    ([caseno] int, [activity] int, [team] varchar(1))
;

INSERT INTO activities
    ([caseno], [activity], [team])
VALUES
    (1, 00, 'A'),
    (1, 10, 'A'),
    (1, 00, 'A'),
    (1, 00, 'B'),
    (1, 90, 'C'),
    (1, 00, 'C'),
    (1, 00, 'A'),
    (2, 10, 'A'),
    (2, 00, 'A'),
    (2, 00, 'B'),
    (3, 90, 'C'),
    (3, 00, 'C')
;

SELECT T.*, A.A, B.B, C.C FROM cases T
LEFT JOIN (SELECT caseno, COUNT(*) AS A FROM activities WHERE activity = '00' AND team = 'A' GROUP BY caseno) A ON T.[caseno] = A.[caseno]
LEFT JOIN (SELECT caseno, COUNT(*) AS B FROM activities WHERE activity = '00' AND team = 'B' GROUP BY caseno) B ON T.[caseno] = B.[caseno]
LEFT JOIN (SELECT caseno, COUNT(*) AS C FROM activities WHERE activity = '00' AND team = 'C' GROUP BY caseno) C ON T.[caseno] = C.[caseno]

SELECT c.caseno, case_detail, date_received,
   COUNT(CASE WHEN team = 'A' THEN 1 END) AS a,
   COUNT(CASE WHEN team = 'B' THEN 1 END) AS b,
   COUNT(CASE WHEN team = 'C' THEN 1 END) AS c
FROM cases c
LEFT JOIN activities a
  ON c.caseno = a.caseno
 AND a.activity = '00'
GROUP BY c.caseno, case_detail, date_received;