By: Aaron Bertrand | Updated: 2023-02-22 | Comments | Related: > Dates
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>); similarly, when it
is the last day of the month, we need to close it with </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:
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:
- Use SQL Server and CSS to Generate More Interesting Events Calendar in HTML
- Creating a date dimension or calendar table in SQL Server
- Using a calendar table in SQL Server – Part 1
- Calendar Table in SQL Server to Identify Date Gaps and Islands
- Create a Calendar Table in SQL Server to Optimize Scheduling Problems
- All SQL Server date tips
About the author
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-22
