SQL Server – Generate and Use Time / Duration Dimension

Recently, I was working on a backups dashboard in PowerBI to do some analytics on the health of our database backups across the board. We set out to understand some basic things about our backups

  • Size
  • Duration
  • Lag (i.e, how long has it been since the last backup)

To give you a sense of what I am talking about, the backups dashboard looks like this (saving more on that for another day of blogging).

backupsdashboard
Backups Dashboard

If you notice, “Duration” is one of the things that is being shown in the middle. I define duration for the purposes of this dashboard as the time taken in seconds/minutes/hours/days etc with the ability to slice and dice by those attributes. Below, you can see the portion that pertains to just the “Duration”. X-axis shows the count of backups and the Y-axis the duration. The color is an indicator of how many fall in the bucket (darker the color, higher the count in that bucket) which draws the analyst’s attention to the areas of interest.

duration
Backup Counts by Duration

Now, I can click on the ones that take “15 Hours to 20 Hours” and find out if I can improve the duration by doing something.

Getting back to the crux of this post, I needed a “Duration” dimension table and had to generate the data for it to power the dashboard’s slice and dice on that dimension.

durationdimensiontable

The data is in increments of 300 seconds going from 0 to 31536000 seconds (1 year). So, this table can be used to analyze activities that take less than 1 year. The last row’s Dimension value should be used for everything that takes over one year (or you can generate more rows based on your need).

The table is very wide but I will try to give you a sense of the data in the table

