Create Employee Work Shift Schedule using PIVOTS

June 6, 2017 jonkim Comments 1 comment

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

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

Jon Kim

Freelance Programmer Picking Up His Marbles

Create Employee Work Shift Schedule using PIVOTS

June 6, 2017 jonkim Comments 1 comment

Like this:

Related

One thought on “Create Employee Work Shift Schedule using PIVOTS”

Leave a ReplyCancel reply

Share this:

Like this:

Related

One thought on “Create Employee Work Shift Schedule using PIVOTS”

Leave a ReplyCancel reply