select
   mdd.MD_Date_DateInteger AS MD_Date_SnapshotDate,
   mdt.MD_Time_TimeInteger AS MD_Time_SnapshotTime,
mdr.MD_Room_SurrogateKey,
ISNULL(MAX(mdb.MD_Building_SurrogateKey), (SELECT MD_Building_SurrogateKey from MD_Building WHERE MD_Building_ID = 'UNKN')) AS MD_Building_SurrogateKey,
MAX(mdr.MD_Room_CapacityExact) AS MD_Room_CapacityExact
FROM
   (SELECT MD_DATE_DATEINTEGER
    FROM dbo.MD_DATE
    WHERE MD_DATE_SQLDATE >= DATEADD(yy, -1, GETDATE())
      AND MD_DATE_SQLDATE <= DATEADD(dd, 7, GETDATE())) mdd,

   (SELECT MD_TIME_TIMEINTEGER
    FROM dbo.MD_TIME
    WHERE MD_TIME_SECOND = 0
      AND MD_TIME_MINUTE = 0) mdt,

   (SELECT DISTINCT m1.MD_ROOM_SURROGATEKEY, m1.MD_ROOM_BUILDINGID, m1.MD_ROOM_CAPACITYEXACT
    FROM dbo.MD_ROOM m1, dbo.MF_BOOKING m2
    WHERE m1.MD_ROOM_SURROGATEKEY = m2.MD_ROOM_SURROGATEKEY
-- AND m2.MF_Booking_SchedulingSystem = 'EMS'
      AND m1.MD_ROOM_CAPACITYEXACT IS NOT NULL
AND m1.MD_ROOM_CAPACITYEXACT > 0) mdr
   INNER JOIN dbo.MD_BUILDING mdb
   ON mdr.MD_ROOM_BUILDINGID = mdb.MD_BUILDING_ID
GROUP BY
   mdd.MD_Date_DateInteger,
   mdt.MD_Time_TimeInteger,
mdr.MD_Room_SurrogateKey