DurationKey          BeginSeconds         EndSeconds           Seconds              Minutes              Hours                Days                 Weeks                Duration                                                                                                                         BeginSecondsSmallRange EndSecondsSmallRange BeginSecondsMediumRange EndSecondsMediumRange BeginSecondsLargeRange EndSecondsLargeRange BeginSecondsHugeRange EndSecondsHugeRange  BeginMinutesSmallRange EndMinutesSmallRange BeginMinutesMediumRange EndMinutesMediumRange BeginMinutesLargeRange EndMinutesLargeRange BeginMinutesHugeRange EndMinutesHugeRange  BeginHoursSmallRange EndHoursSmallRange   BeginHoursMediumRange EndHoursMediumRange  BeginHoursLargeRange EndHoursLargeRange   BeginHoursHugeRange  EndHoursHugeRange    BeginDaysSmallRange  EndDaysSmallRange    BeginDaysMediumRange EndDaysMediumRange   BeginDaysLargeRange  EndDaysLargeRange    BeginDaysHugeRange   EndDaysHugeRange     BeginWeeksSmallRange EndWeeksSmallRange   BeginWeeksMediumRange EndWeeksMediumRange  BeginWeeksLargeRange EndWeeksLargeRange   BeginWeeksHugeRange  EndWeeksHugeRange    SecondsRange                                       SecondsSmallRange                                  SecondsMediumRange                                 SecondsLargeRange                                  SecondsHugeRange                                   MinutesSmallRange                                  MinutesMediumRange                                 MinutesLargeRange                                  MinutesHugeRange                                   HoursSmallRange                                    HoursMediumRange                                   HoursLargeRange                                    HoursHugeRange                                     DaysSmallRange                                     DaysMediumRange                                    DaysLargeRange                                     DaysHugeRange                                      WeeksSmallRange                                    WeeksMediumRange                                   WeeksLargeRange                                    WeeksHugeRange
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------------- ----------------------- --------------------- ---------------------- -------------------- --------------------- -------------------- ---------------------- -------------------- ----------------------- --------------------- ---------------------- -------------------- --------------------- -------------------- -------------------- -------------------- --------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
0                    -300                 0                    0                    0                    0                    0                    0                    0 Days, 0 Hrs, 0 Mins, 0 Secs                                                                                                    0                      300                  0                       900                   0                      2700                 0                     3600                 0                      10                   0                       30                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]
1                    0                    300                  300                  5                    0                    0                    0                    0 Days, 0 Hrs, 5 Mins, 0 Secs                                                                                                    300                    600                  0                       900                   0                      2700                 0                     3600                 0                      10                   0                       30                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    0 Seconds to 300 Seconds                           300 Seconds to 600 Seconds                         0 Seconds to 900 Seconds                           0 Seconds to 2700 Seconds                          0 Seconds to 3600 Seconds                          0 Minutes to 10 Minutes                            0 Minutes to 30 Minutes                            0 Minutes to 60 Minutes                            0 Minutes to 120 Minutes                           0 Hours to 1 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
2                    300                  600                  600                  10                   0                    0                    0                    0 Days, 0 Hrs, 10 Mins, 0 Secs                                                                                                   600                    900                  0                       900                   0                      2700                 0                     3600                 10                     20                   0                       30                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    300 Seconds to 600 Seconds                         600 Seconds to 900 Seconds                         0 Seconds to 900 Seconds                           0 Seconds to 2700 Seconds                          0 Seconds to 3600 Seconds                          10 Minutes to 20 Minutes                           0 Minutes to 30 Minutes                            0 Minutes to 60 Minutes                            0 Minutes to 120 Minutes                           0 Hours to 1 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
3                    600                  900                  900                  15                   0                    0                    0                    0 Days, 0 Hrs, 15 Mins, 0 Secs                                                                                                   900                    1200                 900                     1800                  0                      2700                 0                     3600                 10                     20                   0                       30                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    600 Seconds to 900 Seconds                         900 Seconds to 1200 Seconds                        900 Seconds to 1800 Seconds                        0 Seconds to 2700 Seconds                          0 Seconds to 3600 Seconds                          10 Minutes to 20 Minutes                           0 Minutes to 30 Minutes                            0 Minutes to 60 Minutes                            0 Minutes to 120 Minutes                           0 Hours to 1 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
4                    900                  1200                 1200                 20                   0                    0                    0                    0 Days, 0 Hrs, 20 Mins, 0 Secs                                                                                                   1200                   1500                 900                     1800                  0                      2700                 0                     3600                 20                     30                   0                       30                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    900 Seconds to 1200 Seconds                        1200 Seconds to 1500 Seconds                       900 Seconds to 1800 Seconds                        0 Seconds to 2700 Seconds                          0 Seconds to 3600 Seconds                          20 Minutes to 30 Minutes                           0 Minutes to 30 Minutes                            0 Minutes to 60 Minutes                            0 Minutes to 120 Minutes                           0 Hours to 1 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
5                    1200                 1500                 1500                 25                   0                    0                    0                    0 Days, 0 Hrs, 25 Mins, 0 Secs                                                                                                   1500                   1800                 900                     1800                  0                      2700                 0                     3600                 20                     30                   0                       30                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    1200 Seconds to 1500 Seconds                       1500 Seconds to 1800 Seconds                       900 Seconds to 1800 Seconds                        0 Seconds to 2700 Seconds                          0 Seconds to 3600 Seconds                          20 Minutes to 30 Minutes                           0 Minutes to 30 Minutes                            0 Minutes to 60 Minutes                            0 Minutes to 120 Minutes                           0 Hours to 1 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
6                    1500                 1800                 1800                 30                   0                    0                    0                    0 Days, 0 Hrs, 30 Mins, 0 Secs                                                                                                   1800                   2100                 1800                    2700                  0                      2700                 0                     3600                 30                     40                   30                      60                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    1500 Seconds to 1800 Seconds                       1800 Seconds to 2100 Seconds                       1800 Seconds to 2700 Seconds                       0 Seconds to 2700 Seconds                          0 Seconds to 3600 Seconds                          30 Minutes to 40 Minutes                           30 Minutes to 60 Minutes                           0 Minutes to 60 Minutes                            0 Minutes to 120 Minutes                           0 Hours to 1 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
7                    1800                 2100                 2100                 35                   0                    0                    0                    0 Days, 0 Hrs, 35 Mins, 0 Secs                                                                                                   2100                   2400                 1800                    2700                  0                      2700                 0                     3600                 30                     40                   30                      60                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    1800 Seconds to 2100 Seconds                       2100 Seconds to 2400 Seconds                       1800 Seconds to 2700 Seconds                       0 Seconds to 2700 Seconds                          0 Seconds to 3600 Seconds                          30 Minutes to 40 Minutes                           30 Minutes to 60 Minutes                           0 Minutes to 60 Minutes                            0 Minutes to 120 Minutes                           0 Hours to 1 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
8                    2100                 2400                 2400                 40                   0                    0                    0                    0 Days, 0 Hrs, 40 Mins, 0 Secs                                                                                                   2400                   2700                 1800                    2700                  0                      2700                 0                     3600                 40                     50                   30                      60                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    2100 Seconds to 2400 Seconds                       2400 Seconds to 2700 Seconds                       1800 Seconds to 2700 Seconds                       0 Seconds to 2700 Seconds                          0 Seconds to 3600 Seconds                          40 Minutes to 50 Minutes                           30 Minutes to 60 Minutes                           0 Minutes to 60 Minutes                            0 Minutes to 120 Minutes                           0 Hours to 1 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
9                    2400                 2700                 2700                 45                   0                    0                    0                    0 Days, 0 Hrs, 45 Mins, 0 Secs                                                                                                   2700                   3000                 2700                    3600                  2700                   5400                 0                     3600                 40                     50                   30                      60                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    2400 Seconds to 2700 Seconds                       2700 Seconds to 3000 Seconds                       2700 Seconds to 3600 Seconds                       2700 Seconds to 5400 Seconds                       0 Seconds to 3600 Seconds                          40 Minutes to 50 Minutes                           30 Minutes to 60 Minutes                           0 Minutes to 60 Minutes                            0 Minutes to 120 Minutes                           0 Hours to 1 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
10                   2700                 3000                 3000                 50                   0                    0                    0                    0 Days, 0 Hrs, 50 Mins, 0 Secs                                                                                                   3000                   3300                 2700                    3600                  2700                   5400                 0                     3600                 50                     60                   30                      60                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    2700 Seconds to 3000 Seconds                       3000 Seconds to 3300 Seconds                       2700 Seconds to 3600 Seconds                       2700 Seconds to 5400 Seconds                       0 Seconds to 3600 Seconds                          50 Minutes to 60 Minutes                           30 Minutes to 60 Minutes                           0 Minutes to 60 Minutes                            0 Minutes to 120 Minutes                           0 Hours to 1 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
11                   3000                 3300                 3300                 55                   0                    0                    0                    0 Days, 0 Hrs, 55 Mins, 0 Secs                                                                                                   3300                   3600                 2700                    3600                  2700                   5400                 0                     3600                 50                     60                   30                      60                    0                      60                   0                     120                  0                    1                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    3000 Seconds to 3300 Seconds                       3300 Seconds to 3600 Seconds                       2700 Seconds to 3600 Seconds                       2700 Seconds to 5400 Seconds                       0 Seconds to 3600 Seconds                          50 Minutes to 60 Minutes                           30 Minutes to 60 Minutes                           0 Minutes to 60 Minutes                            0 Minutes to 120 Minutes                           0 Hours to 1 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
12                   3300                 3600                 3600                 60                   1                    0                    0                    0 Days, 1 Hrs, 0 Mins, 0 Secs                                                                                                    3600                   3900                 3600                    4500                  2700                   5400                 3600                  7200                 60                     70                   60                      90                    60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    3300 Seconds to 3600 Seconds                       3600 Seconds to 3900 Seconds                       3600 Seconds to 4500 Seconds                       2700 Seconds to 5400 Seconds                       3600 Seconds to 7200 Seconds                       60 Minutes to 70 Minutes                           60 Minutes to 90 Minutes                           60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
13                   3600                 3900                 3900                 65                   1                    0                    0                    0 Days, 1 Hrs, 5 Mins, 0 Secs                                                                                                    3900                   4200                 3600                    4500                  2700                   5400                 3600                  7200                 60                     70                   60                      90                    60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    3600 Seconds to 3900 Seconds                       3900 Seconds to 4200 Seconds                       3600 Seconds to 4500 Seconds                       2700 Seconds to 5400 Seconds                       3600 Seconds to 7200 Seconds                       60 Minutes to 70 Minutes                           60 Minutes to 90 Minutes                           60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
14                   3900                 4200                 4200                 70                   1                    0                    0                    0 Days, 1 Hrs, 10 Mins, 0 Secs                                                                                                   4200                   4500                 3600                    4500                  2700                   5400                 3600                  7200                 70                     80                   60                      90                    60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    3900 Seconds to 4200 Seconds                       4200 Seconds to 4500 Seconds                       3600 Seconds to 4500 Seconds                       2700 Seconds to 5400 Seconds                       3600 Seconds to 7200 Seconds                       70 Minutes to 80 Minutes                           60 Minutes to 90 Minutes                           60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
15                   4200                 4500                 4500                 75                   1                    0                    0                    0 Days, 1 Hrs, 15 Mins, 0 Secs                                                                                                   4500                   4800                 4500                    5400                  2700                   5400                 3600                  7200                 70                     80                   60                      90                    60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    4200 Seconds to 4500 Seconds                       4500 Seconds to 4800 Seconds                       4500 Seconds to 5400 Seconds                       2700 Seconds to 5400 Seconds                       3600 Seconds to 7200 Seconds                       70 Minutes to 80 Minutes                           60 Minutes to 90 Minutes                           60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
16                   4500                 4800                 4800                 80                   1                    0                    0                    0 Days, 1 Hrs, 20 Mins, 0 Secs                                                                                                   4800                   5100                 4500                    5400                  2700                   5400                 3600                  7200                 80                     90                   60                      90                    60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    4500 Seconds to 4800 Seconds                       4800 Seconds to 5100 Seconds                       4500 Seconds to 5400 Seconds                       2700 Seconds to 5400 Seconds                       3600 Seconds to 7200 Seconds                       80 Minutes to 90 Minutes                           60 Minutes to 90 Minutes                           60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
17                   4800                 5100                 5100                 85                   1                    0                    0                    0 Days, 1 Hrs, 25 Mins, 0 Secs                                                                                                   5100                   5400                 4500                    5400                  2700                   5400                 3600                  7200                 80                     90                   60                      90                    60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    4800 Seconds to 5100 Seconds                       5100 Seconds to 5400 Seconds                       4500 Seconds to 5400 Seconds                       2700 Seconds to 5400 Seconds                       3600 Seconds to 7200 Seconds                       80 Minutes to 90 Minutes                           60 Minutes to 90 Minutes                           60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
18                   5100                 5400                 5400                 90                   1                    0                    0                    0 Days, 1 Hrs, 30 Mins, 0 Secs                                                                                                   5400                   5700                 5400                    6300                  5400                   8100                 3600                  7200                 90                     100                  90                      120                   60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    5100 Seconds to 5400 Seconds                       5400 Seconds to 5700 Seconds                       5400 Seconds to 6300 Seconds                       5400 Seconds to 8100 Seconds                       3600 Seconds to 7200 Seconds                       90 Minutes to 100 Minutes                          90 Minutes to 120 Minutes                          60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
19                   5400                 5700                 5700                 95                   1                    0                    0                    0 Days, 1 Hrs, 35 Mins, 0 Secs                                                                                                   5700                   6000                 5400                    6300                  5400                   8100                 3600                  7200                 90                     100                  90                      120                   60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    5400 Seconds to 5700 Seconds                       5700 Seconds to 6000 Seconds                       5400 Seconds to 6300 Seconds                       5400 Seconds to 8100 Seconds                       3600 Seconds to 7200 Seconds                       90 Minutes to 100 Minutes                          90 Minutes to 120 Minutes                          60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
20                   5700                 6000                 6000                 100                  1                    0                    0                    0 Days, 1 Hrs, 40 Mins, 0 Secs                                                                                                   6000                   6300                 5400                    6300                  5400                   8100                 3600                  7200                 100                    110                  90                      120                   60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    5700 Seconds to 6000 Seconds                       6000 Seconds to 6300 Seconds                       5400 Seconds to 6300 Seconds                       5400 Seconds to 8100 Seconds                       3600 Seconds to 7200 Seconds                       100 Minutes to 110 Minutes                         90 Minutes to 120 Minutes                          60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
21                   6000                 6300                 6300                 105                  1                    0                    0                    0 Days, 1 Hrs, 45 Mins, 0 Secs                                                                                                   6300                   6600                 6300                    7200                  5400                   8100                 3600                  7200                 100                    110                  90                      120                   60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    6000 Seconds to 6300 Seconds                       6300 Seconds to 6600 Seconds                       6300 Seconds to 7200 Seconds                       5400 Seconds to 8100 Seconds                       3600 Seconds to 7200 Seconds                       100 Minutes to 110 Minutes                         90 Minutes to 120 Minutes                          60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
22                   6300                 6600                 6600                 110                  1                    0                    0                    0 Days, 1 Hrs, 50 Mins, 0 Secs                                                                                                   6600                   6900                 6300                    7200                  5400                   8100                 3600                  7200                 110                    120                  90                      120                   60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    6300 Seconds to 6600 Seconds                       6600 Seconds to 6900 Seconds                       6300 Seconds to 7200 Seconds                       5400 Seconds to 8100 Seconds                       3600 Seconds to 7200 Seconds                       110 Minutes to 120 Minutes                         90 Minutes to 120 Minutes                          60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
23                   6600                 6900                 6900                 115                  1                    0                    0                    0 Days, 1 Hrs, 55 Mins, 0 Secs                                                                                                   6900                   7200                 6300                    7200                  5400                   8100                 3600                  7200                 110                    120                  90                      120                   60                     120                  0                     120                  1                    2                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    6600 Seconds to 6900 Seconds                       6900 Seconds to 7200 Seconds                       6300 Seconds to 7200 Seconds                       5400 Seconds to 8100 Seconds                       3600 Seconds to 7200 Seconds                       110 Minutes to 120 Minutes                         90 Minutes to 120 Minutes                          60 Minutes to 120 Minutes                          0 Minutes to 120 Minutes                           1 Hours to 2 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
24                   6900                 7200                 7200                 120                  2                    0                    0                    0 Days, 2 Hrs, 0 Mins, 0 Secs                                                                                                    7200                   7500                 7200                    8100                  5400                   8100                 7200                  10800                120                    130                  120                     150                   120                    180                  120                   240                  2                    3                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    6900 Seconds to 7200 Seconds                       7200 Seconds to 7500 Seconds                       7200 Seconds to 8100 Seconds                       5400 Seconds to 8100 Seconds                       7200 Seconds to 10800 Seconds                      120 Minutes to 130 Minutes                         120 Minutes to 150 Minutes                         120 Minutes to 180 Minutes                         120 Minutes to 240 Minutes                         2 Hours to 3 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks
25                   7200                 7500                 7500                 125                  2                    0                    0                    0 Days, 2 Hrs, 5 Mins, 0 Secs                                                                                                    7500                   7800                 7200                    8100                  5400                   8100                 7200                  10800                120                    130                  120                     150                   120                    180                  120                   240                  2                    3                    0                     5                    0                    10                   0                    24                   0                    1                    0                    3                    0                    5                    0                    10                   0                    1                    0                     2                    0                    4                    0                    8                    7200 Seconds to 7500 Seconds                       7500 Seconds to 7800 Seconds                       7200 Seconds to 8100 Seconds                       5400 Seconds to 8100 Seconds                       7200 Seconds to 10800 Seconds                      120 Minutes to 130 Minutes                         120 Minutes to 150 Minutes                         120 Minutes to 180 Minutes                         120 Minutes to 240 Minutes                         2 Hours to 3 Hours                                 0 Hours to 5 Hours                                 0 Hours to 10 Hours                                0 Hours to 24 Hours                                0 Days to 1 Days                                   0 Days to 3 Days                                   0 Days to 5 Days                                   0 Days to 10 Days                                  0 Weeks to 1 Weeks                                 0 Weeks to 2 Weeks                                 0 Weeks to 4 Weeks                                 0 Weeks to 8 Weeks

