SQLServerCentral Article

Dimensional Modeling Case Study Part 3 - Dynamic Days Dimension

,

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);
This returns a dataset that maps each day value to its corresponding band:
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

Example 2: 5-Week Bands Within a 15-Week Range

In another scenario, we might be reviewing long-term trends, such as how far in advance appointments are typically booked or rescheduled. In this case, it makes sense to group the data by weeks rather than days. To create 5-week bands up to 15 weeks, we can run:

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

This is ideal for cohort analysis, booking trends, or operational planning across a multi-week horizon.

A Note on Labeling Accuracy for Week Bands

You may notice that the band labels for weeks (or months/years) aren’t strictly aligned to calendar logic. For example, 36 days falls into the "5–10 Weeks" band even though it technically starts the sixth week. This is because:

  • Non-day bands aggregate multiple days (e.g., one week = 7 days), so the cut-off points are based on numeric calculations, not calendar boundaries.
  • The labels prioritize clarity and consistency over strict time semantics. The goal is to help users understand and group data easily, not to align to ISO weeks or fiscal calendars.
  • Day-based bands (e.g., 0–5 Days) are more precise by nature and don't face this ambiguity.

For most reporting use cases, this level of approximation is sufficient and user-friendly. However, if your application requires exact calendar alignment, you may want to adjust the logic accordingly.

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating