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:
- Translate the time unit (@DatePart) into a number of days (e.g., 1 for days, 7 for weeks).
- Generate a sequence of day numbers, starting at 0 and ending at the total number of days represented by @MaxPeriod * Unit Size.
- Calculate band ranges for each day value, based on the increment and unit.
- Format each band label for display (e.g., "6–10 Weeks", "11–15 Days").
- 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);
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.