Use SQL Server and CSS to Generate More Interesting Events Calendar in HTML

By:   |   Updated: 2023-02-07   |   Comments   |   Related: > Dates


Problem

Lists can be boring. On blog sites where I have a lot of content, I like a little more pizzazz on my archives page rather than just a long list of "{month name} {year}."

But calendars are irregular. Between leap years and a varying number of days, it can be challenging to construct a visual calendar that is accurate and self-maintaining. How can I generate this HTML and CSS from the database?

Solution

While I doubt many people want to browse my blog posts by month, sometimes I want to take a quick glance to see when my busiest weeks or months have been. And maybe your blog has a broader audience, or you aren't presenting blog posts at all - this applies to any type of event.

Let's start from the end - what we want the output to be. Currently, my archives page might look like this:

A very boring Archives list

But what if I want something prettier, like this (say, rendered on January 25th)?

Visual calendar for January 2023, as observed on January 25th

The month name is clickable and will take you to the archives page for January. The days of the month are accurate – there was a post on Tuesday, January 10th, highlighted in green, and clicking that box would take you to the archives for that day (rather than to the post itself, since any given day could have multiple posts). Similarly, there was a post on Thursday, January 19th, and clicking that day will take you to the archives page for the 19th. The days after the 25th look slightly different from the rest because they're in the future.

Admittedly, January 2023 is a convenient and pedestrian example because the 1st of the month is on a Sunday, so no tricks are required to offset the first day of the month. Let's look at how we'd want the last three months of 2022 to look on a visual calendar:

Visual calendars for 2022 Q3

Notice how the start days of the month are lined up correctly – December started on a Thursday and ended on a Saturday, November was Tuesday to Wednesday, and October was Saturday to Monday. This is accomplished with a little trick I picked up from Zell. Essentially, we are using a CSS grid that is 7 x 6 (7 days times a max of 6 weeks falling in any given month). And we can push the 1st of the month to start at the appropriate grid column by using a class for each possible weekday. Here's the CSS:

days 
{
  display: grid;
  grid-template-columns: repeat(7, 24px);
  grid-template-rows:    repeat(6, 18px);
}
days.d1 day:first-child { grid-column: 1; }
days.d2 day:first-child { grid-column: 2; }
days.d3 day:first-child { grid-column: 3; }
days.d4 day:first-child { grid-column: 4; }
days.d5 day:first-child { grid-column: 5; }
days.d6 day:first-child { grid-column: 6; }
days.d7 day:first-child { grid-column: 7; }

And the HTML for October:

<year>2022</year>
  ...
  <month> 
    <monthname><a href="/2022/10/">October</a> (6)</monthname>
    <days class="d7">
      <day>1</day>
      <day>2</day>
      <day class="on"><a href="/2022/10/03">3</a></day>
      <day>4</day>
      <day>5</day>
      <day class="on"><a href="/2022/10/06">6</a></day>
      <day>7</day>
      ...
      <day>30</day>
      <day>31</day>
    </days>
  </month>
  ...
</year>

I made up my own HTML elements for the day, month, year, etc., instead of relying on common element names, as I find it frustrating to have to override attributes enforced by theme templates or plug-ins. Probably not the best for accessibility, but the simplicity makes the HTML a lot less confusing.

Getting the grid cells to look right (and behave like a table) uses a CSS trick from Håkan Save Hansson. But this tip is less about the presentation and more about getting the database to generate the HTML for you.

We need to think about that generation in two steps:

  1. Building the entire list of days (essentially, the month containing the very first event, all the way through to the current month).
  2. Outer joining that list of dates to the actual events.

The list of days can be generated with some simple set generation. Let's say you have a table called dbo.Events and a column called EventDate.

CREATE TABLE dbo.Events(EventDateTime datetime2);
INSERT dbo.Events(EventDateTime) VALUES
  ('20221003 03:35'),('20221006 14:26'),('20221011 05:00'),
  ('20221021 06:34'),('20221024 16:33'),('20221026 17:27'),
  ('20221104 03:35'),('20221109 14:26'),('20221114 05:00'),
  ('20221118 06:34'),('20221121 16:33'),('20221124 17:27'),
  ('20221127 17:27'),
  ('20221207 03:35'),('20221212 14:26'),('20221216 05:00'),
  ('20221222 06:34'),('20221228 17:27'),
  ('20230110 05:55'),('20230119 11:32');

We need to know all the days from the beginning of the month containing the earliest EventDate up until now. A neat trick I've used to generate a series of n numbers, while waiting for GENERATE_SERIES() to be more mainstream, is to run STRING_SPLIT() against a single-character string replicated n-1 times. For example, this produces the numbers 1 to 20:

