SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calendar Tables


Calendar Tables

Author
Message
siva 20997
siva 20997
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 208
I have been doing this for few years now and was wondering if I was the only person doing it


It is good to see that many have come to the same logical reasoning

I also import and set the Public holidays of every year in to the system which becomes usefull for payroll type of applications
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16842 Visits: 3403
Worth mentioning that this technique works well on any data subject area where there is a defined set of answers.

For example, permutation/combination figures, significance tables.

I was quite proud of coming up with efficient formulae for handling nPr, nCr stuff in http://www.sqlservercentral.com/articles/Troubleshooting/75990/ but suffered the DOH! moment when someone pointed out I could actually store the values in a table instead!

LinkedIn Profile
www.simple-talk.com
Andre Guerreiro
Andre Guerreiro
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2281 Visits: 1515
Excellent article! Thank you.

Now I have a question for you more experienced guys and gals. :-D

If I use a tally table like the one below would that be alright? I'm
trying to use a single tally table for numbers and dates as well.


IF OBJECT_ID('dbo.Tally', 'U') IS NOT NULL
DROP TABLE dbo.Tally;
GO

DECLARE @StartDate DATETIME = '18501231';

SELECT Tab.Number,
@StartDate + Tab.Number AS Date,
MONTH(@StartDate + Tab.Number) AS Month,
YEAR(@StartDate + Tab.Number) AS Year
INTO dbo.Tally
FROM (SELECT TOP(100000)
ROW_NUMBER() OVER (ORDER BY C1.column_id)
FROM model.sys.columns AS C1
CROSS JOIN model.sys.columns AS C2
CROSS JOIN model.sys.columns AS C3) AS Tab(Number);

ALTER TABLE dbo.Tally
ALTER COLUMN Number INT NOT NULL;
GO

ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED(Number)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
FILLFACTOR = 100);
GO

CREATE NONCLUSTERED INDEX IX_Tally_Date ON dbo.Tally(Date)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
FILLFACTOR = 100);
GO

CREATE NONCLUSTERED INDEX IX_Tally_Year ON dbo.Tally(Year)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
FILLFACTOR = 100);
GO

EXEC sp_spaceused 'dbo.Tally';
-- Tally 100000 8512 KB 3328 KB 4432 KB 752 KB



Or should I just remove the Number column and calculate the ROW_NUMBER everytime I use the table? I'm trying to think performance-wise. Have a great weekend y'all!

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
michaelm-746562
michaelm-746562
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 19
Yes, I commonly use the yearmonth (YYYYMM) scenario. However, I use the fieldname TimeKey so as not to argue with the system about any reserved words. Additionally, I use an integer field in the table to allow queries easily to span segments that include differing years. The integer opens the door to arithmetic operations for query construction.

Example:
SequenceNumber TimeKey
11 201111
12 201112
13 201201
14 201212 & C.

In addition, I use the fields StartKey and StopKey (with a default 999999 on the second) as a handy way of identifying SCD lookups for historical data. This system is very useful when working with accounting systems where discreet monthly data is used.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216238 Visits: 41986
codebyo (2/25/2012)
Excellent article! Thank you.

Now I have a question for you more experienced guys and gals. :-D

If I use a tally table like the one below would that be alright? I'm
trying to use a single tally table for numbers and dates as well.


IF OBJECT_ID('dbo.Tally', 'U') IS NOT NULL
DROP TABLE dbo.Tally;
GO

DECLARE @StartDate DATETIME = '18501231';

SELECT Tab.Number,
@StartDate + Tab.Number AS Date,
MONTH(@StartDate + Tab.Number) AS Month,
YEAR(@StartDate + Tab.Number) AS Year
INTO dbo.Tally
FROM (SELECT TOP(100000)
ROW_NUMBER() OVER (ORDER BY C1.column_id)
FROM model.sys.columns AS C1
CROSS JOIN model.sys.columns AS C2
CROSS JOIN model.sys.columns AS C3) AS Tab(Number);

ALTER TABLE dbo.Tally
ALTER COLUMN Number INT NOT NULL;
GO

ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED(Number)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
FILLFACTOR = 100);
GO