Since it is a wide table, it is hard to visualize the contents of the table on a web page. I have included the first three rows in a transposed fashion to give you a better idea of the data.

DurationKey 0 1 2
BeginSeconds -300 0 300
EndSeconds 0 300 600
Seconds 0 300 600
Minutes 0 5 10
Hours 0 0 0
Days 0 0 0
Weeks 0 0 0
Duration 0 Days, 0 Hrs, 0 Mins, 0 Secs 0 Days, 0 Hrs, 5 Mins, 0 Secs 0 Days, 0 Hrs, 10 Mins, 0 Secs
BeginSecondsSmallRange 0 300 600
EndSecondsSmallRange 300 600 900
BeginSecondsMediumRange 0 0 0
EndSecondsMediumRange 900 900 900
BeginSecondsLargeRange 0 0 0
EndSecondsLargeRange 2700 2700 2700
BeginSecondsHugeRange 0 0 0
EndSecondsHugeRange 3600 3600 3600
BeginMinutesSmallRange 0 0 10
EndMinutesSmallRange 10 10 20
BeginMinutesMediumRange 0 0 0
EndMinutesMediumRange 30 30 30
BeginMinutesLargeRange 0 0 0
EndMinutesLargeRange 60 60 60
BeginMinutesHugeRange 0 0 0
EndMinutesHugeRange 120 120 120
BeginHoursSmallRange 0 0 0
EndHoursSmallRange 1 1 1
BeginHoursMediumRange 0 0 0
EndHoursMediumRange 5 5 5
BeginHoursLargeRange 0 0 0
EndHoursLargeRange 10 10 10
BeginHoursHugeRange 0 0 0
EndHoursHugeRange 24 24 24
BeginDaysSmallRange 0 0 0
EndDaysSmallRange 1 1 1
BeginDaysMediumRange 0 0 0
EndDaysMediumRange 3 3 3
BeginDaysLargeRange 0 0 0
EndDaysLargeRange 5 5 5
BeginDaysHugeRange 0 0 0
EndDaysHugeRange 10 10 10
BeginWeeksSmallRange 0 0 0
EndWeeksSmallRange 1 1 1
BeginWeeksMediumRange 0 0 0
EndWeeksMediumRange 2 2 2
BeginWeeksLargeRange 0 0 0
EndWeeksLargeRange 4 4 4
BeginWeeksHugeRange 0 0 0
EndWeeksHugeRange 8 8 8
SecondsRange [N/A] 0 Seconds to 300 Seconds 300 Seconds to 600 Seconds
SecondsSmallRange [N/A] 300 Seconds to 600 Seconds 600 Seconds to 900 Seconds
SecondsMediumRange [N/A] 0 Seconds to 900 Seconds 0 Seconds to 900 Seconds
SecondsLargeRange [N/A] 0 Seconds to 2700 Seconds 0 Seconds to 2700 Seconds
SecondsHugeRange [N/A] 0 Seconds to 3600 Seconds 0 Seconds to 3600 Seconds
MinutesSmallRange [N/A] 0 Minutes to 10 Minutes 10 Minutes to 20 Minutes
MinutesMediumRange [N/A] 0 Minutes to 30 Minutes 0 Minutes to 30 Minutes
MinutesLargeRange [N/A] 0 Minutes to 60 Minutes 0 Minutes to 60 Minutes
MinutesHugeRange [N/A] 0 Minutes to 120 Minutes 0 Minutes to 120 Minutes
HoursSmallRange [N/A] 0 Hours to 1 Hours 0 Hours to 1 Hours
HoursMediumRange [N/A] 0 Hours to 5 Hours 0 Hours to 5 Hours
HoursLargeRange [N/A] 0 Hours to 10 Hours 0 Hours to 10 Hours
HoursHugeRange [N/A] 0 Hours to 24 Hours 0 Hours to 24 Hours
DaysSmallRange [N/A] 0 Days to 1 Days 0 Days to 1 Days
DaysMediumRange [N/A] 0 Days to 3 Days 0 Days to 3 Days
DaysLargeRange [N/A] 0 Days to 5 Days 0 Days to 5 Days
DaysHugeRange [N/A] 0 Days to 10 Days 0 Days to 10 Days
WeeksSmallRange [N/A] 0 Weeks to 1 Weeks 0 Weeks to 1 Weeks
WeeksMediumRange [N/A] 0 Weeks to 2 Weeks 0 Weeks to 2 Weeks
WeeksLargeRange [N/A] 0 Weeks to 4 Weeks 0 Weeks to 4 Weeks
WeeksHugeRange [N/A] 0 Weeks to 8 Weeks 0 Weeks to 8 Weeks

