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
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.