SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

7 rows affected

WITH MonthList
AS (
    SELECT DISTINCT DATEADD(MONTH, DATEDIFF(MONTH, 0, RowEffectiveDate), 0) AS MonthStart
    FROM (
        SELECT RowEffectiveDate FROM dbo.tblPWDimBuilding
        UNION
        SELECT RowEffectiveDate FROM dbo.tblPWDimUnit
        ) AS Dates
    )
SELECT DATEPART(YEAR, m.MonthStart) AS Year, DATEPART(MONTH, m.MonthStart) AS Month, OrganizationName, COUNT(DISTINCT b.BuildingId) AS [Vacant Count]
FROM MonthList m
INNER JOIN dbo.tblPWDimBuilding b ON b.STATUS = 'Vacant'
    AND b.Active = 'True'
    AND b.RowEffectiveDate <= m.MonthStart
    AND (
        b.RowExpirationDate > m.MonthStart
        OR b.RowExpirationDate IS NULL
        )
GROUP BY DATEPART(YEAR, m.MonthStart), DATEPART(MONTH, m.MonthStart), OrganizationName

UNION /* should this be UNION ALL ?? */

SELECT DATEPART(YEAR, m.MonthStart) AS Year, DATEPART(MONTH, m.MonthStart) AS Month, u.OrganizationName, COUNT(DISTINCT u.UnitID) AS [Vacant Count]
FROM MonthList m
LEFT JOIN dbo.tblPWDimBuilding b ON b.Active = 'True'
    AND b.RowEffectiveDate <= m.MonthStart
    AND (
        b.RowExpirationDate > m.MonthStart
        OR b.RowExpirationDate IS NULL
        )
INNER JOIN dbo.tblPWDimUnit u ON b.BuildingId = u.BuildingId
    AND u.STATUS = 'Vacant'
    AND u.RowEffectiveDate <= m.MonthStart
    AND (
        u.RowExpirationDate > m.MonthStart
        OR u.RowExpirationDate IS NULL
        )
GROUP BY DATEPART(YEAR, m.MonthStart), DATEPART(MONTH, m.MonthStart), u.OrganizationName;

Msg 207 Level 16 State 1 Line 13
Invalid column name 'Active'.
Msg 207 Level 16 State 1 Line 16
Invalid column name 'RowExpirationDate'.
Msg 207 Level 16 State 1 Line 17
Invalid column name 'RowExpirationDate'.