CREATE NONCLUSTERED INDEX IX_Tally_Date ON dbo.Tally(Date)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
FILLFACTOR = 100);
GO

CREATE NONCLUSTERED INDEX IX_Tally_Year ON dbo.Tally(Year)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
FILLFACTOR = 100);
GO

EXEC sp_spaceused 'dbo.Tally';
-- Tally 100000 8512 KB 3328 KB 4432 KB 752 KB



Or should I just remove the Number column and calculate the ROW_NUMBER everytime I use the table? I'm trying to think performance-wise. Have a great weekend y'all!


Part of the "speed" factor of a Tally table is that it's densely packed with only the information it needs. Adding the columns you have will make it necessary to read at least twice as many pages to do any job that uses it. Further, you've also just increased the "in memory" size of the table when it does cache.

My recommendation is to never mix the Tally Table with a Calendar table of any type. It's not good for the Tally side of it all.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Andre Guerreiro
Andre Guerreiro
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2281 Visits: 1515
Jeff Moden (2/27/2012)
Part of the "speed" factor of a Tally table is that it's densely packed with only the information it needs. Adding the columns you have will make it necessary to read at least twice as many pages to do any job that uses it. Further, you've also just increased the "in memory" size of the table when it does cache.

My recommendation is to never mix the Tally Table with a Calendar table of any type. It's not good for the Tally side of it all.


I see what you mean. My tally/calendar table would behave just like an ordinary table being joined, right? No real gain there. Lesson learned. Thank you again, Jeff.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Rod Weir
Rod Weir
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 161
Great article and technique.

How about dates stored in UTC? How do you configure the date table then? Do you create a date table that is also in UTC? Does this even make a difference? UTC would be the same as the dates then, right?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216238 Visits: 41986
michaelm-746562 (2/27/2012)
Additionally, I use an integer field in the table to allow queries easily to span segments that include differing years. The integer opens the door to arithmetic operations for query construction.

Example:
SequenceNumber TimeKey
11 201111
12 201112
13 201201
14 201212 & C.


Sorry for the late response but I lost track of this thread. A recent post to it "found" it for me.

What kind of "arithmetic operations" have you actually used against that column and what do they do?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21722 Visits: 10652
Sorry for the late response... I'm just getting caught up on some newsletters where I've been too busy to read.

I think that you did a great job in this article, except in one place (and I'm somewhat surprised that Jeff didn't mention this).
So, in all of this work to build a calendar table to create faster, more efficient set-based queries, you end up using a loop instead of a set-based method to generate your table... here's a set-based method to generate the calendar table:

WITH Tens (N)      AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
Thousands (N) AS (SELECT t1.N FROM Tens t1, Tens t2, Tens t3),
Millions (N) AS (SELECT t1.N FROM Thousands t1, Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions),
Months (N, MonthStart) AS (SELECT TOP (25*12)
N, DATEADD(MONTH, N, '1999-12-01T00:00:00')
FROM Tally)
SELECT N,
MonthStart,
NextMonth = DATEADD(MONTH, 1, MonthStart),
MonthDescr = CONVERT(CHAR(3), caMonth.MonthName) + '-' +
RIGHT(CONVERT(CHAR(4), YEAR(MonthStart)),2),
caMonth.MonthName,
YearMonth = (YEAR(MonthStart)*100) + caMonth.MonthNbr,
caMonth.MonthNbr
FROM Months
CROSS APPLY (SELECT MonthNbr = DATEPART(MONTH, Months.MonthStart),
MonthName = DATENAME(MONTH, Months.MonthStart)) caMonth
ORDER BY Months.MonthStart;



You can use SET IDENTITY_INSERT to insert N into the Identity column. And to address the Y2K issue brought up (running out of dates), just change the "25" to 5000 to take it up to year 7000. Now, if queries based on this table are still in use then, I think we've got bigger problems to worry about... :-Dw00t

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216238 Visits: 41986
WayneS (6/23/2012)
I think that you did a great job in this article, except in one place (and I'm somewhat surprised that Jeff didn't mention this).


Including the WHILE loop, there are good many things about Todd's Calendar Table code that I would have someone change to pass a production code review but didn't mention because I didn't want to take any focus away from what Todd was trying to say in his good article. The point he's trying to get across is much more important.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search