Below is the complete code that I used to create the Dimension table which I called Dim.DurationDimension and generate the rows that go into the table in SQL Server.


------------------------------------------------------------------------------------------------
--Version History
--    v1.0 - 20161011 - Created by Jana Sattainathan | Twitter @SQLJana | WordPress: SQLJana.WordPress.com
------------------------------------------------------------------------------------------------

DECLARE @StartSeconds INT = 0,
		@IncrementSeconds INT = 300,
		@MaxSeconds INT = 31536000; /* 365 days */

DECLARE @RangeSecondsSmall INT = 300,
		@RangeSecondsMedium INT = 900,
		@RangeSecondsLarge INT = 2700,
		@RangeSecondsHuge INT = 3600,
		@RangeMinutesSmall INT = 10,
		@RangeMinutesMedium INT = 30,
		@RangeMinutesLarge INT = 60,
		@RangeMinutesHuge INT = 120,
		@RangeHoursSmall INT = 1,
		@RangeHoursMedium INT = 5,
		@RangeHoursLarge INT = 10,
		@RangeHoursHuge INT = 24,
		@RangeDaysSmall INT = 1,
		@RangeDaysMedium INT = 3,
		@RangeDaysLarge INT = 5,
		@RangeDaysHuge INT = 10,
		@RangeWeeksSmall INT = 1,
		@RangeWeeksMedium INT = 2,
		@RangeWeeksLarge INT = 4,
		@RangeWeeksHuge INT = 8;

DECLARE @Increments INT = @MaxSeconds/@IncrementSeconds;

IF EXISTS (
	SELECT *
	FROM sys.objects so
	WHERE SCHEMA_NAME(so.schema_id) = 'Dim' AND so.name = 'DurationDimension'
)
	DROP TABLE Dim.DurationDimension;

CREATE TABLE [Dim].[DurationDimension](
	[DurationKey] [bigint] NOT NULL PRIMARY KEY,
	[BeginSeconds] [bigint] NOT NULL,
	[EndSeconds] [bigint] NOT NULL,
	[Seconds] [bigint] NOT NULL,
	[Minutes] [bigint] NOT NULL,
	[Hours] [bigint] NOT NULL,
	[Days] [bigint] NOT NULL,
	[Weeks] [bigint] NOT NULL,
	Duration varchar(128) NOT NULL,
	[BeginSecondsSmallRange] [bigint] NOT NULL,
	[EndSecondsSmallRange] [bigint] NOT NULL,
	[BeginSecondsMediumRange] [bigint] NOT NULL,
	[EndSecondsMediumRange] [bigint] NOT NULL,
	[BeginSecondsLargeRange] [bigint] NOT NULL,
	[EndSecondsLargeRange] [bigint] NOT NULL,
	[BeginSecondsHugeRange] [bigint] NOT NULL,
	[EndSecondsHugeRange] [bigint] NOT NULL,
	[BeginMinutesSmallRange] [bigint] NOT NULL,
	[EndMinutesSmallRange] [bigint] NOT NULL,
	[BeginMinutesMediumRange] [bigint] NOT NULL,
	[EndMinutesMediumRange] [bigint] NOT NULL,
	[BeginMinutesLargeRange] [bigint] NOT NULL,
	[EndMinutesLargeRange] [bigint] NOT NULL,
	[BeginMinutesHugeRange] [bigint] NOT NULL,
	[EndMinutesHugeRange] [bigint] NOT NULL,
	[BeginHoursSmallRange] [bigint] NOT NULL,
	[EndHoursSmallRange] [bigint] NOT NULL,
	[BeginHoursMediumRange] [bigint] NOT NULL,
	[EndHoursMediumRange] [bigint] NOT NULL,
	[BeginHoursLargeRange] [bigint] NOT NULL,
	[EndHoursLargeRange] [bigint] NOT NULL,
	[BeginHoursHugeRange] [bigint] NOT NULL,
	[EndHoursHugeRange] [bigint] NOT NULL,
	[BeginDaysSmallRange] [bigint] NOT NULL,
	[EndDaysSmallRange] [bigint] NOT NULL,
	[BeginDaysMediumRange] [bigint] NOT NULL,
	[EndDaysMediumRange] [bigint] NOT NULL,
	[BeginDaysLargeRange] [bigint] NOT NULL,
	[EndDaysLargeRange] [bigint] NOT NULL,
	[BeginDaysHugeRange] [bigint] NOT NULL,
	[EndDaysHugeRange] [bigint] NOT NULL,
	[BeginWeeksSmallRange] [bigint] NOT NULL,
	[EndWeeksSmallRange] [bigint] NOT NULL,
	[BeginWeeksMediumRange] [bigint] NOT NULL,
	[EndWeeksMediumRange] [bigint] NOT NULL,
	[BeginWeeksLargeRange] [bigint] NOT NULL,
	[EndWeeksLargeRange] [bigint] NOT NULL,
	[BeginWeeksHugeRange] [bigint] NOT NULL,
	[EndWeeksHugeRange] [bigint] NOT NULL,
	[SecondsRange] [varchar](50) NOT NULL,
	[SecondsSmallRange] [varchar](50) NOT NULL,
	[SecondsMediumRange] [varchar](50) NOT NULL,
	[SecondsLargeRange] [varchar](50) NOT NULL,
	[SecondsHugeRange] [varchar](50) NOT NULL,
	[MinutesSmallRange] [varchar](50) NOT NULL,
	[MinutesMediumRange] [varchar](50) NOT NULL,
	[MinutesLargeRange] [varchar](50) NOT NULL,
	[MinutesHugeRange] [varchar](50) NOT NULL,
	[HoursSmallRange] [varchar](50) NOT NULL,
	[HoursMediumRange] [varchar](50) NOT NULL,
	[HoursLargeRange] [varchar](50) NOT NULL,
	[HoursHugeRange] [varchar](50) NOT NULL,
	[DaysSmallRange] [varchar](50) NOT NULL,
	[DaysMediumRange] [varchar](50) NOT NULL,
	[DaysLargeRange] [varchar](50) NOT NULL,
	[DaysHugeRange] [varchar](50) NOT NULL,
	[WeeksSmallRange] [varchar](50) NOT NULL,
	[WeeksMediumRange] [varchar](50) NOT NULL,
	[WeeksLargeRange] [varchar](50) NOT NULL,
	[WeeksHugeRange] [varchar](50) NOT NULL
) ON [PRIMARY];

