Fastest way to determine if a single row exists in a another joined table, equivalent of the ANY linq function [closed]

I am trying to return a bit field for a join onto another table but where if a single record exists it returns ‘1’ and if none exist it returned ‘0’. I can’t afford to slow down the operation so it needs to be the most efficient solution possible. I am trying to achieve this on the HasRecord line in the select statement – this currently doesn’t work.

SELECT 
    AVG(CS.HourMinutes00) AS HourMinutes00,
    AVG(CS.HourMinutes01) AS HourMinutes01,
    AVG(CS.HourMinutes02) AS HourMinutes02,
    AVG(CS.HourMinutes03) AS HourMinutes03,
    AVG(CS.HourMinutes04) AS HourMinutes04,
    AVG(CS.HourMinutes05) AS HourMinutes05,
    AVG(CS.HourMinutes06) AS HourMinutes06,
    AVG(CS.HourMinutes07) AS HourMinutes07,
    AVG(CS.HourMinutes08) AS HourMinutes08,
    AVG(CS.HourMinutes09) AS HourMinutes09,
    AVG(CS.HourMinutes10) AS HourMinutes10,
    AVG(CS.HourMinutes11) AS HourMinutes11,
    AVG(CS.HourMinutes12) AS HourMinutes12,
    AVG(CS.HourMinutes13) AS HourMinutes13,
    AVG(CS.HourMinutes14) AS HourMinutes14,
    AVG(CS.HourMinutes15) AS HourMinutes15,
    AVG(CS.HourMinutes16) AS HourMinutes16,
    AVG(CS.HourMinutes17) AS HourMinutes17,
    AVG(CS.HourMinutes18) AS HourMinutes18,
    AVG(CS.HourMinutes19) AS HourMinutes19,
    AVG(CS.HourMinutes20) AS HourMinutes20,
    AVG(CS.HourMinutes21) AS HourMinutes21,
    AVG(CS.HourMinutes22) AS HourMinutes22,
    AVG(CS.HourMinutes23) AS HourMinutes23,
    CASE WHEN EXISTS (SELECT TOP 1 * FROM LocationHistory LH JOIN MachineActivity MA ON LH.MachineActivityId = MA.Id JOIN Machine M ON MA.MachineId = M.Id WHERE CS.ActivityDate = MA.ActivityDate AND CS.MachineId = M.Id) THEN CAST (1 AS BIT) ELSE CAST (0 AS BIT) END AS HasRecord
FROM 
    CustomerSummary
WHERE
    ActivityDate >= '2021-02-15'
AND
    ActivityDate <= '2021-02-21'
AND 
    CS.MachineNumber = '1'
AND
    CS.SiteId = '1'
GO

Answer

Subqueries with aggregation are usually tricky. You can fix the syntax issue by moving the logic to the FROM clause using APPLY:

SELECT . . .,
       v.HasRecord
FROM  CustomerSummary CS CROSS APPLY
      (VALUES (CASE WHEN EXISTS (SELECT 1
                                 FROM LocationHistory LH JOIN 
                                      MachineActivity MA
                                      ON LH.MachineActivityId = MA.Id JOIN
                                      Machine M
                                      ON MA.MachineId = M.Id
                                 WHERE CS.ActivityDate = MA.ActivityDate AND
                                       CS.MachineId = M.Id
                               )
                    THEN CAST (1 AS BIT)
                    ELSE CAST (0 AS BIT)
               END)
      ) v(HasRecord)
WHERE . . .
GROUP BY v.HasRecord;

This should be performant.

Notes:

• Do not use NOLOCK unless you really know what you are doing. Most users do not want inconsistent data used for their results.
• SELECT TOP is not needed with EXISTS.