Create Employee Work Shift Schedule using PIVOTS
Once in awhile, it is required of you to create visualizations of the data you have in the database.
For example, you have a set of data of employees and when they start their shift and when it ends.
But you would like to create a chart for the day to see which employees work together.
Fortunately, creating this chart is easy in SQL Server using Common Table Expressions (CTE) and Pivot.
The sample data we will use is the data seen below.
name | shift_start | shift_end |
---|---|---|
Genvieve Arends | 2017-05-29 08:00 | 2017-05-29 13:00 |
Cookie Saffle | 2017-05-29 09:00 | 2017-05-29 12:00 |
Liane Bayldon | 2017-05-29 12:00 | 2017-05-29 19:00 |
Rosanne Paffley | 2017-05-29 13:00 | 2017-05-29 19:00 |
Giralda Ranfield | 2017-05-29 13:00 | 2017-05-29 15:00 |
Alvis Duester | 2017-05-29 13:00 | 2017-05-29 20:00 |
Rhianna Sherar | 2017-05-29 13:00 | 2017-05-29 16:00 |
Boony Antonchik | 2017-05-29 14:00 | 2017-05-29 17:00 |
Toma Wittier | 2017-05-29 14:00 | 2017-05-29 15:00 |
Ulla Friatt | 2017-05-29 18:00 | 2017-05-29 19:00 |
And you want to create a work shift schedule for the day similar to the one below.
name | 08:00 | 09:00 | 10:00 | 11:00 | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Alvis Duester | |||||||||||||
Boony Antonchik | |||||||||||||
Cookie Saffle | |||||||||||||
Genvieve Arends | |||||||||||||
Giralda Ranfield | |||||||||||||
Liane Bayldon | |||||||||||||
Rhianna Sherar | |||||||||||||
Rosanne Paffley | |||||||||||||
Toma Wittier | |||||||||||||
Ulla Friatt |
How would you do it? Using pivot.
First, let’s setup the table with the SQL script below.
CREATE TABLE #DATA ( name VARCHAR(50), shift_start DATETIME, shift_end DATETIME ) INSERT INTO #DATA VALUES ('Boony Antonchik', '2017-05-30 14:00', '2017-05-30 17:00') INSERT INTO #DATA VALUES ('Toma Wittier', '2017-05-30 14:00', '2017-05-30 15:00') INSERT INTO #DATA VALUES ('Genvieve Arends', '2017-05-30 8:00', '2017-05-30 13:00') INSERT INTO #DATA VALUES ('Ulla Friatt', '2017-05-30 18:00', '2017-05-30 19:00') INSERT INTO #DATA VALUES ('Giralda Ranfield', '2017-05-30 13:00', '2017-05-30 15:00') INSERT INTO #DATA VALUES ('Alvis Duester', '2017-05-30 13:00', '2017-05-30 20:00') INSERT INTO #DATA VALUES ('Rhianna Sherar', '2017-05-30 13:00', '2017-05-30 16:00') INSERT INTO #DATA VALUES ('Liane Bayldon', '2017-05-30 12:00', '2017-05-30 19:00') INSERT INTO #DATA VALUES ('Cookie Saffle', '2017-05-30 9:00', '2017-05-30 12:00') INSERT INTO #DATA VALUES ('Rosanne Paffley', '2017-05-30 13:00', '2017-05-30 19:00')
Next, let’s get a list of hours between the earliest time to the latest time.
Using Common Table Expressions (CTE) makes it easy to do a recursive query.
;WITH cte (time_list, time_max) AS ( SELECT min(shift_start) time_list, max(shift_end) time_max FROM #DATA UNION ALL SELECT dateadd(HOUR, 1, time_list), time_max FROM cte WHERE time_list < time_max ) SELECT c.time_list INTO #TEMPDATES FROM cte c
If you query the #TEMPDATES table, you will get a list of datetimes in increments of 1 hour.
time_list |
---|
2017-05-30 08:00:00.000 |
2017-05-30 09:00:00.000 |
2017-05-30 10:00:00.000 |
2017-05-30 11:00:00.000 |
2017-05-30 12:00:00.000 |
2017-05-30 13:00:00.000 |
2017-05-30 14:00:00.000 |
2017-05-30 15:00:00.000 |
2017-05-30 16:00:00.000 |
2017-05-30 17:00:00.000 |
2017-05-30 18:00:00.000 |
2017-05-30 19:00:00.000 |
2017-05-30 20:00:00.000 |
Next, let’s join the two tables, #DATA and #TEMPDATES, so that we get a list of times the employee’s shift falls into.
SELECT name, shift_start, shift_end, time_list, CONVERT(TIME, time_list) PivotDate FROM #TEMPDATES t LEFT JOIN #DATA d ON t.time_list between shift_start and shift_end
A sample of how it looks below.
name | shift_start | shift_end | time_list | PivotDate |
---|---|---|---|---|
Genvieve Arends | 2017-05-30 08:00:00.000 | 2017-05-30 13:00:00.000 | 2017-05-30 08:00:00.000 | 08:00:00.0000000 |
Genvieve Arends | 2017-05-30 08:00:00.000 | 2017-05-30 13:00:00.000 | 2017-05-30 09:00:00.000 | 09:00:00.0000000 |
Cookie Saffle | 2017-05-30 09:00:00.000 | 2017-05-30 12:00:00.000 | 2017-05-30 09:00:00.000 | 09:00:00.0000000 |
Genvieve Arends | 2017-05-30 08:00:00.000 | 2017-05-30 13:00:00.000 | 2017-05-30 10:00:00.000 | 10:00:00.0000000 |
Cookie Saffle | 2017-05-30 09:00:00.000 | 2017-05-30 12:00:00.000 | 2017-05-30 10:00:00.000 | 10:00:00.0000000 |
Genvieve Arends | 2017-05-30 08:00:00.000 | 2017-05-30 13:00:00.000 | 2017-05-30 11:00:00.000 | 11:00:00.0000000 |
Cookie Saffle | 2017-05-30 09:00:00.000 | 2017-05-30 12:00:00.000 | 2017-05-30 11:00:00.000 | 11:00:00.0000000 |
Genvieve Arends | 2017-05-30 08:00:00.000 | 2017-05-30 13:00:00.000 | 2017-05-30 12:00:00.000 | 12:00:00.0000000 |
Liane Bayldon | 2017-05-30 12:00:00.000 | 2017-05-30 19:00:00.000 | 2017-05-30 12:00:00.000 | 12:00:00.0000000 |
Cookie Saffle | 2017-05-30 09:00:00.000 | 2017-05-30 12:00:00.000 | 2017-05-30 12:00:00.000 | 12:00:00.0000000 |
Genvieve Arends | 2017-05-30 08:00:00.000 | 2017-05-30 13:00:00.000 | 2017-05-30 13:00:00.000 | 13:00:00.0000000 |
Giralda Ranfield | 2017-05-30 13:00:00.000 | 2017-05-30 15:00:00.000 | 2017-05-30 13:00:00.000 | 13:00:00.0000000 |
Alvis Duester | 2017-05-30 13:00:00.000 | 2017-05-30 20:00:00.000 | 2017-05-30 13:00:00.000 | 13:00:00.0000000 |
And now the PIVOT magic. Let’s PIVOT the table from above, so that we have a column for the employee’s name and a column for each hour.
SELECT name, [08:00], [09:00], [10:00], [11:00], [12:00], [13:00] , [14:00], [15:00], [16:00], [17:00], [18:00], [19:00], [20:00] INTO #PIVOTED FROM ( SELECT name, shift_start, shift_end, time_list, CONVERT(TIME, time_list) PivotDate FROM #TEMPDATES t LEFT JOIN #DATA d ON t.time_list between shift_start and shift_end ) x PIVOT ( COUNT(time_list) FOR PivotDate IN ([08:00], [09:00], [10:00], [11:00], [12:00], [13:00] , [14:00], [15:00], [16:00], [17:00], [18:00], [19:00], [20:00]) ) p
And the result will be like so:
name | 08:00 | 09:00 | 10:00 | 11:00 | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Alvis Duester | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
Boony Antonchik | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 0 |
Cookie Saffle | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Genvieve Arends | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Giralda Ranfield | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
Liane Bayldon | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
Rhianna Sherar | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
Rosanne Paffley | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
Toma Wittier | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
Ulla Friatt | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 |
Now all you have to do is generate the html.
DECLARE @body VARCHAR(MAX) SET @body = '<html><head>' + '<style type="text/css">' + '#sample table {font-size: 0.6em;}' + '#sample .one {background-color: #32cd32;}' + '</style>' + '</head><body>' + '<div id=''sample''><table>' + '<tr><th>Name</th><th>[08:00]</th><th>[09:00]</th><th>[10:00]</th><th>[11:00]</th>' + '<th>[12:00]</th><th>[13:00]</th><th>[14:00]</th><th>[15:00]</th><th>[16:00]</th>' + '<th>[17:00]</th><th>[18:00]</th><th>[19:00]</th><th>[20:00]</th></tr>' + CAST(( SELECT name td, [08:00] td, [09:00] td, [10:00] td, [11:00] td, [12:00] td, [13:00] td, [14:00] td, [15:00] td, [16:00] td, [17:00] td, [18:00] td, [19:00] td, [20:00] td FROM #PIVOTED FOR XML RAW('tr'), ELEMENTS ) AS NVARCHAR(MAX)) + '</table></div></body></html>' SET @body = REPLACE(@body, '<td>1</td>', '<td class=''one'' />') SET @body = REPLACE(@body, '<td>0</td>', '<td />') SELECT @body
Below is the HTML ouput.
Name | [08:00] | [09:00] | [10:00] | [11:00] | [12:00] | [13:00] | [14:00] | [15:00] | [16:00] | [17:00] | [18:00] | [19:00] | [20:00] |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Alvis Duester | |||||||||||||
Boony Antonchik | |||||||||||||
Cookie Saffle | |||||||||||||
Genvieve Arends | |||||||||||||
Giralda Ranfield | |||||||||||||
Liane Bayldon | |||||||||||||
Rhianna Sherar | |||||||||||||
Rosanne Paffley | |||||||||||||
Toma Wittier | |||||||||||||
Ulla Friatt |
Now that wasn’t too hard. But I don’t like how I have to specify the hours.
What if I had 100 columns to pivot? That would be a pain and is prone to errors.
Next time, I’ll show you how to create the same result but using dynamic SQL to generate the pivot columns
For now, the complete code is below.
CREATE TABLE #DATA ( name VARCHAR(50), shift_start DATETIME, shift_end DATETIME ) INSERT INTO #DATA VALUES ('Boony Antonchik', '2017-05-30 14:00', '2017-05-30 17:00') INSERT INTO #DATA VALUES ('Toma Wittier', '2017-05-30 14:00', '2017-05-30 15:00') INSERT INTO #DATA VALUES ('Genvieve Arends', '2017-05-30 8:00', '2017-05-30 13:00') INSERT INTO #DATA VALUES ('Ulla Friatt', '2017-05-30 18:00', '2017-05-30 19:00') INSERT INTO #DATA VALUES ('Giralda Ranfield', '2017-05-30 13:00', '2017-05-30 15:00') INSERT INTO #DATA VALUES ('Alvis Duester', '2017-05-30 13:00', '2017-05-30 20:00') INSERT INTO #DATA VALUES ('Rhianna Sherar', '2017-05-30 13:00', '2017-05-30 16:00') INSERT INTO #DATA VALUES ('Liane Bayldon', '2017-05-30 12:00', '2017-05-30 19:00') INSERT INTO #DATA VALUES ('Cookie Saffle', '2017-05-30 9:00', '2017-05-30 12:00') INSERT INTO #DATA VALUES ('Rosanne Paffley', '2017-05-30 13:00', '2017-05-30 19:00') ;WITH cte (time_list, time_max) AS ( SELECT min(shift_start) time_list, max(shift_end) time_max FROM #DATA UNION ALL SELECT dateadd(HOUR, 1, time_list), time_max FROM cte WHERE time_list < time_max ) SELECT c.time_list INTO #TEMPDATES FROM cte c SELECT name, [08:00], [09:00], [10:00], [11:00], [12:00], [13:00] , [14:00], [15:00], [16:00], [17:00], [18:00], [19:00], [20:00] INTO #PIVOTED FROM ( SELECT name, shift_start, shift_end, time_list, CONVERT(TIME, time_list) PivotDate FROM #TEMPDATES t LEFT JOIN #DATA d ON t.time_list between shift_start and shift_end ) x PIVOT ( COUNT(time_list) FOR PivotDate IN ([08:00], [09:00], [10:00], [11:00], [12:00], [13:00] , [14:00], [15:00], [16:00], [17:00], [18:00], [19:00], [20:00]) ) p DECLARE @body VARCHAR(MAX) SET @body = '<html><head>' + '<style type="text/css">' + '#sample table {font-size: 0.6em;}' + '#sample .one {background-color: #32cd32;}' + '</style>' + '</head><body>' + '<div id=''sample''><table>' + '<tr><th>Name</th><th>[08:00]</th><th>[09:00]</th><th>[10:00]</th><th>[11:00]</th>' + '<th>[12:00]</th><th>[13:00]</th><th>[14:00]</th><th>[15:00]</th><th>[16:00]</th>' + '<th>[17:00]</th><th>[18:00]</th><th>[19:00]</th><th>[20:00]</th></tr>' + CAST(( SELECT name td, [08:00] td, [09:00] td, [10:00] td, [11:00] td, [12:00] td, [13:00] td, [14:00] td, [15:00] td, [16:00] td, [17:00] td, [18:00] td, [19:00] td, [20:00] td FROM #PIVOTED FOR XML RAW('tr'), ELEMENTS ) AS NVARCHAR(MAX)) + '</table></div></body></html>' SET @body = REPLACE(@body, '<td>1</td>', '<td class=''one'' />') SET @body = REPLACE(@body, '<td>0</td>', '<td />') SELECT @body DROP TABLE #PIVOTED DROP TABLE #TEMPDATES DROP TABLE #DATAhttps://gist.github.com/jon-kim/e5fa66afac61b0714044e3e64ca7c85b
One thought on “Create Employee Work Shift Schedule using PIVOTS”