jon-kim/CreateEmployeeTimetable_Pivot_Dynamic.SQL

## CreateEmployeeTimetable_Pivot_Dynamic.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')

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @PIVOTHTML AS NVARCHAR(MAX)

;WITH cte (datelist, maxdate) AS
(
    SELECT min(shift_start) datelist, max(shift_end) maxdate
    FROM #DATA
    UNION all
    SELECT dateadd(HOUR, 1, datelist), maxdate
    FROM cte
    WHERE datelist < maxdate
)
SELECT c.datelist
INTO #TEMPDATES
FROM cte c

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(TIME, datelist))
                    from #TEMPDATES
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT name, ' + @cols + '
		into #PIVOTED from (
			select s.name, s.shift_start, s.shift_end, d.datelist, CONVERT(TIME, datelist) PivotDate
			from #TEMPDATES d
			left join #DATA s on d.datelist between s.shift_start and s.shift_end
		) x
		pivot (
			count(datelist)
			for PivotDate in (' + @cols + ')
		) p

	SELECT @result = CAST((
		SELECT name td, ' + REPLACE(@cols, ',', ' td,') + ' td
		FROM    #PIVOTED
		FOR XML RAW(''tr''), ELEMENTS
	) AS NVARCHAR(MAX))'

EXECUTE sp_executesql @query, N'@result NVARCHAR(MAX) OUTPUT', @result=@PIVOTHTML OUTPUT

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>' + REPLACE(REPLACE(REPLACE(@cols, ':00.0000000]', '</th>'), '[', '<th>'), ',', '') + '</tr>'
    + @PIVOTHTML
    + '</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 #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')

	DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @PIVOTHTML AS NVARCHAR(MAX)

	;WITH cte (datelist, maxdate) AS
	(
	SELECT min(shift_start) datelist, max(shift_end) maxdate
	FROM #DATA
	UNION all
	SELECT dateadd(HOUR, 1, datelist), maxdate
	FROM cte
	WHERE datelist < maxdate
	)
	SELECT c.datelist
	INTO #TEMPDATES
	FROM cte c

	select @cols = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(TIME, datelist))
	from #TEMPDATES
	FOR XML PATH(''), TYPE
	).value('.', 'VARCHAR(MAX)')
	,1,1,'')

	set @query = 'SELECT name, ' + @cols + '
	into #PIVOTED from (
	select s.name, s.shift_start, s.shift_end, d.datelist, CONVERT(TIME, datelist) PivotDate
	from #TEMPDATES d
	left join #DATA s on d.datelist between s.shift_start and s.shift_end
	) x
	pivot (
	count(datelist)
	for PivotDate in (' + @cols + ')
	) p

	SELECT @result = CAST((
	SELECT name td, ' + REPLACE(@cols, ',', ' td,') + ' td
	FROM #PIVOTED
	FOR XML RAW(''tr''), ELEMENTS
	) AS NVARCHAR(MAX))'

	EXECUTE sp_executesql @query, N'@result NVARCHAR(MAX) OUTPUT', @result=@PIVOTHTML OUTPUT

	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>' + REPLACE(REPLACE(REPLACE(@cols, ':00.0000000]', '</th>'), '[', '<th>'), ',', '') + '</tr>'
	+ @PIVOTHTML
	+ '</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 #TEMPDATES
	DROP TABLE #DATA