snippetsqlModerate

How to create recurrent weekdays as columns in a pivot?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

weekdayscolumnscreatepivotrecurrenthow

Problem

I am newcomer to programming and databases and I would be grateful for some help on the following scenario.

I use PHP with SQL Server. I am building an employee attendance system and I would like to create a (pivot) table with months as rows and all weekdays name as columns (for a specific year). The values in cells will be the number of day(1, 2, 3...31).

The background color of the cell (already exists as table column) declares the type of employees' leave. The table has the following columns: employee_id, leave_date, leave_type, leave_type_color.

I want to achieve a result like below:

Thank you.

Solution

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 ', 
      slot, cell = CASE WHEN slot = 1 THEN '' 
        + COALESCE(DATENAME(MONTH,DATEADD(MONTH, m-1, 0)),'') 
        + '' ELSE '' END + '' + COALESCE(RTRIM(DATEPART(DAY,[Date])), ' ')
        + '' + CASE WHEN slot = 37 THEN '' 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 = '', 
    [1]  = 'MonthS',    [2]  = 'M', 
    [3]  = 'T', [4]  = 'W', [5]  = 'T', 
    [6]  = 'F', [7]  = 'S', [8]  = 'S', 
    [9]  = 'M', [10] = 'T', [11] = 'W',
    [12] = 'T', [13] = 'F', [14] = 'S', 
    [15] = 'S', [16] = 'M', [17] = 'T',
    [18] = 'W', [19] = 'T', [20] = 'F', 
    [21] = 'S', [22] = 'S', [23] = 'M',
    [24] = 'T', [25] = 'W', [26] = 'T', 
    [27] = 'F', [28] = 'S', [29] = 'S', 
    [30] = 'M', [31] = 'T', [32] = 'W', 
    [33] = 'T', [34] = 'F', [35] = 'S',
    [36] = 'S', [37] = 'M'
  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 ` /

 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);

Code Snippets

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');

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])), '&nbsp;')
        + '</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</

EXEC dbo.BuildLeaveHTMLTable @EmployeeID = 1;

+ COALESCE(' bgcolor=#' + RTRIM(el.leave_type_color),
      CASE WHEN DATEPART(WEEKDAY, [Date]) IN (1,7) 
      THEN ' bgcolor=#cccccc' ELSE '' END)

+ CASE WHEN DATEPART(WEEKDAY, [Date]) IN (1,7) 
      THEN ' bgcolor=#cccccc' ELSE COALESCE(' bgcolor=#' 
      + RTRIM(el.leave_type_color), '') END

Context

StackExchange Database Administrators Q#220772, answer score: 11

Revisions (0)

No revisions yet.