This blog will no longer be updated.

New content is available on my new blog

November 2008 - Posts - Piotr Rodak

November 2008 - Posts

Numbers table

I haven't been here for a while.. Quite busy time in my work, and also a bit of laziness, let's be honest ;)

There are a few things that I came across druring last few months. I will try to write about them in following posts.

Today I would like to write about Numbers or Tally table. The idea of having a table that contains only numbers and is used in various scenarios is not entirely new. Jeff Moden  wrote an excellent article that contains a few ideas about how to use the tally table. These are really only a few samples and I would like to know about other implementations. I personally used one of the ideas from the article to implement string split functionality for parameters passed as CSV arrays. You may like them or not, but in some cases it is still much better to pass such a list than to do some twisted programming on DAL or business layer.

So, the split string is quite same as in Jeff's article. I wrapped the functionality in UDF that returns table. The code requires that the list of values begins and ends with commas. I have lists of values that do not follow this requirement. Since I wanted to have single statement UDF and also pretty simple and clean way of calling it, I decided to use CTE to modify the parameter value so it is suitable for the split string code:

CREATE function [Admin].[fnListToTable](@list varchar(max), @separator varchar(10))
returns table
as
return
(
        --splits list of strings into a table. Uses Admin.tTally as indexer.
        with ParamCte(GroupIDs) as
        (select @separator + @list + @separator)

                SELECT SUBSTRING(GroupIDs,Number+1,CHARINDEX(@separator,GroupIDs,Number+1)-Number-1) Field
                        FROM Admin.tTally, ParamCte
                        WHERE Number < LEN(@list) 
                        and SUBSTRING(GroupIDs,Number,1) = @separator
                       
)

 The other application for tally table I found very useful is to enable decoding of bit masks. I have a table that stores some simple scheduling information.

 
CREATE TABLE [Control].[tFeedSchedule](
        [ScheduleId] [int] IDENTITY(1,1) NOT NULL,
        [FeedId] [int] NOT NULL,
        [DaysOfWeek] [tinyint] NOT NULL,
        [WindowOpen] [smallint] NOT NULL,
        [WindowClose] [smallint] NOT NULL,
 CONSTRAINT [PK_tFeedSchedule] PRIMARY KEY CLUSTERED
(
        [ScheduleId] ASC
)

)

 The DaysOfWeek column contains days of week when certain event is due to occur and WindowOpen and WindowClose numbers of minutes from midnight. Days of week are encoded as bit flags starting from Monday at 00000001 binary and ending at Sunday at 01000000 binary. There are some functions in my database that return real dates steming from the schedules defined in the above table.

The function below is one of such functions: It returns table with time 'windows' for specific feeds to come in week timeframe.

CREATE function [Control].[fnGetScheduleDaysForFeed](@FeedID int = null)
returns table
as
return
(
        --Returns list of days feed is scheduled to run in with their time windows
        select FeedID, case when @@datefirst = 1 then Number else Number + 1 end as [DayOfWeek], WindowOpen WindowOpenMinutes, WindowClose WindowCloseMinutes,

        convert(varchar, Dateadd(minute, WindowOpen, DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))), 108) WindowOpenTime,

        convert(varchar, Dateadd(minute, WindowClose, DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))), 108) WindowCloseTime,

        case when WindowOpen > WindowClose then 1 else 0 end CrossMidnight
 from Control.tFeedSchedule a inner join Admin.tTally b
        on a.DaysOfWeek & power(2, Number-1) <> 0
        where Number < 8 and (@FeedID is null or a.FeedID = @FeedID)
)

 There are many more possible applications of numbers table. I wonder, if it would be useful to create a library of such code snippets or ideas? If you know links to such resources, please put them in comments.