Tally Table: Generar rangos en SQL Server sin While

Photo by Austris Augusts on Unsplash

Tally table o tabla de cuentas se caracteriza por constar de una sola columna de tipo numérico con valores consecutivos. Los valores de la columna permanecen inalterables y sirven, por ejemplo, para generar secuencias en SQL sin utilizar instruciones While. Algunas operaciones comunes son generar rangos fecha, encontrar posiciones de caracteres en cadenas de textos o convertir a filas secuencias de texto.

En esta publicación, aprenderá a:

  • Crear la tabla Tally y como cargarla de datos.
  • Utilizar la tabla Tally para generar rangos de datos sin iteraciones.

Para empezar, primero crearemos la tabla y cargaremos los registros para manejar operaciones de generación de días con rangos de fechas. Los datos permitiran hasta 3650 posiciones. Seguido, se ejecuta la consulta.

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 /* Puede iniciar en 0 ó 1 según se desee*/
DECLARE @IMax INT = 365*10 /*Para manejar operaciones de días con 10 años aproximadamente*/
WHILE @I<=@IMax
	BEGIN
		INSERT INTO dbo.TallyTable VALUES (@I)
		SET @I=@I+1
	END
USE Demo
GO

Luego procedemos a crear la tabla en la que almacenaremos la información correspondiente a los períodos que una persona esta hospedada en un hotel. Esta tabla recientemente creada ha sido simplificadas por tal no tiene normalización.

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'

En la siguiente consulta se aprecia como generar los días contenidos en un rango de fechas con la tabla de cuentas o tally table.

USE Demo
GO
SELECT *, DATEADD(DAY, tt.id, h.FechaIngreso) Dia /*cambie tt.id a tt.id - 1 para iniciar conteo desde 0*/
	FROM dbo.Hospedaje H
		INNER JOIN dbo.TallyTable TT
			ON DATEADD(DAY, tt.id, h.FechaIngreso)<= h.FechaEgreso /*cambie tt.id a tt.id - 1 para iniciar conteo desde 0*/
	ORDER BY h.Cliente, tt.ID asc
Tally table

Conclusiones

Finalmente, hemos visto como generar una secuencia de números que puede ser utilizada en nuestras consultas para facilitar operaciones con cálculos de rangos. ¿Tiene otros estilos para realizar este tipo de consultas? Por favor coméntanos.


Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *