<3 correlated subqueries

gistfile1.txt

SELECT [me].[id], [me].[name], [me].[group_id], [me].[user], [me].[version], [me].[is_active], [key_server_side_mia], [button_server_side_mia], [messenger_server_side_
mia], [key_pro_server_side_mia], [key_status], [button_status], [messenger_status], [key_pro_status], [is_group_active], [last_checkin], [macro]
  FROM (
    SELECT [me].[id], [me].[name], [me].[group_id], [me].[user], [me].[version], [me].[is_active], [key_server_side_mia], [button_server_side_mia], [messenger_server_s
ide_mia], [key_pro_server_side_mia], [key_status], [button_status], [messenger_status], [key_pro_status], [is_group_active], [last_checkin], [macro],  ROW_NUMBER() OVE
R (
        ORDER BY
        SELECT( 1 )
       ) AS [rno__row__index]
      FROM (
        SELECT [me].[id], [me].[name], [me].[group_id], [me].[user], [me].[version], [me].[is_active], (
            SELECT CASE WHEN device.last_checkin < DATEADD ( second, 4 * 60, GETUTCDATE(  ) ) THEN 1 ELSE 0 END
              FROM [Computer_Device] [computer_device_links_alias]
              JOIN [Devices] [device]
                ON [device].[id] = [computer_device_links_alias].[device_id]
              LEFT JOIN [Computer_Device] [computer_device_links]
                ON [computer_device_links].[device_id] = [device].[id]
              LEFT JOIN [Computers] [computer]
                ON [computer].[id] = [computer_device_links].[computer_id]
              LEFT JOIN [ComputerGroups] [group]
                ON [group].[id] = [computer].[group_id]
            WHERE [device].[type_id] = '2' AND [group].[is_lynx_key_pro] = '0' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[
id]
           ) AS [key_server_side_mia], (
            SELECT CASE WHEN device.last_checkin < DATEADD ( second, 4 * 60, GETUTCDATE(  ) ) THEN 1 ELSE 0 END
              FROM [Computer_Device] [computer_device_links_alias]
              JOIN [Devices] [device]
                ON [device].[id] = [computer_device_links_alias].[device_id]
            WHERE [device].[type_id] = '1' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
           ) AS [button_server_side_mia], (
            SELECT CASE WHEN device.last_checkin < DATEADD ( second, 4 * 60, GETUTCDATE(  ) ) THEN 1 ELSE 0 END
              FROM [Computer_Device] [computer_device_links_alias]
              JOIN [Devices] [device]
                ON [device].[id] = [computer_device_links_alias].[device_id]
            WHERE [device].[type_id] = '3' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
           ) AS [messenger_server_side_mia], (
            SELECT CASE WHEN device.last_checkin < DATEADD ( second, 4 * 60, GETUTCDATE(  ) ) THEN 1 ELSE 0 END
              FROM [Computer_Device] [computer_device_links_alias]
              JOIN [Devices] [device]
                ON [device].[id] = [computer_device_links_alias].[device_id]
              LEFT JOIN [Computer_Device] [computer_device_links]
                ON [computer_device_links].[device_id] = [device].[id]
              LEFT JOIN [Computers] [computer]
                ON [computer].[id] = [computer_device_links].[computer_id]
              LEFT JOIN [ComputerGroups] [group]
                ON [group].[id] = [computer].[group_id]
            WHERE [device].[type_id] = '2' AND [group].[is_lynx_key_pro] = '1' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[
id]
           ) AS [key_pro_server_side_mia], (
            SELECT [device].[status_id]
              FROM [Computer_Device] [computer_device_links_alias]
              JOIN [Devices] [device]
                ON [device].[id] = [computer_device_links_alias].[device_id]
              LEFT JOIN [Computer_Device] [computer_device_links]
                ON [computer_device_links].[device_id] = [device].[id]
              LEFT JOIN [Computers] [computer]
                ON [computer].[id] = [computer_device_links].[computer_id]
              LEFT JOIN [ComputerGroups] [group]
                ON [group].[id] = [computer].[group_id]
            WHERE [device].[type_id] = '2' AND [group].[is_lynx_key_pro] = '0' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
           ) AS [key_status], (
            SELECT [device].[status_id]
              FROM [Computer_Device] [computer_device_links_alias]
              JOIN [Devices] [device]
                ON [device].[id] = [computer_device_links_alias].[device_id]
            WHERE [device].[type_id] = '1' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
           ) AS [button_status], (
            SELECT [device].[status_id]
              FROM [Computer_Device] [computer_device_links_alias]
              JOIN [Devices] [device]
                ON [device].[id] = [computer_device_links_alias].[device_id]
            WHERE [device].[type_id] = '3' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
           ) AS [messenger_status], (
            SELECT [device].[status_id]
              FROM [Computer_Device] [computer_device_links_alias]
              JOIN [Devices] [device]
                ON [device].[id] = [computer_device_links_alias].[device_id]
              LEFT JOIN [Computer_Device] [computer_device_links]
                ON [computer_device_links].[device_id] = [device].[id]
              LEFT JOIN [Computers] [computer]
                ON [computer].[id] = [computer_device_links].[computer_id]
              LEFT JOIN [ComputerGroups] [group]
                ON [group].[id] = [computer].[group_id]
            WHERE [device].[type_id] = '2' AND [group].[is_lynx_key_pro] = '1' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
           ) AS [key_pro_status], (
            SELECT [group_alias].[is_active]
              FROM [ComputerGroups] [group_alias]
            WHERE [group_alias].[id] = [me].[group_id]
           ) AS [is_group_active], (
            SELECT MAX( [device].[last_checkin] )
              FROM [Computer_Device] [computer_device_links_alias]
              JOIN [Devices] [device]
                ON [device].[id] = [computer_device_links_alias].[device_id]
            WHERE [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
           ) AS [last_checkin], [macro].[to] AS [macro]
          FROM [Computers] [me]
          LEFT JOIN [Lookup] [macro]
            ON [macro].[from] = [me].[name]
          JOIN [ComputerGroups] [group]
            ON [group].[id] = [me].[group_id]
        WHERE [group].[is_active] = '1' AND [me].[is_active] = '1'
       ) [me]
   ) [me]
WHERE [rno__row__index] >= '1' AND [rno__row__index] <= '25'