求MS SQL Server中住宿提供商入住率计算的T-SQL解决方案

Hey there! As someone with a couple years of MS SQL Server experience under my belt, I totally feel your pain with this occupancy rate calculation—those overlapping placement dates and variable month lengths are easy to mess up, especially when you start getting wonky percentages like 300% or 3%. Let’s walk through a solid T-SQL solution that fixes those issues.

First, Let’s Clarify the Core Problem #

The biggest mistake here is assuming fixed days per month (like 30) and not properly calculating the overlap between each placement and the target time period (month/quarter). We need to calculate exactly how many days each placement contributes to a given month/quarter, even if it starts before, ends after, or overlaps entirely with the period.

Step 1: Ensure Your Date Dimension Has Proper Start/End Dates #

First, let’s make sure your tbl_Month_Year has the actual start and end dates for each period. If it only has [Year] and [Month] columns, we can generate those using DATEFROMPARTS:

WITH DatePeriods AS (
    SELECT
        [Month],
        [Year],
        PeriodStart = DATEFROMPARTS([Year], [Month], 1),
        PeriodEnd = EOMONTH(DATEFROMPARTS([Year], [Month], 1)),
        PeriodTotalDays = DAY(EOMONTH(DATEFROMPARTS([Year], [Month], 1)))
    FROM tbl_Month_Year
)

If you need quarterly periods instead, adjust this to get the first day of the quarter and last day of the quarter (e.g., DATEFROMPARTS([Year], (([Quarter]-1)*3)+1, 1) for start, and EOMONTH(DATEFROMPARTS([Year], (([Quarter]-1)*3)+3, 1)) for end).

Step 2: Calculate Overlap Days for Each Placement & Period #

Next, we need to join our providers, date periods, and placements, then calculate the number of days each placement overlaps with the period. Here’s how to handle the date boundaries:

• For a placement’s start date: use the later of the placement’s Vacancy Filled Date and the period’s start date
• For a placement’s end date: use the earlier of the placement’s Vacancy End Date (or current date if it’s NULL, meaning the placement is still active) and the period’s end date
• If the adjusted start date is after the adjusted end date, the overlap is 0 days (no contribution to the period)

Step 3: Full T-SQL Implementation #

Putting it all together, here’s the complete query:

WITH DatePeriods AS (
    -- Generate proper start/end dates for each month from tbl_Month_Year
    SELECT
        [Month],
        [Year],
        PeriodStart = DATEFROMPARTS([Year], [Month], 1),
        PeriodEnd = EOMONTH(DATEFROMPARTS([Year], [Month], 1)),
        PeriodTotalDays = DAY(EOMONTH(DATEFROMPARTS([Year], [Month], 1)))
    FROM tbl_Month_Year
),
ProviderPeriodOverlaps AS (
    -- Get all provider-period combinations, then calculate placement overlap days
    SELECT
        tSC.[Provider Name],
        dp.[Year],
        dp.[Month],
        dp.PeriodTotalDays,
        tSC.[Service Capacity],
        -- Calculate overlap days for each placement
        OverlapDays = CASE
            WHEN tPL.[Vacancy Filled Date] > dp.PeriodEnd THEN 0
            WHEN COALESCE(tPL.[Vacancy End Date], GETDATE()) < dp.PeriodStart THEN 0
            ELSE DATEDIFF(DAY,
                IIF(tPL.[Vacancy Filled Date] > dp.PeriodStart, tPL.[Vacancy Filled Date], dp.PeriodStart),
                IIF(COALESCE(tPL.[Vacancy End Date], GETDATE()) < dp.PeriodEnd, COALESCE(tPL.[Vacancy End Date], GETDATE()), dp.PeriodEnd)
            ) + 1 -- +1 because DATEDIFF counts intervals, not inclusive days
        END
    FROM tbl_Service_Capacity tSC
    CROSS JOIN DatePeriods dp -- All providers for all periods
    LEFT JOIN tbl_Placements tPL
        ON tSC.[Provider Name] = tPL.[Provider Name]
        -- Only join placements that could overlap with the period
        AND tPL.[Vacancy Filled Date] <= dp.PeriodEnd
        AND COALESCE(tPL.[Vacancy End Date], GETDATE()) >= dp.PeriodStart
)
-- Calculate final occupancy rate
SELECT
    [Provider Name],
    [Year],
    [Month],
    [Service Capacity],
    PeriodTotalDays,
    TotalPlacementDays = SUM(OverlapDays),
    OccupancyRate = CASE
        WHEN [Service Capacity] = 0 OR PeriodTotalDays = 0 THEN NULL -- Avoid division by zero
        ELSE ROUND((SUM(OverlapDays) * 100.0) / ([Service Capacity] * PeriodTotalDays), 2)
    END
FROM ProviderPeriodOverlaps
GROUP BY [Provider Name], [Year], [Month], [Service Capacity], PeriodTotalDays
ORDER BY [Provider Name], [Year], [Month];

Key Notes to Avoid Common Issues: #

• Handling NULL End Dates: We use COALESCE(tPL.[Vacancy End Date], GETDATE()) to treat ongoing placements as ending today. Adjust this if you need to use the period end date instead.
• Inclusive Day Count: Adding +1 to DATEDIFF is crucial because DATEDIFF(DAY, '2024-01-01', '2024-01-02') returns 1, but those are 2 full days.
• Division by Zero: The CASE statement in the final occupancy rate calculation prevents errors if capacity is 0 or the period has 0 days (though your date dimension shouldn’t have that).
• Overlap Filtering: The LEFT JOIN condition filters out placements that can’t possibly overlap with the period, which improves query performance.

Testing the Query #

To verify, pick a provider and month where you know the expected days. For example:

• Provider A has capacity 2
• Month is January 2024 (31 days)
• One placement runs from 2024-01-15 to 2024-01-20: that’s 6 days
• Another placement runs from 2023-12-30 to 2024-02-05: overlap with Jan 2024 is 31 days (entire month)
  Total Placement Days = 6 + 31 = 37
  Occupancy Rate = (37 / (2 * 31)) * 100 ≈ 59.68%

Run this scenario through the query and check if you get that result—it should line up correctly.

内容的提问来源于stack exchange，提问作者Gizmo191