About This Blog

Sunday, 22 May 2011

Creating Tally Tables using CTE in SQL Server 2005/2008

A Tally table is simply a table with a single column of very well indexed sequential values starting at any value of your requirement and going up to some predefined threshold. The starting value & the ending value should not be arbitrary. It should be as per the requirement in question.

There are n numbers of ways in which a Tally Table could be generated and the most obvious one is by making use of loops.

Here, I would demonstrate how we could generate a Tally Table using CTE. We will make use of the recursive nature of CTEs to get our job done.

Generating a sequence of numbers from 1 to 20

DECLARE @Max ASINT= 20

;WITH CTE AS (

SELECT 1 Num

UNIONALL

SELECT Num + 1 FROM CTE WHERE Num < @Max

)

SELECT*FROM CTE

Generating a sequence of Dates starting with the current date & going till next 20 days

DECLARE @MaxDate ASDATETIME=GETDATE()+ 20

;WITH CTE AS (

SELECTGETDATE() Dates

UNIONALL

SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate

)

SELECT*FROM CTE

I love this way of generating the Tally Tables as it’s just too simple and on the fly.

Search This Blog

About Me

Experience of more than 11+ years in developing & designing Client-Server based applications, smart client applications, web portals and small utility applications using Microsoft technologies.
Strong in designing the Application Architecture, providing out of box solutions & a very good trouble shooter.