Emil Drkusic
SQL Server Reports - create reporting intervals - the data model we'll use in the article

Learn SQL: Create SQL Server reports using date and time functions

May 8, 2020 by

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.

SQL Server Reports - create reporting intervals - the data model we'll use in the article

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.

SQL example - all dates in May 2020

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.

All calls in the database table

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.

SQL example - all calls in January

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.

SQL Server Reports - 10 minute intervals

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

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query?
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions
Learn SQL: SQL-Related Jobs
Learn SQL: Non-Equi Joins in SQL Server

Emil Drkusic
Latest posts by Emil Drkusic (see all)
SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. 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

2,978 Views