jon-kim/CreateEmployeeTimetable_Pivot.SQL

## CreateEmployeeTimetable_Pivot.SQL
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
	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