--Generate the ranges and populate the dimension table
WITH keys
AS
(
	SELECT
		  TOP (@Increments+1)
		  (ROW_NUMBER() OVER (ORDER BY o1.[object_id])) - 1 AS DurationKey
	-------------
	--IMPORTANT: Your database needs enough objects (in sys.objects) to be able to generate millions of rows..
	--				...and hence the 3 CROSS JOINS to sys.objects
	-------------
	FROM (SELECT [object_id] FROM sys.objects) AS o1
		CROSS JOIN (SELECT [object_id] FROM sys.objects) AS o2
		CROSS JOIN (SELECT [object_id] FROM sys.objects) AS o3
	ORDER BY o1.[object_id]
)
INSERT INTO
	Dim.DurationDimension
SELECT
	rangeData.*,
	SecondsRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginSeconds AS NVARCHAR(15))) + ' Seconds to ' + LTRIM(CAST(EndSeconds AS NVARCHAR(15))) + ' Seconds'
				END,
	SecondsSmallRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginSecondsSmallRange AS NVARCHAR(15))) + ' Seconds to ' + LTRIM(CAST(EndSecondsSmallRange AS NVARCHAR(15))) + ' Seconds'
				END,
	SecondsMediumRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginSecondsMediumRange AS NVARCHAR(15))) + ' Seconds to ' + LTRIM(CAST(EndSecondsMediumRange AS NVARCHAR(15))) + ' Seconds'
				END,
	SecondsLargeRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginSecondsLargeRange AS NVARCHAR(15))) + ' Seconds to ' + LTRIM(CAST(EndSecondsLargeRange AS NVARCHAR(15))) + ' Seconds'
				END,
	SecondsHugeRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginSecondsHugeRange AS NVARCHAR(15))) + ' Seconds to ' + LTRIM(CAST(EndSecondsHugeRange AS NVARCHAR(15))) + ' Seconds'
				END,
	MinutesSmallRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginMinutesSmallRange AS NVARCHAR(15))) + ' Minutes to ' + LTRIM(CAST(EndMinutesSmallRange AS NVARCHAR(15))) + ' Minutes'
				END,
	MinutesMediumRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginMinutesMediumRange AS NVARCHAR(15))) + ' Minutes to ' + LTRIM(CAST(EndMinutesMediumRange AS NVARCHAR(15))) + ' Minutes'
				END,
	MinutesLargeRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginMinutesLargeRange AS NVARCHAR(15))) + ' Minutes to ' + LTRIM(CAST(EndMinutesLargeRange AS NVARCHAR(15))) + ' Minutes'
				END,
	MinutesHugeRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginMinutesHugeRange AS NVARCHAR(15))) + ' Minutes to ' + LTRIM(CAST(EndMinutesHugeRange AS NVARCHAR(15))) + ' Minutes'
				END,
	HoursSmallRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginHoursSmallRange AS NVARCHAR(15))) + ' Hours to ' + LTRIM(CAST(EndHoursSmallRange AS NVARCHAR(15))) + ' Hours'
				END,
	HoursMediumRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginHoursMediumRange AS NVARCHAR(15))) + ' Hours to ' + LTRIM(CAST(EndHoursMediumRange AS NVARCHAR(15))) + ' Hours'
				END,
	HoursLargeRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginHoursLargeRange AS NVARCHAR(15))) + ' Hours to ' + LTRIM(CAST(EndHoursLargeRange AS NVARCHAR(15))) + ' Hours'
				END,
	HoursHugeRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginHoursHugeRange AS NVARCHAR(15))) + ' Hours to ' + LTRIM(CAST(EndHoursHugeRange AS NVARCHAR(15))) + ' Hours'
				END,
	DaysSmallRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginDaysSmallRange AS NVARCHAR(15))) + ' Days to ' + LTRIM(CAST(EndDaysSmallRange AS NVARCHAR(15))) + ' Days'
				END,
	DaysMediumRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginDaysMediumRange AS NVARCHAR(15))) + ' Days to ' + LTRIM(CAST(EndDaysMediumRange AS NVARCHAR(15))) + ' Days'
				END,
	DaysLargeRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginDaysLargeRange AS NVARCHAR(15))) + ' Days to ' + LTRIM(CAST(EndDaysLargeRange AS NVARCHAR(15))) + ' Days'
				END,
	DaysHugeRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginDaysHugeRange AS NVARCHAR(15))) + ' Days to ' + LTRIM(CAST(EndDaysHugeRange AS NVARCHAR(15))) + ' Days'
				END,
	WeeksSmallRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginWeeksSmallRange AS NVARCHAR(15))) + ' Weeks to ' + LTRIM(CAST(EndWeeksSmallRange AS NVARCHAR(15))) + ' Weeks'
				END,
	WeeksMediumRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginWeeksMediumRange AS NVARCHAR(15))) + ' Weeks to ' + LTRIM(CAST(EndWeeksMediumRange AS NVARCHAR(15))) + ' Weeks'
				END,
	WeeksLargeRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginWeeksLargeRange AS NVARCHAR(15))) + ' Weeks to ' + LTRIM(CAST(EndWeeksLargeRange AS NVARCHAR(15))) + ' Weeks'
				END,
	WeeksHugeRange = CASE WHEN DurationKey <=0 THEN '[N/A]'  					WHEN DurationKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginWeeksHugeRange AS NVARCHAR(15))) + ' Weeks to ' + LTRIM(CAST(EndWeeksHugeRange AS NVARCHAR(15))) + ' Weeks'
				END