DECLARE @numRows int = 20;
SELECT rn = ROW_NUMBER() OVER (ORDER BY @@SPID)  
  FROM STRING_SPLIT(REPLICATE('|', @numRows - 1), '|') AS x;

Another trick I've come to appreciate is using EOMONTH to retrieve the first day of a month. For example, the first day of this month can be derived from the following:

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1));

Incorporating those into calculating the days we want in our range:

DECLARE @firstDate date = (SELECT MIN(EventDateTime) FROM dbo.Events),
        @lastDate  date = EOMONTH(GETDATE()),
        @numDays   int;
SET @firstDate = DATEADD(DAY, 1, EOMONTH(@firstDate, -1));
SET @numDays   = DATEDIFF(DAY, @firstDate, @lastDate) + 1;
SELECT TheDay  = DATEADD(DAY, rn-1, @firstDate) FROM
(
  SELECT rn = ROW_NUMBER() OVER (ORDER BY @@SPID)
    FROM STRING_SPLIT(REPLICATE('|', @numDays - 1), '|') AS x
) AS x;

When run in January 2023, this produces 123 rows – the sequence of days from October 1st through January 31st:

TheDay
----------
2022-10-01
2022-10-02
2022-10-03
...
2023-01-29
2023-01-30
2023-01-31

To build our HTML, we also need to know the weekday (regardless of DATEFIRST settings) and, when it is the first of the month, which weekday that day falls on. We can add an outer query to help calculate this:

SELECT TheDay      = src.d, 
    DayOfMonth     = DATEPART(DAY, src.d), 
    WeekdayOfFirst = CONCAT(CASE DATEPART(DAY, src.d) 
         WHEN 1 THEN
           COALESCE(NULLIF((DATEPART(WEEKDAY, src.d) + @@DATEFIRST) % 7, 0), 7)
         END, '')
FROM
(
  SELECT d = DATEADD(DAY, rn-1, @firstDate) FROM
  (
    SELECT rn = ROW_NUMBER() OVER (ORDER BY @@SPID)
      FROM STRING_SPLIT(REPLICATE(',', @numDays - 1), ',') AS x
  ) AS x
) AS src;

Which produces:

TheDay      DayOfMonth  WeekdayOfFirst
----------  ---------   --------------
2022-10-01  1           7
2022-10-02  2           
2022-10-03  3           
...
2022-10-31  31           
2022-11-01  1           3
2022-11-02  2           
...
2022-11-30  30          
2022-12-01  1           5
2022-12-02  2           
...
2022-12-31  31          
2023-01-01  1           1
2023-01-02  2           
...
2023-01-29  29          
2023-01-30  30          
2023-01-31  31

Next, we'll additionally need a way to know whether a day is the first or last day of a month, or the first or last day of the year (which may not be the first or last day of the calendar year, because we're only showing the first month a blog post appears through the current month). We need to know this so we can close any outer HTML tags. Extending the query above might look like this: 

SELECT TheDay        = src.d, 
      DayOfMonth     = src.md, 
      WeekdayOfFirst = CONCAT(CASE src.md WHEN 1 THEN
             COALESCE(NULLIF((src.wd + @@DATEFIRST) % 7, 0), 7)
           END,''),
      IsFirstOfYear  = CASE ROW_NUMBER() OVER (PARTITION BY src.y ORDER BY src.d) 
                         WHEN 1 THEN '1' ELSE '' END,
      IsLastOfYear   = CASE ROW_NUMBER() OVER (PARTITION BY src.y ORDER BY src.d DESC) 
                         WHEN 1 THEN '1' ELSE '' END,
      IsLastOfMonth  = CASE WHEN src.eom = src.d THEN '1' ELSE '' END
  FROM
  (
    SELECT d, y = YEAR(d), wd = DATEPART(WEEKDAY, d),
           md = DATEPART(DAY, d), eom = EOMONTH(d)
    FROM
    (
      SELECT d = DATEADD(DAY, rn-1, @firstDate)
      FROM
      (
        SELECT rn = ROW_NUMBER() OVER (ORDER BY @@SPID)
          FROM STRING_SPLIT(REPLICATE(',', @numDays - 1), ',') AS x
      ) AS x
    ) AS y
  ) AS src 
  ORDER BY TheDay;

Abridged results:

TheDay      DayOfMonth  WeekdayOfFirst  IsFirstOfYear  IsLastOfYear  IsLastOfMonth
----------  ----------  --------------  -------------  ------------  -------------
2022-10-01  1           7               1              
...
2022-10-31  31                                                       1
2022-11-01  1           3             
2022-11-02  2                         
...
2022-11-30  30                                                       1
2022-12-01  1           5               
2022-12-02  2                           
...
2022-12-31  31                                         1             1
2023-01-01  1           1               1         
2023-01-02  2                           
...
2023-01-31  31                                         1             1

