Create Employee Work Shift Schedule using PIVOTS

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.
<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>
			<tr>
				<td>Alvis Duester</td><td/>
				<td/><td/><td/>
				<td/><td class='one'/><td class='one'/>
				<td class='one'/><td class='one'/><td class='one'/>
				<td class='one'/><td class='one'/><td class='one'/>
			</tr>
			<tr>
				<td>Boony Antonchik</td><td/>
				<td/><td/><td/>
				<td/><td/><td class='one'/>
				<td class='one'/><td class='one'/><td class='one'/>
				<td/><td/><td/>
			</tr>
			<tr>
				<td>Cookie Saffle</td><td/>
				<td class='one'/><td class='one'/><td class='one'/>
				<td class='one'/><td/><td/>
				<td/><td/><td/>
				<td/><td/><td/>
			</tr>
			<tr>
				<td>Genvieve Arends</td><td class='one'/>
				<td class='one'/><td class='one'/><td class='one'/>
				<td class='one'/><td class='one'/><td/>
				<td/><td/><td/>
				<td/><td/><td/>
			</tr>
			<tr>
				<td>Giralda Ranfield</td><td/>
				<td/><td/><td/>
				<td/><td class='one'/><td class='one'/>
				<td class='one'/><td/><td/>
				<td/><td/><td/>
			</tr>
			<tr>
				<td>Liane Bayldon</td><td/>
				<td/><td/><td/>
				<td class='one'/><td class='one'/><td class='one'/>
				<td class='one'/><td class='one'/><td class='one'/>
				<td class='one'/><td class='one'/><td/>
			</tr>
			<tr>
				<td>Rhianna Sherar</td><td/>
				<td/><td/><td/>
				<td/><td class='one'/><td class='one'/>
				<td class='one'/><td class='one'/><td/>
				<td/><td/><td/>
			</tr>
			<tr>
				<td>Rosanne Paffley</td><td/>
				<td/><td/><td/>
				<td/><td class='one'/><td class='one'/>
				<td class='one'/><td class='one'/><td class='one'/>
				<td class='one'/><td class='one'/><td/>
			</tr>
			<tr>
				<td>Toma Wittier</td><td/>
				<td/><td/><td/>
				<td/><td/><td class='one'/>
				<td class='one'/><td/><td/>
				<td/><td/><td/>
			</tr>
			<tr>
				<td>Ulla Friatt</td><td/>
				<td/><td/><td/>
				<td/><td/><td/>
				<td/><td/><td/>
				<td class='one'/><td class='one'/><td/>
			</tr>
		</table>
	</div></body>
</html>

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 #DATA

https://gist.github.com/jon-kim/e5fa66afac61b0714044e3e64ca7c85b

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: