SQL Calendar Table Simplified using a Table and a View

Problem

In my last tip, I showed how to use T-SQL to generate HTML for fancy calendar visuals overlaid with event data from another table. As an extension of that tip, let’s now look at simplifying parts of that query by caching the date information in a calendar table to streamline the outer queries and avoid complications caused by different DATEFIRST settings.

Solution

Let’s start with a simple calendar table in SQL Server. All we need in the table is a list of dates since everything else could be derived. One benefit to doing all the work upfront is that the queries we write later don’t need to deal with non-standard DATEFIRST settings. Reviewing the example in the previous tip, this is the structure we’ll need:

CREATE TABLE dbo.EventCalendar
(
    TheDate        date,
    TheMonth       tinyint,
    TheYear        smallint,
    TheDay         tinyint,
    WeekdayOfFirst tinyint,
    IsLastOfMonth  bit,
    IsFirstMonth   bit,
    IsLastMonth    bit,
    CONSTRAINT PK_EventCalendar
      PRIMARY KEY CLUSTERED 
      (
        TheYear  DESC, 
        TheMonth DESC,
        TheDay
      )
);

Now, to populate it. Let's forget the specific data in our events table for now, though we can keep in mind how far back our data generally goes. My posts go back to December 2006, so I'll start the date range on the first of that month.

And instead of worrying about today's date, or the last event in the table, we can go forward to some point in the future (since we will continue adding events in future months). The end of this decade should do it. The "last month" represented by an event in the table at any given time will be a moving target, so we won't bother storing that and instead rely on calculating that at runtime.

This specific date range exposes a weakness in the REPLICATE solution I suggested to manufacture a date range. By default, REPLICATE maxes out at 8,000 characters, producing a maximum of 8,001 rows. We can overcome the 8k limit by explicitly converting the replicated character to varchar(max) (or assigning a variable declared as such).

Here is the code to populate our table, given these factors:

DECLARE @firstDate date = '20061201',
        @lastDate  date = '20291231',
        @comma     varchar(max) = ',';
INSERT dbo.EventCalendar
(
  TheDate,
  TheMonth,
  TheYear,
  TheDay,
  WeekDayOfFirst,
  IsLastOfMonth,
  IsFirstMonth,
  IsLastMonth
)
SELECT 
  TheDate, 
  TheMonth,
  TheYear,
  TheDay, 
  WeekdayOfFirst = CASE WHEN TheDay = 1 THEN DayOfWeek ELSE NULL END,
  IsLastOfMonth  = CASE WHEN LastDayOfMonth = TheDate THEN 1 ELSE 0 END,
  IsFirstMonth   = CASE ROW_NUMBER() OVER 
                   (PARTITION BY TheYear ORDER BY TheMonth ASC, TheDay DESC)
                   WHEN 1 THEN 1 ELSE 0 END,
  IsLastMonth    = CASE ROW_NUMBER() OVER 
                   (PARTITION BY TheYear ORDER BY TheMonth DESC, TheDay ASC) 
                   WHEN 1 THEN 1 ELSE 0 END
FROM
(
  SELECT TheDate        = d, 
         TheMonth       = DATEPART(MONTH,   d), 
         TheYear        = DATEPART(YEAR,    d), 
         DayOfWeek      = DATEPART(WEEKDAY, d),
         TheDay         = DATEPART(DAY,     d), 
         LastDayOfMonth = EOMONTH(d)
  FROM
  (
    SELECT d = DATEADD(DAY, RowNum - 1, @firstDate)
    FROM
    (
      SELECT RowNum = ROW_NUMBER() OVER (ORDER BY @@SPID)
        FROM STRING_SPLIT
        (
          REPLICATE(@comma, DATEDIFF(DAY, @firstDate, @lastDate)), 
          @comma
        ) AS Numbers
    ) AS Days
  ) AS DatesWithInfo
) AS FinalSource;

First, I populated this table with November 2022 through March 2023 to demonstrate how the data in the table will help us build the eventual HTML and CSS we require (including years where only some months are represented). If the day is the first of the month, we need to add a <month> tag (along with <days></code>); similarly, when it is the last day of the month, we need to close it with <code></days></month>. When it is the first, we also need to determine which class will be used (which correlates to which grid column the first day goes in). First and last months are kind of backward here, but when it is “the last month of the year” (which doesn’t necessarily mean December), that’s when we need the opening <year> tag because we’re displaying months in reverse order. And when it is “the first month of the year” (not necessarily January), that’s when we “close” the year, so we need the closing </year> tag.

To generate the HTML for all the months in the range, our query is much simpler than before. The only added complexity in the SELECT clause is the consideration for the case where the current month is the last month of this year, and it’s not yet December. This could also be accomplished in other ways, like with a row number from the source.

