Skip to content

Instantly share code, notes, and snippets.

@jon-kim
Last active June 6, 2017 19:39
Show Gist options
  • Save jon-kim/e5fa66afac61b0714044e3e64ca7c85b to your computer and use it in GitHub Desktop.
Save jon-kim/e5fa66afac61b0714044e3e64ca7c85b to your computer and use it in GitHub Desktop.
Create Employee Timetable with PIVOT in SQL Server
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment