Tally Table: Generate Ranges in SQL Server without While

Photo by Austris Augusts on Unsplash

Tally table or counting table is characterized by consisting of a single column of numerical type with consecutive values. The column values remain unchanged and are used, for example, to generate sequences in SQL without using While statements. Some common operations are generating date ranges, finding character positions in text strings, or converting text sequences to rows.

In this post, you will learn how to:

  • Create the Tally table and how to load it with data.
  • Use the Tally table to generate ranges of data without iterations.

Let´s start, first, we will create the table and load the records to handle day generation operations with date ranges. The data will allow up to 3650 positions. Next, the query is executed.

CREATE DATABASE Demo
GO
USE Demo
go
SET NOCOUNT ON
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.TallyTable') AND type in (N'U'))
	DROP TABLE dbo.TallyTable
GO
CREATE TABLE dbo.TallyTable(ID INT NOT null, PRIMARY KEY(ID))
GO
DECLARE @I INT = 0 /* Can start at 0 0r 1*/
DECLARE @IMax INT = 365*10 /* Approximately 10 years*/
WHILE @I<=@IMax
	BEGIN
		INSERT INTO dbo.TallyTable VALUES (@I)
		SET @I=@I+1
	END
USE Demo
GO

Then, we proceed to create the table in which we will store the information corresponding to the periods that a person is staying in a hotel. This newly created table has been simplified so it has no normalized.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Hospedaje') AND type in (N'U'))
	DROP TABLE dbo.Hospedaje
GO
CREATE TABLE Hospedaje	(
		HospedajeId INT NOT NULL IDENTITY(1,1),
		Cliente VARCHAR(100) NOT NULL,
		FechaIngreso DATE NOT NULL,
		FechaEgreso DATE NOT NULL
	 )
INSERT INTO dbo.Hospedaje(
		  Cliente ,
          FechaIngreso ,
          FechaEgreso
        )
	SELECT 'PEKERMAN', ' 20140504', '20140507'

The following query shows how to generate the days contained in a date range with the tally table.

USE Demo
GO
SELECT *, DATEADD(DAY, tt.id, h.FechaIngreso) Day /* change tt.id to tt.id - 1 to start counting from 0 */
	FROM dbo.Hospedaje H
		INNER JOIN dbo.TallyTable TT
			ON DATEADD(DAY, tt.id, h.FechaIngreso)<= h.FechaEgreso /* change tt.id to tt.id - 1 to start counting from 0 */
	ORDER BY h.Cliente, tt.ID asc
Tally table

Conclusions

Finally, we have seen how to generate a sequence of numbers that can be used in our queries to facilitate operations with range calculations. Do you have other styles to make this type of query? Please let me know.


Leave a Reply

Your email address will not be published. Required fields are marked *