Background
In the first parts of this series (part 1, part 2, part 3), we explored dimension tables for age groups, calendar days, and dynamic banding at the day level. These covered time aspects that align with calendar structures—birthdates, admission dates, lengths of stay. But many operational questions are about durations, not dates.
In healthcare especially, you often ask:
- How long did a patient wait for triage?
- How long was the ED stay?
- How quickly did staff respond to a nurse call?
- What’s the average queue time in a call center?
- How long does pre-op preparation take?
These are durations—the time between two events—often tracked in seconds, minutes, or hours, not calendar days. They aren’t tied to weekdays or holidays. They're purely numeric. To support such analysis, we introduce fn_DimTimeDuration: a dynamic table-valued function that creates a banded duration dimension on demand.
Why Not Use fn_DimDays?
While fn_DimDays is effective for day-level and coarser banding (e.g., weeks, months, years), using it for durations measured in seconds or minutes is inefficient. For example:
- 1 hour at second-level grain = 3,600 rows
- 2 days at minute-level grain = 2,880 rows
- 1 week at hour-level grain = 168 rows
If we fixed the grain at seconds for every case, a simple 7-day view would explode to over 600,000 rows. That’s overkill when your dashboard just needs to show “1–5 hours” or “6–10 minutes.”
To avoid unnecessary data volume, we design fn_DimTimeDuration with a unit-relative grain:
- One row per second (if @TimeUnit = 's')
- One row per minute (if @TimeUnit = 'm')
- One row per hour (if @TimeUnit = 'h')
This keeps the output efficient, contextual, and tightly scoped to the user’s intended banding.
Designing the Function
CREATE FUNCTION dbo.fn_DimTimeDuration
(
@TimeUnit CHAR(1), -- 's': seconds, 'm': minutes, 'h': hours
@Increment INT, -- Size of each band
@MaxUnit INT -- Maximum duration to represent
)It returns a virtual dimension table with:
- Duration : Each numeric value between 0 and @MaxUnit + 1
- Band : A label like "0–5 Minutes" or "30 Minutes+"
- BandOrder : Used for sorting or filtering band groups
Pseudocode Summary
IF @TimeUnit is invalid THEN EXIT
Set label = "Seconds" | "Minutes" | "Hours"
FOR Duration FROM 0 TO @MaxUnit:
BandMin = @Increment * ((Duration - 1) / @Increment)
BandMax = BandMin + @Increment
IF Duration == 0:
Band = "0–[Increment] [Unit]"
ELSE:
Band = "[BandMin+1]–[BandMax] [Unit]"
Append final "+ band":
Band = "[MaxUnit] [Unit]+"
Duration = MaxUnit + 1
This ensures even over-limit durations can still map to a catch-all "+ band."
Function Code
Here’s the complete SQL:
CREATE FUNCTION dbo.fn_DimTimeDuration
(
@TimeUnit CHAR(1), -- s: seconds, m: minutes, h: hours
@Increment INT, -- Banding increment
@MaxUnit INT -- Maximum unit to generate
)
RETURNS @Duration TABLE (Duration INT, Band VARCHAR(100), BandOrder INT)
AS
BEGIN
DECLARE @UnitLabel VARCHAR(10);
SET @UnitLabel =
CASE @TimeUnit
WHEN 's' THEN 'Seconds'
WHEN 'm' THEN 'Minutes'
WHEN 'h' THEN 'Hours'
END;
IF @UnitLabel IS NULL RETURN;
;WITH Tally AS (
SELECT TOP (@MaxUnit + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Duration
FROM master.dbo.spt_values
)
, Banding AS (
SELECT
Duration,
BandMin = @Increment * ((Duration - 1) / @Increment),
BandMax = @Increment * ((Duration - 1) / @Increment) + @Increment
FROM Tally
)
INSERT INTO @Duration
SELECT
Duration,
Band = CASE
WHEN Duration <= @MaxUnit THEN
CASE
WHEN Duration = 0 THEN CONCAT('0–', @Increment, ' ', @UnitLabel)
ELSE CONCAT(BandMin + 1, '–', BandMax, ' ', @UnitLabel)
END
ELSE CONCAT(@MaxUnit, ' ', @UnitLabel, '+')
END,
BandOrder = CASE
WHEN Duration <= @MaxUnit THEN BandMax
ELSE @MaxUnit + 1
END
FROM Banding
WHERE Duration <= @MaxUnit
UNION ALL
SELECT @MaxUnit + 1, CONCAT(@MaxUnit, ' ', @UnitLabel, '+'), @MaxUnit + 1;
RETURN;
END;
Example 1: 5-Minute Bands within 30 Minutes
SELECT * FROM fn_DimTimeDuration('m', 5, 30);
Duration Band BandOrder -------- ------------------- ---------- 0 0–5 Minutes 5 1 1–5 Minutes 5 2 1–5 Minutes 5 ... ... ... 30 26–30 Minutes 30 31 30 Minutes+ 31
This gives one row per minute from 0 to 30, plus a final row for anything over 30 minutes. Fact records with DurationInMinutes>30 can be capped at 31 to join on Band = '30 Minutes+'.
Example 2: 1-Hour Bands within 6 Hours
SELECT * FROM fn_DimTimeDuration('h', 1, 6);
Duration Band BandOrder -------- --------------- --------- 0 0–1 Hours 1 1 1–2 Hours 2 2 2–3 Hours 3 3 3–4 Hours 4 4 4–5 Hours 5 5 5–6 Hours 6 6 6–7 Hours 7 7 6 Hours+ 8
Again, the last row (Duration = 7) ensures that anything beyond 6 hours is grouped neatly.
Behavior and Usage
When the function runs, it builds a duration dimension with one row for each unit between 0 and @MaxUnit, and appends an extra "+ band" for overflow.
The grain depends on your chosen unit:
- @TimeUnit = 's' → 1 row per second
- @TimeUnit = 'm'→ 1 row per minute
- @TimeUnit = 'h'→ 1 row per hour
For example:
- 2 days in minutes = 2,880 rows
- 1 week in hours = 168 rows
- 1 hour in seconds = 3,600 rows
The function generates a consistent BandOrder to enable grouping and sorting in reports. There’s no calendar logic involved—just numeric differences.
Matching Over-Limit Durations
Durations beyond @MaxUnit aren’t generated by default. To join them to the final "+ band," simply cap the duration in your fact query:
-- Duration in seconds
DATEDIFF(SECOND, StartTime, EndTime) AS RawDuration,
CASE
WHEN DATEDIFF(SECOND, StartTime, EndTime) > @MaxUnit THEN @MaxUnit + 1
ELSE DATEDIFF(SECOND, StartTime, EndTime)
END AS CappedDurationThen join like:
FROM Fact f
JOIN fn_DimTimeDuration('s', 10, 600) d
ON f.CappedDuration = d.DurationThis ensures over-limit durations still match the final band like "600 Seconds+".
👉 Tip: Apply the same logic in fn_DimDays (Part 3) when capping long lengths of stay.
Real-World Use Cases in Healthcare
Let’s see how different durations benefit from tailored granularity:
Scenario Unit Max Duration Justification ------------------------------------------------------------------------------------------------- Triage wait time Seconds 3600 High precision for short waits Call center queue time Seconds 3600 Analyze abandon/drop-off behavior ED treatment duration Minutes 2880 Two days of moderate granularity Staff response to patient alert Minutes 2880 Evaluate quality and timeliness Operating room utilization Hours 168 Weekly throughput and bottleneck views Pre-op to anesthesia time Minutes 1440 Single-day procedure timelines
Conclusion
fn_DimTimeDuration rounds out the time-dimension toolkit by supporting fine-grained duration banding based on runtime parameters. It complements fn_DimDays with flexibility, compactness, and performance.
By combining this with calculated durations in your fact tables and capping over-limit values to the final "+ band," you can model anything from a 5-second queue time to a 7-day hospital stay. No static tables. No reloads. Just dynamic, purposeful banding—ready for real-time reporting.
