so far i have done this but not sure if it is producing the right results.

; with cte as ( select Room, case when bookedtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime) when Endtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime) else null end as Mins from xxxxx where case when bookedtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime) when Endtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime) else null end is not null and case when bookedtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime) when Endtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime) else null end > 0 ) select Room, SUM(60*FLOOR(Mins) + 100*(Mins-FLOOR(Mins)) ) / 60 [HoursSpent]from ctegroup by Room

Result--------------------

Room HoursSpentA1 3219A2 2272A3 3579A4 4393

If it is in time how do i show in time so its not exceding 60minsFor room A2 it cant be 22 hrs abd 72mins so how can i get the results in hours and mins????