The reason for getting just one row ist the CROSS APPLY: there are only data of UnitId2005 in the UnitsData table, eliminating the remaining rows.

I broke my approach down into three sections:

UD30 to get the data for question 4,

UD48 for questions 7 and 8 and

UD for question 5 and 6 (since the lates value might be older tha 30 days for a specific Unit).

SELECT

Unit.Name,

Unit.SerialNumber,

Unit.Location,

UD30.AverageValue,

UD.LastDate,

UD.LastValue,

CASE WHEN UD48.Cnt IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END AS LT48hrs,

CASE WHEN UD48.Cnt >=2 THEN 'TRUE' ELSE 'FALSE' END AS SameValue

FROM Units Unit

OUTER APPLY -- get the values for the 30 day average

(

SELECT TOP 1

UnitID,

AVG(PowerConsumptionValue) AS AverageValue

FROM UnitsData UD2

WHERE Unit.ID = UD2.UnitID

AND DateOfData>= DATEADD(dd,-30,getdate())

GROUP BY UnitID

) UD30

OUTER APPLY

(

SELECT x.UnitId, Max(cnt) AS Cnt

FROM

(

SELECT UnitId,PowerConsumptionValue, COUNT(*) AS Cnt

FROM UnitsData

WHERE DateOfData > DATEADD(hh,-48,GETDATE())

GROUP BY UnitId,PowerConsumptionValue

)x

WHERE x.UnitId = Unit.Id

GROUP BY x.UnitId

)UD48

OUTER APPLY

(

SELECT TOP 1

DateOfData AS LastDate,

PowerConsumptionValue AS LastValue

FROM UnitsData UD

WHERE UD.UnitId = Unit.Id

ORDER BY UD.DateOfData DESC, ID DESC

) UD