I've written a query that uses 3 subqueries to return 3 values for each employee in the main query. I need to add a 4th value for each employee, that is dependant or calculated from the 3 subquery values.

I have only been able to do this by re-writing out the subqueries in my `IIF` statement, but as it's pretty heavy on the database, this has resulted in a performance drop of over triple the execution time of the query.

```
SELECT a.emp_id, a.emp_name,
    IIF((SELECT AVG(productivity_score) FROM productivity as b WHERE a.emp_id = b.emp_id) > 100, 'Y', 'N') as [prod],
    IIF((SELECT AVG(lateness_score) FROM lateness as c WHERE a.emp_id = c.emp_id) > 80, 'Y', 'N') as [late],
    IIF((SELECT AVG(attendance_score) FROM attendance as d WHERE a.emp_id = d.emp_id) > 80, 'Y', 'N') as [attn],

-- ** status of all 3 here ** --
    IIF(IIF((SELECT AVG(productivity_score) FROM productivity as b WHERE a.emp_id = b.emp_id) > 100, 'Y', 'N') = 'Y'
    AND IIF((SELECT AVG(lateness_score) FROM lateness as c WHERE a.emp_id = c.emp_id) > 80, 'Y', 'N') = 'Y'
    AND IIF((SELECT AVG(attendance_score) FROM attendance as d WHERE a.emp_id = d.emp_id) > 80, 'Y', 'N') = 'Y',
    'Y','N') as [eligibility]
FROM employee as a;

```

What I want is to be able to write it almost like this:

```
-- ** status of all 3 here ** --
    IIF([prod] = 'Y'
    AND [late] = 'Y'
    AND [attn] = 'Y', 'Y','N') as [eligibility]
FROM employee as a;

```

Is there a way to write it to prevent it having to re-run the subqueries again, or should I be writing this query in a completely different way?