8

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:

enter image description here

Thank you.

  • Thank you for an interesting problem! I'm not excited about mixing data and presentation but in some cases having all the logic in one place can be practical. – Aaron Bertrand Oct 23 '18 at 11:55
11

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])), '&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</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):

enter image description here

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

![enter image description here

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);
  • Yup. What Aaron said. – Rob Farley Oct 23 '18 at 7:08
  • 2
    You're so weird. – Erik Darling Oct 23 '18 at 13:03
  • Thanks for the help.I get the error : Conversion failed when converting the varchar value '>' to data type int. – Mike T Oct 24 '18 at 21:52
  • @MikeT That code is fully tested, what did you change? Is the leave_type_color column numeric? – Aaron Bertrand Oct 24 '18 at 21:54
  • 1)Does "DECLARE @return_value int" plays a role when i execute the procedure in SQL 2016? 2)I changed a few column names because the leave table is a join of 2 other tables.leave_type_color is integer. – Mike T Oct 24 '18 at 22:00
1

Start by considering what you want to have as the columns, and that’s basically “Week 1 Day 1 (Sun)”, “Week 1 Day 2 (Mon)”, through to “Week 6 Day 7 (Sat)”. Essentially, Day 1-42. January 1st is then “Week 1 Day 2” of January. I’ll call this WeekPlusDay for now.

To work out where each one starts, just consider the weekday part of the date.

Your data set then just has to include that “WeekPlusDay” value, and you display the DayOfMonth.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.