SQL insert dayofweek information in each day column without null

Question

This is my [Resource table] I am getting my data:

Start Date                 Time From  Time To  Time From To    Training Room
2011-05-24 00:00:00.000    8:00       23:00    8:00 - 23:00    Room1
2011-05-25 00:00:00.000    1:00       23:00    1:00 - 23:00    Room1
2011-05-26 00:00:00.000    1:00       23:00    1:00 - 23:00    Room1
2011-05-04 00:00:00.000    20:00      23:00   20:00 - 23:00    Room2
2011-05-05 00:00:00.000    20:00      22:00   20:00 - 22:00    Room2
2011-05-20 00:00:00.000    20:00      23:00   20:00 - 23:00    Room3
2011-05-21 00:00:00.000    17:00      19:00   17:00 - 19:00    Room2

What I want is to populate this table [FACT Training]:

enter image description here

But I am having problems with populating the table without nulls. Since I want to insert data to each day column, inserting only the specific dayofweek name:

WHERE datename(dw,[Start Date]) = 'Monday'
WHERE datename(dw,[Start Date]) = 'Tuesday'
etc...

So my insert looks something like this:

enter image description here

But needs to filter for each day...

score 0 · Answer 1 · answered Nov 06 '13 at 16:36

Looking at the problem you described I started out by only putting the column value into the correct day of the week, this will create 7 rows each only populating the column for the day on which the [Start Date] falls.

SELECT
    [Monday] =
        CASE WHEN DATENAME(DW,[Start Date]) = 'Monday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END
    ,[Tuesday] = 
        CASE WHEN DATENAME(DW,[Start Date]) = 'Tuesday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END
    ,[Wednesday] = 
        CASE WHEN DATENAME(DW,[Start Date]) = 'Wednesday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END
    ,[Thursday] = 
        CASE WHEN DATENAME(DW,[Start Date]) = 'Thursday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END
    ,[Friday] = 
        CASE WHEN DATENAME(DW,[Start Date]) = 'Friday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END
    ,[Saturday] = 
        CASE WHEN DATENAME(DW,[Start Date]) = 'Saturday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END
    ,[Sunday] = 
        CASE WHEN DATENAME(DW,[Start Date]) = 'Sunday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END
FROM
    [Resource table]

But then I thought, you might want to display all these in one row, in which case you would need to group them by a certain value, or values. I am unaware of a SQL Aggregate function for creating a concatenated list of column values for a group so you would probably need to create one, but you could group this by training room and if there's only ever going to be one entry per day per training room something like the following could be used (SQL purists might want to look away now):

SELECT
    [Week Id] = CONVERT(VARCHAR,DATEPART(YEAR,[Start Date])) + '-' + CONVERT(VARCHAR,DATEPART(WEEK,[Start Date]))
    ,[Training Room]
    ,[Monday] = MAX(CASE WHEN DATENAME(DW,[Start Date]) = 'Monday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END)
    ,[Tuesday] = MAX(CASE WHEN DATENAME(DW,[Start Date]) = 'Tuesday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END)
    ,[Wednesday] = MAX(CASE WHEN DATENAME(DW,[Start Date]) = 'Wednesday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END)
    ,[Thursday] = MAX(CASE WHEN DATENAME(DW,[Start Date]) = 'Thursday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END)
    ,[Friday] = MAX(CASE WHEN DATENAME(DW,[Start Date]) = 'Friday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END)
    ,[Saturday] = MAX(CASE WHEN DATENAME(DW,[Start Date]) = 'Saturday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END)
    ,[Sunday] = MAX(CASE WHEN DATENAME(DW,[Start Date]) = 'Sunday' THEN ISNULL([Training Room],'') + N' - ' + [Time From To] ELSE '' END)
FROM
    [Resource table] 
GROUP BY 
    CONVERT(VARCHAR,DATEPART(YEAR,[Start Date])) + '-' + CONVERT(VARCHAR,DATEPART(WEEK,[Start Date]))
    ,[Training Room]

You could always leave out the declaration of the two initial columns I've grouped by ([Week Id] and [Training Room]) as you don't have to display them in order to view them.

SQL insert dayofweek information in each day column without null

1 Answers1