In the previous two articles of this series, we’ve discussed how to create a report and how to use built-in date and time functions. Today, we’ll join these two together and analyze some interesting queries we could use while creating more complex SQL Server reports where date and time functions are used to create reporting categories.
Data model and some general thoughts
We’ll use the same data model we’re using throughout this series. Since we’ll be mainly focused on how to generate date (time) reporting categories, we’ll be using data from only one table and that is the call table.
The main idea is to create several queries that could be used as they are in this article or easily tweaked to meet the requirements defined for the specific SQL Server report. We’ll use the statements and functions we’ve previously mentioned, but we’ll also introduce some new concepts in this article. These new concepts shall be covered only in the amount needed to understand SQL statements from this article, and we’ll talk more about them later in the series.
Creating a date range for a SQL Server report (list all dates in a given month)
One of the most common reporting requirements is to generate a report for the date range – including all dates in that range. E.g. let’s assume we want to list all calls in the given month. We want to have each date in that month together with the number of calls started on that date.
You could suppose we can easily take calls from the call table and group them by date. And that’s OK. But the problem is that we may lack calls for some dates and for such dates we wouldn’t have a row in the final result. And we want to have this row present with the value of 0 (not only that the report looks nicer that way, but we also know that row isn’t missing as the result of an error, etc.).
To achieve that, we’ll need to create a list of all dates and then join that list on the query with the reporting data (the one selecting data from the call table).
We could create a list of dates using the set of commands as presented below. Let’s take a look at these commands as well as their output.
|
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 |
-- dates in range -- declaring all DATE variables we'll use DECLARE @date DATE; DECLARE @start_date DATE; DECLARE @end_date DATE; DECLARE @loop_date DATE; -- declaring a table variable DECLARE @dates TABLE (date DATE); -- setting the first and the last date in the month given by date SET @date = '2020/05/12'; SET @start_date = DATEFROMPARTS(YEAR(@date ), MONTH(@date ), '01'); SET @end_date = EOMONTH(@date); -- check dates SELECT @date AS cur_date, @start_date AS first_date, @end_date AS last_date; -- populating a table (variable) with all dates in a given month SET @loop_date = @start_date; WHILE @loop_date <= @end_date BEGIN INSERT INTO @dates(date) VALUES (@loop_date); SET @loop_date = DATEADD(DAY, 1, @loop_date); END; -- selecting report dates SELECT * FROM @dates; |
These are some important things I would like to point out related to the code presented above. I’ll start from the end, and that is the final result. Two queries returned two results:
- The first query returned 3 values cur_date (given date), first_date (the first date in a month same as a month of the given date), and last_date (the last date in a month same as a month of the given date). This query was used only to show that we got the first and the last date in the month from the original date. Its’ result was used for the second query
- The second query returned all dates in the given month. Please notice that besides these dates we don’t have any other data
Now we’ll explain what we did to achieve this. Still, before we do it so, let’s mention a few new concepts we’ve used:
- DECLARE @date DATE; declares a variable of the DATE type. Later we’ll assign value to this variable – SET @date = ‘2020/05/12’;, and use it in the code where needed. Variables are used (in databases, but also in programming) as a place to store values you’ll need later. In SQL Server (and generally speaking) you could define variables of any available data type used for columns
- DECLARE @dates TABLE (date DATE); It is also possible to define the TABLE type variable. This variable shall be used to store a complete table, and can be used as a standard table – you can insert new data, or select from this variable (that is what we’ll do in the code), or perform other table-related operations
- We’ve used the WHILE loop. In programming, loops are used to execute a certain code as many times as loop condition is true. WHILE loop shall execute while the condition stands – that could be 0 times, only once, multiple times (the most common situation), or even an infinite number of times (this is usually the result of an error)
And this is what we did to achieve the result:
- First, we’ve defined a desired date -> SET @date = ‘2020/05/12’. This is the date in the month where we want to have all dates in a list
- With these two statements, we’ve defined the first and the last date in the given month -> SET @start_date = DATEFROMPARTS(YEAR(@date ), MONTH(@date ), ’01’); SET @end_date = EOMONTH(@date); For date and time functions, please check Learn SQL: SQL Server date and time functions article
- We’ve used the WHILE loop to go from the start date until the end date and inserted each date 1 by 1 in the table variable (INSERT INTO @dates(date) VALUES (@loop_date);). Please notice that we’ve increased date by 1 in each step/pass of the loop (SET @loop_date = DATEADD(DAY, 1, @loop_date);)
- In the end, we’ve selected from the table variable to get the complete list
The result of all of this is that for any given date, we can list all dates in that month from first to last. Later we could join these dates to actual report data and include in the report even dates without related reporting data.
Also, you could easily tweak this code (change the way how the @start_date and the @end_date are calculated) and list all dates e.g. in the given week; starting 20 days before a given date and ending 20 days after; in the given year, etc.
JOIN a date range and the report data
In the previous step, we’ve created a list of reporting dates we want to have present in the SQL Server report. Please notice that while creating this list we haven’t queried any database table. Now, we’ll join this list to the data from our database. The first thing to do is to check what is in the call table.
|
1 |
SELECT * FROM call; |
We can conclude we have 10 calls and all of them were made on 2020-01-11. We’ll use the code from the previous section of this article and combine it with the call table to create an actual report.
|
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 |
-- select number of calls per day in the given month DECLARE @date DATE; DECLARE @start_date DATE; DECLARE @end_date DATE; DECLARE @loop_date DATE; -- declaring a table variable DECLARE @dates TABLE (date DATE); -- setting the first and the last date in the month given by date SET @date = '2020/01/12'; SET @start_date = DATEFROMPARTS(YEAR(@date ), MONTH(@date ), '01'); SET @end_date = EOMONTH(@date); -- populating a table (variable) with all dates in a given month SET @loop_date = @start_date; WHILE @loop_date <= @end_date BEGIN INSERT INTO @dates(date) VALUES (@loop_date); SET @loop_date = DATEADD(DAY, 1, @loop_date); END; SELECT d.date, COUNT(call.id) AS calls FROM @dates d LEFT JOIN call ON d.date = CAST(call.start_time AS DATE) GROUP BY d.date; |
Please notice two things:
- We have the same list of dates in the 1st column, but also a number of calls performed on each day in the 2nd column (all of them are 0 except 10 calls on 2020-01-11)
- In the last query (SELECT), we’ve used LEFT JOIN to have all the dates present in the final report, COUNT(call.id) to count ids and not rows (counting rows would return 1 for each day where we have 0), used an alias name for the table variable – FROM @dates d, and casted/converted start time to DATE (it is originally DATETIME) – CAST(call.start_time AS DATE)
Creating a datetime range in minutes (as a reporting category)
So far, we’ve only used dates as reporting categories. That is fine if we want our SQL Server report granulated on a daily level. But what to do if we want to create a report with different time intervals (hours, minutes, seconds, etc.)? Let’s first take a look at the code below.
|
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 38 39 40 41 42 43 |
-- number of calls with predefined start time, end time and interval DECLARE @start_time DATETIME; -- starting from here DECLARE @end_time DATETIME; -- until the time is under this value DECLARE @interval CHAR(3); -- interval definition (e.g. day, minute etc.) DECLARE @increment INT; -- interval increment DECLARE @loop_time DATETIME; -- variable used in the loop DECLARE @times TABLE(start_time DATETIME, end_time DATETIME); SET @start_time = '2020-01-11 09:00:00'; SET @end_time = '2020-01-11 10:00:00'; SET @interval = 'MI'; SET @increment = 10; SET @loop_time = @start_time; WHILE @loop_time < @end_time BEGIN IF @interval = 'yy' SET @loop_time = DATEADD(yy, @increment, @loop_time); -- year IF @interval = 'qq' SET @loop_time = DATEADD(qq, @increment, @loop_time); -- quarter IF @interval = 'mm' SET @loop_time = DATEADD(mm, @increment, @loop_time); -- month IF @interval = 'dy' SET @loop_time = DATEADD(dy, @increment, @loop_time); -- day of year IF @interval = 'dd' SET @loop_time = DATEADD(dd, @increment, @loop_time); -- day IF @interval = 'wk' SET @loop_time = DATEADD(wk, @increment, @loop_time); -- week IF @interval = 'dw' SET @loop_time = DATEADD(dw, @increment, @loop_time); -- weekday IF @interval = 'hh' SET @loop_time = DATEADD(hh, @increment, @loop_time); -- hour IF @interval = 'mi' SET @loop_time = DATEADD(mi, @increment, @loop_time); -- minute IF @interval = 'ss' SET @loop_time = DATEADD(ss, @increment, @loop_time); -- second IF @interval = 'ms' SET @loop_time = DATEADD(ms, @increment, @loop_time); -- millisecond IF @interval = 'mcs' SET @loop_time = DATEADD(mcs, @increment, @loop_time); -- microsecond IF @interval = 'ns' SET @loop_time = DATEADD(ns, @increment, @loop_time); -- nanosecond INSERT INTO @times(start_time, end_time) VALUES (@start_time, @loop_time); SET @start_time = @loop_time; END; SELECT t.start_time, t.end_time, COUNT(call.id) AS calls FROM @times t LEFT JOIN call ON t.start_time < call.start_time AND call.start_time <= t.end_time GROUP BY t.start_time, t.end_time; |
The output of this report are 10 minutes time intervals on 2020-01-11, and the number of calls started during these intervals. Let’s see how we achieved that.
The idea of the code above is to be able to define the start time and the end time of our SQL Server report, and intervals. We’ll define intervals by time unit and the increment. In our example time units are minutes and the increment value is set to 10. Let’s quickly analyze our code:
- DECLARE @start_time DATETIME; and DECLARE @end_time DATETIME; – are variables where we’ll store/define the start time and the end time of our SQL Server report
- DECLARE @interval CHAR(3); – is used to store a 2 or 3 letters code defining the interval unit (minute, second, hour, etc.)
- DECLARE @increment INT; – is used to store the number of units per each interval
- We’ve again used the WHILE loop to loop from start time to end time. At each step of the loop, we’ve checked the interval defined and added interval accordingly, inserted one row in the table variable, and increased value for the next loop pass
- The last statement is the SELECT statement using the table variable and the call table, to count the number of calls in the intervals defined in the table @times
It’s worth noticing that you can easily change the interval unit and increment and adjust this code accordingly to your reporting needs.
When to use these queries (in SQL Server reports)
I’ve been many times in situations where I’ve needed to create reports for a certain time period. In case you need to do that, you can use the queries above to create a date/datetime categories for your SQL Server report. Of course, you’ll need to tweak queries a little and add your reporting tables (SELECTs) but that should work perfectly.
Table of contents
His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...
You can find him on LinkedIn
View all posts by Emil Drkusic
- Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
- Learn SQL: Dynamic SQL - March 3, 2021
- Learn SQL: SQL Injection - November 2, 2020