FROM
(
	SELECT
		keyData.*,
		--BeginMinutes = (@IncrementSeconds * DurationKey) - (@IncrementSeconds),
		--EndMinutes = (@IncrementSeconds * DurationKey),
		Duration = CONVERT(VARCHAR(12), Seconds /60/60/24)   + ' Days, '
					  + CONVERT(VARCHAR(12), Seconds /60/60 % 24) + ' Hrs, '
					  + CONVERT(VARCHAR(2),  Seconds /60 % 60)    + ' Mins, '
					  + CONVERT(VARCHAR(2),  Seconds % 60)        + ' Secs',
		------------------------------------------------------
		BeginSecondsSmallRange = Seconds - (Seconds  % @RangeSecondsSmall),
		EndSecondsSmallRange = (Seconds + @RangeSecondsSmall) - (Seconds  % @RangeSecondsSmall),
		BeginSecondsMediumRange = Seconds - (Seconds  % @RangeSecondsMedium),
		EndSecondsMediumRange = (Seconds + @RangeSecondsMedium) - (Seconds  % @RangeSecondsMedium),
		BeginSecondsLargeRange = Seconds - (Seconds  % @RangeSecondsLarge),
		EndSecondsLargeRange = (Seconds + @RangeSecondsLarge) - (Seconds  % @RangeSecondsLarge),
		BeginSecondsHugeRange = Seconds - (Seconds  % @RangeSecondsHuge),
		EndSecondsHugeRange = (Seconds + @RangeSecondsHuge) - (Seconds  % @RangeSecondsHuge),
		------------------------------------------------------
		BeginMinutesSmallRange = Minutes - (Minutes  % @RangeMinutesSmall),
		EndMinutesSmallRange = (Minutes + @RangeMinutesSmall) - (Minutes  % @RangeMinutesSmall),
		BeginMinutesMediumRange = Minutes - (Minutes  % @RangeMinutesMedium),
		EndMinutesMediumRange = (Minutes + @RangeMinutesMedium) - (Minutes  % @RangeMinutesMedium),
		BeginMinutesLargeRange = Minutes - (Minutes  % @RangeMinutesLarge),
		EndMinutesLargeRange = (Minutes + @RangeMinutesLarge) - (Minutes  % @RangeMinutesLarge),
		BeginMinutesHugeRange = Minutes - (Minutes  % @RangeMinutesHuge),
		EndMinutesHugeRange = (Minutes + @RangeMinutesHuge) - (Minutes  % @RangeMinutesHuge),
		------------------------------------------------------
		BeginHoursSmallRange = Hours - (Hours  % @RangeHoursSmall),
		EndHoursSmallRange = (Hours + @RangeHoursSmall) - (Hours  % @RangeHoursSmall),
		BeginHoursMediumRange = Hours - (Hours  % @RangeHoursMedium),
		EndHoursMediumRange = (Hours + @RangeHoursMedium) - (Hours  % @RangeHoursMedium),
		BeginHoursLargeRange = Hours - (Hours  % @RangeHoursLarge),
		EndHoursLargeRange = (Hours + @RangeHoursLarge) - (Hours  % @RangeHoursLarge),
		BeginHoursHugeRange = Hours - (Hours  % @RangeHoursHuge),
		EndHoursHugeRange = (Hours + @RangeHoursHuge) - (Hours  % @RangeHoursHuge),
		------------------------------------------------------
		BeginDaysSmallRange = Days - (Days  % @RangeDaysSmall),
		EndDaysSmallRange = (Days + @RangeDaysSmall) - (Days  % @RangeDaysSmall),
		BeginDaysMediumRange = Days - (Days  % @RangeDaysMedium),
		EndDaysMediumRange = (Days + @RangeDaysMedium) - (Days  % @RangeDaysMedium),
		BeginDaysLargeRange = Days - (Days  % @RangeDaysLarge),
		EndDaysLargeRange = (Days + @RangeDaysLarge) - (Days  % @RangeDaysLarge),
		BeginDaysHugeRange = Days - (Days  % @RangeDaysHuge),
		EndDaysHugeRange = (Days + @RangeDaysHuge) - (Days  % @RangeDaysHuge),
		------------------------------------------------------
		BeginWeeksSmallRange = Weeks - (Weeks  % @RangeWeeksSmall),
		EndWeeksSmallRange = (Weeks + @RangeWeeksSmall) - (Weeks  % @RangeWeeksSmall),
		BeginWeeksMediumRange = Weeks - (Weeks  % @RangeWeeksMedium),
		EndWeeksMediumRange = (Weeks + @RangeWeeksMedium) - (Weeks  % @RangeWeeksMedium),
		BeginWeeksLargeRange = Weeks - (Weeks  % @RangeWeeksLarge),
		EndWeeksLargeRange = (Weeks + @RangeWeeksLarge) - (Weeks  % @RangeWeeksLarge),
		BeginWeeksHugeRange = Weeks - (Weeks  % @RangeWeeksHuge),
		EndWeeksHugeRange = (Weeks + @RangeWeeksHuge) - (Weeks  % @RangeWeeksHuge)
	FROM
	(
		SELECT
			DurationKey,
			BeginSeconds = (@IncrementSeconds * DurationKey) - (@IncrementSeconds),
			EndSeconds = (@IncrementSeconds * DurationKey),
			Seconds = (@IncrementSeconds * DurationKey),
			Minutes = @IncrementSeconds * DurationKey  / 60,
			Hours = (@IncrementSeconds * DurationKey / 60 / 60),
			Days = (@IncrementSeconds * DurationKey / 60 / 60 / 24),
			Weeks = (@IncrementSeconds * DurationKey / 60 / 60 / 24 / 7)
		FROM
			Keys
	) keyData
) rangeData;

GO

/****** Object:  Index [DurationDimension_BeginAndEndSeconds_NCUIDX01]    Script Date: 10/10/2016 2:47:37 PM *****/
CREATE UNIQUE NONCLUSTERED INDEX [DurationDimension_BeginAndEndSeconds_NCUIDX01] ON [Dim].[DurationDimension]
(
	[BeginSeconds] ASC,
	[EndSeconds] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

In the fact tables, I use the [DurationKey] column by matching the time my backup operation took by the [BeginSeconds] and [EndSeconds] columns. The index should help with that matching. You may add additional indexes as you see fit depending on the columns you use for slicing and dicing in your dashboards.

I will appreciate your feedback if you find this useful.

Advertisements

2 thoughts on “SQL Server – Generate and Use Time / Duration Dimension

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s