Optimising multiple selects with a WHILE loop

Question

I need to run a larger version of the SQL Server 2008 query below (42 repeats of the SELECT, as apposed to the 4 repeats shown below). It does not seem to me to be very optimised, and an ideal candidate for using a while loop, but after may hours of searching and trying I can't get it to work. Can anyone suggest a more optimised query to accomplish what I need?

Thanks

SELECT 
    t1.Occupied as "1", t2.Occupied as "2", 
    t3.Occupied as "3", t4.Occupied as "4"
FROM 
    (SELECT 
         COUNT(function_id) AS Occupied
     FROM 
         ev_functions 
     WHERE 
         room_id = 22 
         AND DATEADD(HOUR,3,GETDATE()) > from_date_time 
         AND GETDATE() < to_date_time 
         AND function_status_id = 6) t1,
    (SELECT 
         COUNT(function_id) AS Occupied
     FROM 
         ev_functions 
     WHERE 
         room_id = 1 
         AND DATEADD(HOUR,3,GETDATE()) > from_date_time 
         AND GETDATE() < to_date_time 
         AND function_status_id = 6) t2,
    (SELECT 
         COUNT(function_id) AS Occupied
     FROM 
         ev_functions 
     WHERE 
         room_id = 1 
         AND DATEADD(HOUR,3,GETDATE()) > from_date_time 
         AND GETDATE() < to_date_time 
         AND function_status_id = 6) t3,
    (SELECT 
         COUNT(function_id) AS Occupied
     FROM 
         ev_functions 
     WHERE 
         room_id = 1 
         AND DATEADD(HOUR,3,GETDATE()) > from_date_time 
         AND GETDATE() < to_date_time 
         AND function_status_id = 6) t4

Answer 1

In your case,you are accessing Same table multiple times for a small set of changes

use SUM with CASE to avoid that and also for readability

sum(case when room_id = 1 then 1 else 0 end ) as room1,
sum(case when room_id = 22 then 1 else 0 end )..
.....
and so on
from ev_functions 
where  DATEADD(HOUR,3,GETDATE()) > from_date_time 
and GETDATE() < to_date_time 
and function_status_id = 6