To generate the HTML of just the calendars shown above, we can order differently and use CASE expressions to dictate where HTML entities start and end.

SELECT html = CASE WHEN IsLastMonth = '1' AND DayOfMonth = 1 
    THEN CONCAT('<yearname>', TheYear, '</yearname><year>') ELSE '' END
  + CASE DayOfMonth WHEN 1 THEN CONCAT('<month><monthname>', 
         DATENAME(MONTH, TheDay), '</monthname><days class="d',
         WeekdayOfFirst, '">') ELSE '' END
  + CONCAT('<day', CASE WHEN TheDay > GETDATE() THEN ' class="future"' END,
      '>', DayOfMonth, '</day>')
  + CASE IsLastOfMonth WHEN '1' THEN '</days></month>' ELSE '' END
  + CASE WHEN IsLastOfMonth = '1' AND IsFirstMonth = '1' 
    THEN '</year>' ELSE '' END
FROM
(
  SELECT TheDay      = src.d, 
      TheMonth       = src.m,
      TheYear        = src.y,
      DayOfMonth     = src.md, 
      WeekdayOfFirst = CONCAT(CASE src.md WHEN 1 THEN
             COALESCE(NULLIF((src.wd + @@DATEFIRST) % 7, 0), 7)
           END,''),
      IsLastOfMonth  = CASE WHEN src.eom = src.d THEN '1' ELSE '' END,
      IsFirstMonth   = CASE ROW_NUMBER() OVER (PARTITION BY src.y ORDER BY src.m, src.d DESC) 
                         WHEN 1 THEN '1' ELSE '' END,
      IsLastMonth    = CASE ROW_NUMBER() OVER (PARTITION BY src.y ORDER BY src.m DESC, src.d) 
                         WHEN 1 THEN '1' ELSE '' END
  FROM
  (
    SELECT d, 
           m   = MONTH(d), 
           y   = YEAR(d), 
           wd  = DATEPART(WEEKDAY, d),
           md  = DATEPART(DAY, d), 
           eom = EOMONTH(d)
    FROM
    (
      SELECT d = DATEADD(DAY, rn-1, @firstDate)
      FROM
      (
        SELECT rn = ROW_NUMBER() OVER (ORDER BY @@SPID)
          FROM STRING_SPLIT(REPLICATE(',', @numDays - 1), ',') AS x
      ) AS x
    ) AS y
  ) AS src
) AS html
ORDER BY TheYear DESC, TheMonth DESC, DayOfMonth;

The output with carriage returns and indentation added after the fact:

html
---------------------------------------------------------------------------
<yearname>2023</yearname>
<year>
  <month>
    <monthname>January</monthname>
    <days class="d1">
      <day>1</day>
      <day>2</day>
      ...
      <day class="future">30</day>
      <day class="future">31</day>
    </days>
  </month>
</year>
<yearname>2022</yearname>
<year>
  <month>
    <monthname>December</monthname>
    <days class="d5">
      <day>1</day>
      <day>2</day>
      ...
      <day>30</day>
      <day>31</day>
    </days>
  </month>
  <month>
    <monthname>November</monthname>
    <days class="d3">
      <day>1</day>
      <day>2</day>
      ...

That's not the prettiest output, but we can loop through the result, dump the HTML to the screen, and have the basic calendar spanning all the months in our event range.

Next, to hook in our actual events, we can outer join to the events table and add styling and <a> to the <day> cells with a match.

SELECT html = CASE WHEN IsLastMonth = '1' AND DayOfMonth = 1 
         THEN CONCAT('<yearname>', TheYear, '</yearname><year>') ELSE '' END
       + CASE DayOfMonth WHEN 1 THEN CONCAT('<month><monthname>',
         '<a href="/', cal.TheYear, '/', cal.TheMonth, '/">',
         DATENAME(MONTH, TheDay), '</a> (', COALESCE(mc.MonthCount, 0), 
         ')</monthname><days class="d', WeekdayOfFirst, '">') 
         ELSE '' END + CONCAT('<day', 
           CASE WHEN TheDay > GETDATE() THEN ' class="future"' END,
           CASE WHEN e.EventDate IS NOT NULL THEN 
           CONCAT(' class="on"><a href="/', cal.TheYear, '/', cal.TheMonth, '/', 
           DATEPART(DAY, cal.TheDay), '/"') END, '>', DayOfMonth, '</a></day>')
       + CASE IsLastOfMonth WHEN '1' THEN '</days></month>' ELSE '' END
       + CASE WHEN IsLastOfMonth = '1' AND IsFirstMonth = '1' 
               THEN '</year>' ELSE '' END
