Best Way to store days of the week with Time Spans

Question

I'm creating a slowly changing dimension table to keep a log of when someone changed a schedule and what they changed it to

This required me to store Days of the week with a start time and end time associated with it the information is in a table structured with UserID, Day of the Week, StartTime, EndTime then has rows of values for each day of the week for each user.

What is the best way to flatten this out and store it into a table where I can retrieve the Start stop times for any day of the week. I was thinking a columns for each day of the week with a start time and stop time. For example Monday_Start, Monday_End. The particular date this day of the week falls on is irrelevant as that aspect is logged by a rowstartdate column and rowenddate column.

Answer 1

I see no reason why a database view couldn't be used here with the help of a current indication field.

Assuming you had a Type 2 Dimension as such:

 UserID
,DayOfWeek
,StartWorkTime
,EndWorkTime
,EffectiveStartDate
,EffectiveEndDate
,Current -- Y/N Flags for being current or not current

You could effectively create a database view that pivoted StartWorkTime and EndWorkTime onto UserID and Current columns.

 CREATE VIEW [schema].[view_name] AS
 SELECT
    UserID
   ,[Current] -- Y = Yes / N = No
   ,MAX(CASE WHEN DayOfWeek = 2 THEN StartWorkTime  ELSE NULL END) AS Mon_StartWorkTime
   ,MAX(CASE WHEN DayOfWeek = 2 THEN EndWorkTime    ELSE NULL END) AS Mon_EndWorkTime 
   ,MAX(CASE WHEN DayOfWeek = 3 THEN StartWorkTime  ELSE NULL END) AS Tue_StartWorkTime
   ,MAX(CASE WHEN DayOfWeek = 3 THEN EndWorkTime    ELSE NULL END) AS Tue_EndWorkTime 
   ,MAX(CASE WHEN DayOfWeek = 4 THEN StartWorkTime  ELSE NULL END) AS Wed_StartWorkTime
   ,MAX(CASE WHEN DayOfWeek = 4 THEN EndWorkTime    ELSE NULL END) AS Wed_EndWorkTime 
   ,MAX(CASE WHEN DayOfWeek = 5 THEN StartWorkTime  ELSE NULL END) AS Thu_StartWorkTime
   ,MAX(CASE WHEN DayOfWeek = 5 THEN EndWorkTime    ELSE NULL END) AS Thu_EndWorkTime 
   ,MAX(CASE WHEN DayOfWeek = 6 THEN StartWorkTime  ELSE NULL END) AS Fri_StartWorkTime
   ,MAX(CASE WHEN DayOfWeek = 6 THEN EndWorkTime    ELSE NULL END) AS Fri_EndWorkTime 
 FROM [database].[schema].[table_name]
 WHERE [Current] = 'Y'
 GROUP BY
    UserID
   ,[Current]

Then your results would be for one employee working 0800 to 1600 some days and to 1200 other days as such:

UserID  Current Mon_StartWorkTime   Mon_EndWorkTime     Tue_StartWorkTime   Tue_EndWorkTime     Wed_StartWorkTime   Wed_EndWorkTime     Thu_StartWorkTime   Thu_EndWorkTime     Fri_StartWorkTime   Fri_EndWorkTime
1       Y       08:00:00.0000000    16:00:00.0000000    08:00:00.0000000    16:00:00.0000000    08:00:00.0000000    16:00:00.0000000    08:00:00.0000000    12:00:00.0000000    08:00:00.0000000    12:00:00.0000000