Background

In this case study series, we've explored how to use age and tally tables to support various forms of banding by denormalizing them into a single dimension table. Each banding, such as age groups or day ranges, is built upon a base level of granularity—ages for age-based dimensions and days for date-based dimensions. These static dimension tables are preloaded and remain unchanged until a future reload is required to support new bandings.

However, some analytical use cases require more flexibility. In particular, users may want to define custom bandings dynamically to explore what-if scenarios. One such case involves analyzing patient appointment data, where the number of days between the appointment date and cancellation date, also referred to as the notice period, is a key metric.

Instead of relying on a fixed Days dimension, a user might wish to define notice period bands on the fly. For instance, with a 10-day increment:

• 0–10 Days
• 11–20 Days
• 21–30 Days
• 31–40 Days
• 41–50 Days
• 50+ Days

If most cancellations occur within 25 days, a finer-grained 5-day increment might be used instead:

• 0–5 Days
• 6–10 Days
• 11–15 Days
• 16–20 Days
• 21–25 Days
• 25+ Days

Alternatively, the same notice period might be grouped in weeks:

• 0–5 Weeks
• 6–10 Weeks
• 11–15 Weeks
• 15+ Weeks

Or even months:

• 0–2 Months
• 3–4 Months
• 5–6 Months
• 6+ Months

As you can see, there are numerous possibilities depending on the business context. Supporting this level of flexibility calls for a dynamic approach—one that adapts to user input without requiring a static table for each banding scenario.

Why a Dynamic Days Dimension?

As seen in the background examples, different reporting scenarios demand different time groupings—often on the fly. These can range from:

• Standard intervals like daily, weekly, monthly, or yearly bands
• Custom ranges such as 5-day, 10-week, or 2-month bands
• Use-case-specific breakdowns to support what-if analysis and ad hoc exploration

Manually maintaining static tables for every possible configuration is neither scalable nor efficient. In fact, doing so can introduce unnecessary complexity and performance overhead—especially when working with large datasets.

To address this challenge, we can leverage a Table-Valued Function (TVF) in SQL Server to dynamically generate a Days dimension tailored to the specific parameters provided by the user. This approach offers the best of both worlds: the flexibility of custom time bands with the efficiency of SQL-based computation, all without requiring changes to the underlying data model.

Designing the Function

To support dynamic banding in reporting, we introduce a table-valued function called fn_DimDays. This function generates a customizable Days dimension based on three input parameters:

• @DatePart – Specifies the unit of time:
  'd' = Days,  'w' = Weeks, 'm' = Months, 'y' = Years
• @Increment– The size of each band (e.g., 10 days, 2 weeks, 1 month)
• @MaxPeriod – The total number of bands to generate

The function returns a table of day values, each mapped to a corresponding time band label, such as "0–10 Days" or "11–20 Days".

At a high level, the function works as follows:

1. Translate the time unit (@DatePart) into a number of days (e.g., 1 for days, 7 for weeks).
2. Generate a sequence of day numbers, starting at 0 and ending at the total number of days represented by @MaxPeriod * Unit Size.
3. Calculate band ranges for each day value, based on the increment and unit.
4. Format each band label for display (e.g., "6–10 Weeks", "11–15 Days").
5. Return the final dataset with each day value, its corresponding band label, and an ordering field for sorting.

This dynamic structure allows the function to support a wide variety of scenarios—from short notice periods to long-range appointment planning—without the need for static tables.

CREATE FUNCTION [dbo].[fn_DimDays] 
(
    @DatePart CHAR(1),  -- d: days, w: weeks, m: months, y: years
    @Increment INT,      -- Increment value for period banding
    @MaxPeriod INT       -- Maximum number of periods to generate
)
RETURNS @Days TABLE (Days INT, Band VARCHAR(100), BandOrder INT) 
AS
BEGIN
    -- Validate input and determine increment value
    DECLARE @DaysIncrement INT, @PeriodCaption VARCHAR(10);

    SET @DaysIncrement = 
        CASE @DatePart
            WHEN 'd' THEN 1
            WHEN 'w' THEN 7
            WHEN 'm' THEN 30
            WHEN 'y' THEN 365
            ELSE NULL -- Handle invalid inputs
        END;

    SET @PeriodCaption = 
        CASE @DatePart
            WHEN 'd' THEN 'Days'
            WHEN 'w' THEN 'Weeks'
            WHEN 'm' THEN 'Months'
            WHEN 'y' THEN 'Years'
        END;

    -- Exit early if invalid @DatePart
    IF @DaysIncrement IS NULL
        RETURN;

    DECLARE @MaxDays INT = @MaxPeriod * @DaysIncrement;

    -- Generate a series of days using a Tally Table approach
    ;WITH Tally AS
    (
        SELECT TOP (@MaxDays + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Days
        FROM master.dbo.spt_values
    )
    , Band AS 
    (
        -- Compute banding
        SELECT  
            Days,
            BandMin = @Increment * ((Days - 1) / (@Increment * @DaysIncrement)),
            BandMax = @Increment * ((Days - 1) / (@Increment * @DaysIncrement)) + @Increment
        FROM Tally
    )
    INSERT INTO @Days
    SELECT  
        Days,
        Band = CASE 
                WHEN @PeriodCaption = 'Days' AND Days > @Increment  
                    THEN CONCAT(BandMin + 1, '-', BandMax, ' ', @PeriodCaption)  
                ELSE CONCAT(BandMin, '-', BandMax, ' ', @PeriodCaption)  
               END,
        BandOrder = BandMax
    FROM Band
    WHERE Days <= @MaxDays

    UNION ALL

    SELECT @MaxDays + 1,  
           CONCAT(@MaxPeriod, ' ', @PeriodCaption, '+'),
           @MaxPeriod + 1

    RETURN;
END;

Using the Dynamic Days Dimension

Let’s look at two examples of how fn_DimDays can be used in practice.

Example 1: 5-Day Bands Within a 20-Day Range

Suppose we want to analyze appointment cancellations based on the notice period (i.e., the number of days between cancellation date and appointment date). We want to see how many cancellations occur in 5-day intervals, up to a total of 20 days. We can call:

SELECT * FROM fn_DimDays('d', 5, 20);

Days    Band         BandOrder
---------------------------------
0       0-5 Days       5
1       0-5 Days       5
...
5       0-5 Days       5
6       6-10 Days      10
...
10      6-10 Days      10
11      11-15 Days     15
...
20      16-20 Days     20
21      20 Days+       21

SELECT * FROM fn_DimDays('w', 5, 15)

Days    Band         BandOrder
---------------------------------
0       0-5 Weeks      5
1       0-5 Weeks      5
...
35      0-5 Weeks      5
36      5-10 Weeks     10
...
70      5-10 Weeks     10
71      10-15 Weeks    15
...
105     10-15 Weeks    15
106     15 Weeks+      16

Conclusion

This function provides a flexible, performance-friendly way to dynamically create a Days Dimension. By allowing users to specify parameters at runtime, it eliminates the need for a static table while maintaining efficiency.