FROM
(
  SELECT TheDay      = src.d, 
      TheMonth       = src.m,
      TheYear        = src.y,
      DayOfMonth     = src.md, 
      WeekdayOfFirst = CONCAT(CASE src.md WHEN 1 THEN
             COALESCE(NULLIF((src.wd + @@DATEFIRST) % 7, 0), 7)
           END,''),
      IsLastOfMonth  = CASE WHEN src.eom = src.d THEN '1' ELSE '' END,
      IsFirstMonth   = CASE ROW_NUMBER() OVER (PARTITION BY src.y ORDER BY src.m, src.d DESC) 
                         WHEN 1 THEN '1' ELSE '' END,
      IsLastMonth    = CASE ROW_NUMBER() OVER (PARTITION BY src.y ORDER BY src.m DESC, src.d) 
                         WHEN 1 THEN '1' ELSE '' END
  FROM
  (
    SELECT d, 
           m   = MONTH(d), 
           y   = YEAR(d), 
           wd  = DATEPART(WEEKDAY, d),
           md  = DATEPART(DAY, d), 
           eom = EOMONTH(d)
    FROM
    (
      SELECT d = DATEADD(DAY, rn-1, @firstDate)
      FROM
      (
        SELECT rn = ROW_NUMBER() OVER (ORDER BY @@SPID)
          FROM STRING_SPLIT(REPLICATE(',', @numDays - 1), ',') AS x
      ) AS x
    ) AS y
  ) AS src
) AS cal
LEFT OUTER JOIN 
(
  SELECT EventDate = CONVERT(date, EventDateTime)
    FROM dbo.Events AS e
    GROUP BY CONVERT(date, EventDateTime)
) AS e
ON e.EventDate = cal.TheDay
LEFT OUTER JOIN 
(
  SELECT TheMonth = DATEFROMPARTS(YEAR(EventDateTime), MONTH(EventDateTime), 1),
    MonthCount = COUNT(*)
  FROM dbo.Events
  GROUP BY YEAR(EventDateTime), MONTH(EventDateTime)
) AS mc ON mc.TheMonth = cal.TheDay
ORDER BY cal.TheYear DESC, cal.TheMonth DESC, cal.DayOfMonth;

A little ugly, sure, but it can be very handy to produce this from a central location instead of processing all the conditionals on the client side (especially when multiple clients are involved). Here is some of the output (with, again, formatting added after the fact):

html
---------------------------------------------------
<yearname>2023</yearname>
  <year>
    <month>
      <monthname>
        <a href="/2023/1/">January</a> (2)
      </monthname>
      <days class="d1">
        <day>1</a></day>
        <day>2</a></day>
        ...
        <day>9</a></day>
        <day class="on">
          <a href="/2023/1/10/">10</a>
        </day>
        <day>11</a></day>
        ...
        <day class="future">31</a></day>
      </days>
    </month>
  </year>
  <yearname>2022</yearname>
  <year>
    <month>
      <monthname>
        <a href="/2022/12/">December</a> (6)
      </monthname>
      <days class="d5">
        <day>1</a></day>
        <day>2</a></day>
        ...
        <day>27</a></day>
        <day class="on">
          <a href="/2022/12/28/">28</a>
        </day>
        <day>29</a></day>
        <day>30</a></day>
        <day>31</a></day>
      </days>
    </month>
    <month>
      <monthname>
        <a href="/2022/11/">November</a> (7)
      </monthname>
      <days class="d3">
        <day>1</a></day>
        <day>2</a></day>
        ...

Attached is a working example including the full SQL script, more sample data, and formatting niceties:

This assumes your post or event archives are accessible via simple y/m and y/m/d URLs (like WordPress), but it should be trivial to adjust the URL patterns if you use a different format.

It also assumes events don't exist in the future; otherwise, you will need to make minor adjustments to allow both future and on CSS classes to coexist.

Finally, it assumes your event data is stored in SQL Server. I originally intended to show the equivalent query in MySQL since many of you who use WordPress are tied to the PHP/MySQL stack. The queries here will mostly work in MySQL 8.0, I think, but many hosts are still on older versions (e.g., 5.7), which lack many enhancements like CTEs and ROW_NUMBER(). So maybe I could write a port in a future tip.

Technically, this calendar is static until you publish a new post or move into a new month, so you can generate it once and only rebuild it when those events happen. But you also need to consider when you remove a post or change its publication date and that you’ll need to remember to generate a new grid every month. You could at least store the pre-joined data as a sort of calendar table, but perhaps something else I'll leave for a future tip.

Next Steps

Do something interesting with your archives page! Once you have the basic structure down, CSS is a very powerful way to make these calendar widgets more interesting and engaging. See the following tips for other ideas for managing calendar-related data:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-02-07

Comments For This Article

















get free sql tips
agree to terms