SELECT html = CASE WHEN TheDay = 1  AND (IsLastMonth = 1 OR 
                   (TheMonth < 12 AND TheYear = DATEPART(YEAR, GETDATE()))) 
    THEN CONCAT('<yearname>', TheYear, '</yearname><year>') ELSE '' END
  + CASE TheDay WHEN 1 THEN CONCAT('<month><monthname>', 
         DATENAME(MONTH, TheDate), '</monthname><days class="d',
         WeekdayOfFirst, '">') ELSE '' END
  + CONCAT('<day', CASE WHEN TheDate > GETDATE() THEN ' class="future"' END,
      '>', TheDay, '</day>')
  + CASE IsLastOfMonth WHEN 1 THEN '</days></month>' ELSE '' END
  + CASE WHEN IsLastOfMonth = 1 AND IsFirstMonth = 1 
    THEN '</year>' ELSE '' END
FROM dbo.EventCalendar
WHERE TheDate <= EOMONTH(GETDATE())
ORDER BY TheYear DESC, TheMonth DESC, TheDay;

That said, that’s a big set of code to carry forth into other queries. I would probably simplify this further by using a view both to hide away a lot of that conditional logic and to make it more self-documenting. For example:

CREATE VIEW dbo.vCalendarSource
AS
  SELECT TheDate, TheYear, TheMonth,
         ym = CONCAT('<a href="/', TheYear, '/', TheMonth, '/'),
         RowOpen = CASE WHEN TheDay = 1 
                   AND
                   (
                     IsLastMonth = 1 
                     OR 
                     (
                       TheMonth < 12 AND 
                       TheYear = DATEPART(YEAR, GETDATE())
                     )
                   )
                   THEN CONCAT
                        (
                          '<yearname>', 
                          TheYear, 
                          '</yearname><year>'
                        ) 
                   ELSE '' END,
         MonthOpen = CASE TheDay WHEN 1 
                     THEN '<month><monthname>' 
                     ELSE '' END,
         -- leave an opening here to make the month clickable
         MonthName = DATENAME(MONTH, TheDate),
         MonthClose = CASE TheDay WHEN 1 
                      THEN CONCAT
                           (
                             '</monthname><days class="d',
                             WeekdayOfFirst, 
                             '">'
                           )
                      ELSE '' END,
         DayOpen = CONCAT
                   (
                     '<day class="', 
                     CASE WHEN TheDate > GETDATE() 
                     THEN 'future ' END
                   ),
        -- leave an opening here for an additional class
        -- and to add an <a> making the day clickable
        TheDay,
        RowClose = CONCAT
        (
          '</day>',
          CASE IsLastOfMonth WHEN 1 
          THEN '</days></month>' END,
          CASE WHEN IsLastOfMonth = 1 AND IsFirstMonth = 1 
          THEN '</year>' END
        )
  FROM dbo.EventCalendar
  WHERE TheDate <= EOMONTH(GETDATE());

That is also a big, ugly piece of code, but it abstracts away some of the complexity of building HTML when joining to any event data source. Now our query that combines the calendar overlaid with event data – which still needs to perform some conditionals based on event data – can look like this:

SELECT html = CONCAT
(
  cal.RowOpen, 
  cal.MonthOpen,
  CASE WHEN aggM.MonthCount IS NOT NULL 
       THEN CONCAT (cal.ym, '">', cal.MonthName, 
            '</a> (',  aggM.MonthCount, ')' ) 
       WHEN cal.TheDay = 1 THEN cal.MonthName END,
  cal.MonthClose, 
  cal.DayOpen,
  CASE WHEN aggD.d IS NOT NULL
    THEN CONCAT (' on">', cal.ym, cal.TheDay, 
           '/">', cal.TheDay, '</a>')
    ELSE CONCAT('">', cal.TheDay) END,
  cal.RowClose
)
FROM dbo.vCalendarSource AS cal
LEFT OUTER JOIN
(
  SELECT d = CONVERT(date, EventDateTime)
    FROM dbo.Events 
    GROUP BY CONVERT(date, EventDateTime)
) AS aggD ON aggD.d = cal.TheDate
LEFT OUTER JOIN 
(
  SELECT m = DATEFROMPARTS(YEAR(EventDateTime), MONTH(EventDateTime), 1),
    MonthCount = COUNT(*)
  FROM dbo.Events
  GROUP BY YEAR(EventDateTime), MONTH(EventDateTime)
) AS aggM ON aggM.m = cal.TheDate
ORDER BY cal.TheYear DESC, cal.TheMonth DESC, cal.TheDay;

As before, this produces HTML that you can then massage using CSS into this:

Example of a web page showing HTML output

And you can make this your own. With slightly different CSS, the exact same HTML can produce just about anything you could imagine:

I certainly made it my own; in late January, I implemented a variation of this code (after translating it to MySQL 5.7 syntax and changing up some of the stylings) on my blog: see it in action.

Next Steps

I initially envisioned this for blog posts, but I think there are many other applications where visualizing data on a calendar can help observe patterns – you could display backup or other job failures from msdb, failover events from cluster logs, patching from event logs, or any past or future events from your tables. If you’re tracking it in a table, you can make it both useful and pretty on an HTML page.

Also, see these related tips and other resources:

Resources

Community

Subscribe