The most complex part of this is just building the calendar in that format. Pivoting and surrounding it with HTML is pretty easy. First, let's start with this, your employee table with leave dates. leave_type didn't seem relevant to the problem at hand.
CREATE TABLE dbo.EmpLeave
(
EmployeeID int,
leave_date date,
leave_type_color char(6)
);
INSERT dbo.EmpLeave(EmployeeID,leave_date,leave_type_color)
VALUES(1,'2018-01-02','7777cc'),(1,'2018-04-01','ffffac');
The procedure I came up with looks like this (and warning: it assumes @@DATEFIRST = 7):
CREATE PROCEDURE dbo.BuildLeaveHTMLTable
@EmployeeID int,
@Year smallint = NULL
AS
BEGIN
SET NOCOUNT ON;
SET @Year = COALESCE(@Year, DATEPART(YEAR, GETDATE()));
DECLARE @FirstDay date = DATEADD(YEAR, @Year-1900, 0);
;WITH Numbers AS ( -- 366 possible days (leap year)
SELECT n = 1 UNION ALL SELECT n + 1 FROM Numbers WHERE n <= 365
),
Calendar AS ( -- a year's worth of dates and dateparts
SELECT [Date] = d,
MonthStart = DATEADD(DAY, 1-DAY(d),d),
Y = CONVERT(smallint, DATEPART(YEAR, d)),
M = CONVERT(tinyint, DATEPART(MONTH, d)),
D = CONVERT(tinyint, DATEPART(DAY, d)),
WY = CONVERT(tinyint, DATEPART(WEEK, d)),
DW = CONVERT(tinyint, DATEPART(WEEKDAY,d))
FROM
(
SELECT d = CONVERT(date,DATEADD(DAY, n-1, @FirstDay)) FROM Numbers
) AS c WHERE YEAR(d) = @Year -- in case it's not a leap year
),
BaseSlots AS ( -- base set of 37 ints
-- month can be spread across 6 weeks, but no more than 2 days in 6th week
SELECT TOP (37) slot = n FROM Numbers ORDER BY n
),
Months AS ( -- base set of 12 ints
SELECT TOP (12) m = slot FROM BaseSlots ORDER BY slot
),
SlotAlignment AS ( -- align days of week to slot numbers
-- this is the most cryptic part of this solution
-- determines which set of 7 slots, and which slot
-- exactly, a given date will appear under
SELECT c.*, slot = DW+(c.WY+1-DATEPART(WEEK,c.MonthStart)-1)*7
FROM Calendar AS c
INNER JOIN Months AS m ON c.M = m.m
),
SlotMatrix AS ( -- extrapolate actual dates to 37 x 12 matrix
SELECT m.m, s.slot, sa.[Date]
FROM BaseSlots AS s
CROSS JOIN Months AS m
LEFT OUTER JOIN SlotAlignment AS sa
ON sa.m = m.m AND sa.slot = s.slot
),
FinalHTML AS ( -- build some HTML!
SELECT m = '<!-- ' + RIGHT('0' + RTRIM(m), 2) + ' -->',
slot, cell = CASE WHEN slot = 1 THEN '<tr><th>'
+ COALESCE(DATENAME(MONTH,DATEADD(MONTH, m-1, 0)),'')
+ '</th>' ELSE '' END + '<td' + COALESCE(' bgcolor=#'
+ RIGHT(CONVERT(varchar(10),CONVERT(varbinary(8), el.leave_type_color),1),6),
CASE WHEN DATEPART(WEEKDAY, [Date]) IN (1,7)
THEN ' bgcolor=#cccccc' ELSE '' END)
+ '>' + COALESCE(RTRIM(DATEPART(DAY,[Date])), ' ')
+ '</td>' + CASE WHEN slot = 37 THEN '</tr>' ELSE '' END
FROM SlotMatrix AS q LEFT OUTER JOIN dbo.EmpLeave AS el
ON q.Date = el.leave_date
AND el.EmployeeID = @EmployeeID
) -- now turn it sideways
SELECT m = '<!-- 00 -->',
[1] = '<tr><th>Month</th><th>S</th>', [2] = '<th>M</th>',
[3] = '<th>T</th>', [4] = '<th>W</th>', [5] = '<th>T</th>',
[6] = '<th>F</th>', [7] = '<th>S</th>', [8] = '<th>S</th>',
[9] = '<th>M</th>', [10] = '<th>T</th>', [11] = '<th>W</th>',
[12] = '<th>T</th>', [13] = '<th>F</th>', [14] = '<th>S</th>',
[15] = '<th>S</th>', [16] = '<th>M</th>', [17] = '<th>T</th>',
[18] = '<th>W</th>', [19] = '<th>T</th>', [20] = '<th>F</th>',
[21] = '<th>S</th>', [22] = '<th>S</th>', [23] = '<th>M</th>',
[24] = '<th>T</th>', [25] = '<th>W</th>', [26] = '<th>T</th>',
[27] = '<th>F</th>', [28] = '<th>S</th>', [29] = '<th>S</th>',
[30] = '<th>M</th>', [31] = '<th>T</th>', [32] = '<th>W</th>',
[33] = '<th>T</th>', [34] = '<th>F</th>', [35] = '<th>S</th>',
[36] = '<th>S</th>', [37] = '<th>M</th>'
UNION ALL
(
SELECT * FROM FinalHTML PIVOT (MAX(cell) FOR slot IN
(
[1], [2], [3], [4], [5], [6], [7], [8], [9], [10],[11],[12],[13],[14],
[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],
[29],[30],[31],[32],[33],[34],[35],[36],[37]
)) AS p
)
ORDER BY m OPTION (MAXRECURSION 366);
END
GO
Results of this call:
EXEC dbo.BuildLeaveHTMLTable @EmployeeID = 1;
Look like this (I stopped at the 7th day column):
You'll have to add the <table> / </table> wrapper yourself, but here is what the output looks like when put in between those and saved as HTML (and of course you can further enhance it with CSS):
When leave falls on a weekend, the leave color trumps the weekend color, but that is easy to adjust. Change this:
+ COALESCE(' bgcolor=#' + RTRIM(el.leave_type_color),
CASE WHEN DATEPART(WEEKDAY, [Date]) IN (1,7)
THEN ' bgcolor=#cccccc' ELSE '' END)
To this:
+ CASE WHEN DATEPART(WEEKDAY, [Date]) IN (1,7)
THEN ' bgcolor=#cccccc' ELSE COALESCE(' bgcolor=#'
+ RTRIM(el.leave_type_color), '') END
To convert a color in decimal format (like 65280) to its RGB equivalent (00FF00), you have to do a bunch of manipulation. I would consider storing it as RGB hex in the first place, but I updated the solution here with something similar to this:
SELECT RIGHT(CONVERT(varchar(10),CONVERT(varbinary(8), 65280),1),6);
