Month over Month querying in multiple dimension tables

Currently, we have a data warehouse (Azure SQL Server) that I am connecting to. The problem: I need to have month-over-month counts of vacant properties separated by the region they are located. Properties can be categorized as multi-family (more than one door), or single-family (one door). If a property is vacant in one month and continues to be in the next month, we need to count it. There is a possibility the record will remain idle so I will not have a row in the DB that I can associate with the later month

Context (simplified):

tblPWDimBuilding

tblPWDimUnit

RowEffectiveDate – As we have historical data in dim tables, this column specifies that the record is active from this particular date.

RowExpirationDate – This is again for historical data. This column specifies that record got expired on that particular date i.e. inactive record, for active records the value is null.

RowIsActive - 1 signifies active record whereas 0 signifies inactive record (Historical data).

The expected Results from the table above:

(notice the status of the property on a multi-family is ignored and the unit is considered instead)

Where in April, we have results from the single-family home with ID 1235, and from the multi-family home, where one door is vacant with building Id of 1238 and unit ID of 2124. Moving into May, we don't have any new records in the DIM table for buildings or units so the 2 doors from April are still vacant in May and need to be accounted for, along with the newly vacant building with ID 1237. This process will need to be used when reviewing the trailing 6-12 months so we can't just look at the last month's period. I am not sure how to emulate this waterfall like counting process. My unsuccessful attempt is below:

    SELECT
         DATEPART(YEAR, RowEffectiveDate) AS Year,
         DATEPART(MONTH, RowEffectiveDate) As Month,
         OrganizationName,
         COUNT(DISTINCT b.BuildingId) AS [Vacant Count]
    FROM curated.tblPWDimBuilding b
    WHERE b.Status = 'Vacant'
        AND b.Active = 'True'
    GROUP BY
        DATEPART(YEAR, RowEffectiveDate),
        DATEPART(MONTH, RowEffectiveDate),
        OrganizationName

    UNION

    SELECT
        DATEPART(YEAR, u.RowEffectiveDate) AS Year,
        DATEPART(MONTH, u.RowEffectiveDate) As Month,
        u.OrganizationName,
        COUNT(DISTINCT u.UnitID) AS [Vacant Count]
    FROM curated.tblPWDimBuilding b
              INNER JOIN curated.tblPWDimUnits u ON b.BuildingId = u.BuildingId
                 AND u.Status = 'Vacant'
    WHERE b.Active = 'True'
    GROUP BY
        DATEPART(YEAR, u.RowEffectiveDate),
        DATEPART(MONTH, u.RowEffectiveDate),
        u.OrganizationName;

In the code above I have a union between the single-family perspective and the multi-family perspective at the unit level. I then group by the year and month of when the record was enabled in the database, and group by the organization or region that the property is located. The Active=True flag is a flag specific to the source system to be sure it wasn't deactivated.

The above is close, but when using the row effective date it fails to account for the case where a vacant property needs to be reflected in the next month and has been idle (where the record last month will be the one marked as active) The grouping will no longer work correctly. I'm stuck! How might one solve this? Where might I have gone wrong?

For records that remain idle and need to be counted in the next month use a date dimension table or by generate a list of months in a CTE and then join that with your current query. Below the MonthList CTE is the needed list of month starts created from the RowEffectiveDate column in both tables. Then, for each month, the query checks if a record is active during that month if the RowEffectiveDate is less than or equal to the month start and the RowExpirationDate is greater than the month start, or NULL, which will include idle records in the next month's count:

WITH MonthList
AS (
    SELECT DISTINCT DATEADD(MONTH, DATEDIFF(MONTH, 0, RowEffectiveDate), 0) AS MonthStart
    FROM (
        SELECT RowEffectiveDate FROM curated.tblPWDimBuilding
        UNION
        SELECT RowEffectiveDate FROM curated.tblPWDimUnits
        ) 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 curated.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 curated.tblPWDimBuilding b ON b.Active = 'True'
    AND b.RowEffectiveDate <= m.MonthStart
    AND (
        b.RowExpirationDate > m.MonthStart
        OR b.RowExpirationDate IS NULL
        )
INNER JOIN curated.tblPWDimUnits 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;

note without sample data, or an expected result, it isn't possible to verify that query, but I think the important part of it is to: create & use a date dimension table, or continue generating a list of months - then join your data to that as it enables the "look ahead" logic needed.

I'm also unsure if you should use UNION or UNION ALL - it is possible that UNION is incorrect as it may suppress rows that should be reported - but again without data it is difficult to judge.

It seems that you need to track the vacant properties month over month, considering both single-family and multi-family properties. To address the issue of vacant properties that continue to be vacant in the next month but may not have a corresponding row in the database, you can modify your query to include a subquery that identifies the properties that were vacant in the previous month.

Here's an example of how you can modify your query to achieve this:

WITH PreviousMonthVacant AS (
    SELECT
        DATEADD(MONTH, -1, RowEffectiveDate) AS PreviousMonth,
        CASE
            WHEN Status = 'Vacant' THEN BuildingId
            WHEN u.Status = 'Vacant' THEN u.BuildingId
            ELSE NULL
        END AS VacantBuildingId,
        CASE
            WHEN Status = 'Vacant' THEN NULL
            WHEN u.Status = 'Vacant' THEN u.UnitId
            ELSE NULL
        END AS VacantUnitId
    FROM curated.tblPWDimBuilding b
    LEFT JOIN curated.tblPWDimUnits u ON b.BuildingId = u.BuildingId
    WHERE
        b.Active = 'True'
        OR u.Active = 'True'
)
SELECT
    DATEPART(YEAR, RowEffectiveDate) AS Year,
    DATEPART(MONTH, RowEffectiveDate) AS Month,
    OrganizationName,
    COUNT(DISTINCT b.BuildingId) AS [Vacant Count]
FROM curated.tblPWDimBuilding b
LEFT JOIN curated.tblPWDimUnits u ON b.BuildingId = u.BuildingId
WHERE
    b.Active = 'True'
    OR u.Active = 'True'
    OR b.BuildingId IN (
        SELECT VacantBuildingId
        FROM PreviousMonthVacant
        WHERE VacantBuildingId IS NOT NULL
            AND DATEPART(YEAR, PreviousMonth) = DATEPART(YEAR, b.RowEffectiveDate)
            AND DATEPART(MONTH, PreviousMonth) = DATEPART(MONTH, b.RowEffectiveDate)
    )
GROUP BY
    DATEPART(YEAR, RowEffectiveDate),
    DATEPART(MONTH, RowEffectiveDate),
    OrganizationName

I used a common table expression (CTE) to identify the vacant properties in the previous month. The CTE includes the previous month's date, the vacant building ID, and the vacant unit ID.

A left join is performed on the building and unit tables to include the vacant properties in the result. With that, a condition is added to include the vacant properties from the previous month by checking if the building ID is present in the subquery results.

By including this logic, you should be able to capture the vacant properties month-over-month, even if they were idle in the previous month and do not have a corresponding row in the database.