How can I refer to my subquery values in an IIF or another subquery, without having to write it all out again?

user_10164553 0
Viewed 114

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?

tsql sql-server
3 Answers

JOIN the results together instead of using subqueries:

SELECT e.emp_id, e.emp_name,
       (CASE WHEN p.avg_productivity_score > 100 THEN 'Y' ELSE 'N' END) as productivity,
       (CASE WHEN l.avg_lateness_score > 100 THEN 'Y' ELSE 'N' END) as lateness,    
       (CASE WHEN l.avg_attendance_score > 100 THEN 'Y' ELSE 'N' END) as attendance,    
      . . . 
FROM employee e LEFT JOIN
     (SELECT p.emp_id, AVG(p.productivity_score) as avg_productivity_score
      FROM productivity p
      GROUP BY p.emp_id
     ) p
     ON p.emp_id = e.emp_id LEFT JOIN
     (SELECT l.emp_id, AVG(l.lateness_score) as avg_lateness_score
      FROM lateness l
      GROUP BY l.emp_id
     ) p
     ON l.emp_id = e.emp_id LEFT JOIN
     (SELECT a.emp_id, AVG(a.attendance_score) as avg_attendance_score
      FROM attendance a
      GROUP BY a.emp_id
     ) p
     ON a.emp_id = e.emp_id
user_11440350

You may try rewriting your query using joins, which would also give you a possible performance boost:

SELECT
    a.emp_id,
    a.emp_name,
    CASE WHEN p.prod_score_avg > 100 THEN 'Y' ELSE 'N' END AS prod,
    CASE WHEN l.late_score_avg > 80 THEN 'Y' ELSE 'N' END AS late
    CASE WHEN at.attn_score_avg > 80 THEN 'Y' ELSE 'N' END AS attn,
    CASE WHEN p.prod_score_avg > 100 AND
              l.late_score_avg > 80 AND
              at.attn_score_avg > 80 THEN 'Y' ELSE 'NO' END AS eligibility
FROM employee a
LEFT JOIN
(
    SELECT emp_id, AVG(productivity_score) AS prod_score_avg
    FROM productivity
    GROUP BY emp_id
) p
    ON a.emp_id = p.emp_id
LEFT JOIN
(
    SELECT emp_id, AVG(lateness_score) AS late_score_avg
    FROM lateness
    GROUP BY emp_id
) l
    ON a.emp_id = l.emp_id
LEFT JOIN
(
    SELECT emp_id, AVG(attendance_score) AS attn_score_avg
    FROM attendance
    GROUP BY emp_id
) at
    ON a.emp_id = at.emp_id

Note that the major drawback of your current approach is that you are performing aggregations in the select clause using correlated subqueries over three other tables. In the worst case scenario, this would force SQL Server to scan each of those three tables once per every record in the employee table. By moving this aggregation logic to separate subqueries, SQL Server only has to aggregate once, over each table.

user_18632290

I agree, as they all share a common ID, a left join is more suited here, but purely to answer your question of referring to subqueries without re-writing, you can try a cross apply to do this. I find it can make for good reuse of particular subqueries and tidier to read:

SELECT 
    a.emp_id, 
    a.emp_name,
    [prod].val as [prod],
    [late].val as [late],
    [attn].val as [attn],
    IIF([prod].val = 'Y' AND [late].val = 'Y' AND [attn].val = 'Y','Y','N') as [eligibility]
FROM
    employee as a
    cross apply
        (select IIF((SELECT AVG(productivity_score) FROM productivity as b WHERE a.emp_id = b.emp_id) > 100, 'Y', 'N')) as [prod](val)
    cross apply
        (select IIF((SELECT AVG(lateness_score) FROM lateness as c WHERE a.emp_id = c.emp_id) > 80, 'Y', 'N')) as [late](val)
    cross apply
        (select IIF((SELECT AVG(attendance_score) FROM attendance as d WHERE a.emp_id = d.emp_id) > 80, 'Y', 'N') as [attn](val)
user_78584510
Related