Here is what I propose:

WITH StartingPoint AS

(

--SELECT

--count([Unit]) as Actual,

--unit,

--[1st_of_month],

--last_of_month

--FROM [lchs_live].[dbo].[Full_Referrals_Dataset_live]

--where RfR1 =

--'18 month project'

--group by unit,

--[1st_of_month],

--[last_of_month]

SELECT Actual=6,unit='NW',[1st_of_month]='2013-08-01',last_of_month='2013-08-31'

UNION ALL SELECT 4,'SE','2013-08-01','2013-08-31'

UNION ALL SELECT 5,'SW','2013-08-01','2013-08-31'

),

Units (unit) AS

(

SELECT 'NW' UNION ALL SELECT 'SE' UNION ALL SELECT 'SW' UNION ALL SELECT 'NE'

)

SELECT Actual=ISNULL(Actual, 0), b.unit, a.[1st_of_month], a.last_of_month

FROM

(

SELECT DISTINCT [1st_of_month], last_of_month

FROM StartingPoint a

) a

CROSS JOIN Units b

LEFT JOIN StartingPoint c ON b.unit = c.unit