+------------+------------+------------+
| Member_Key | VisitCount |    date    |
+------------+------------+------------+
|       4000 |          1 | 2014-05-07 |
|       4000 |          1 | 2014-05-09 |
|       4001 |          2 | 2014-05-08 |
+------------+------------+------------+

+------------+--------------+
| Member_Key |  Condition   |
+------------+--------------+
|       4000 | Diabetes     |
|       4000 | Diabetes     |
|       4001 | Hypertension |
+------------+--------------+

+------------+---------------+------------+
| Member_Key | Member_Gender | Member_DOB |
+------------+---------------+------------+
|       4000 | M             | 1970-05-21 |
|       4001 | F             | 1968-02-19 |
+------------+---------------+------------+

SELECT c.conditions,
   age.gender,
   CASE
     WHEN age.age BETWEEN 45 AND 54 THEN Sum(act.visitflag)
   END AS age_45_54_years
FROM   table_a act
   INNER JOIN (SELECT DISTINCT member_key,
                               conditions
               FROM   table_b)c
           ON c. member_key = act.member_key
   INNER JOIN (SELECT DISTINCT member_key,
                               member_gender,
                               Datepart(year, '2017-10-16') -
                               Datepart(year, member_dob) AS Age
               FROM   [table_c]) AS age
           ON age.member_key = c.member_key
GROUP  BY c.conditions,
      age.member_gender