Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Calendar Tables Expand / Collapse
Author
Message
Posted Friday, February 24, 2012 2:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, March 1, 2014 12:37 PM
Points: 75, 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
Post #1257218
Posted Friday, February 24, 2012 2:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 2,903, Visits: 1,820
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
Newbie on www.simple-talk.com
Post #1257228
Posted Saturday, February 25, 2012 8:14 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:33 AM
Points: 900, Visits: 1,489
Excellent article! Thank you.

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

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
Post #1257781
Posted Monday, February 27, 2012 11:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 9, 2012 9:43 AM
Points: 4, Visits: 18
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.

Post #1258443
Posted Monday, February 27, 2012 4:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 37,099, Visits: 31,649
codebyo (2/25/2012)
Excellent article! Thank you.

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

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1258572
Posted Monday, February 27, 2012 5:09 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:33 AM
Points: 900, Visits: 1,489
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
Post #1258578
Posted Sunday, March 25, 2012 1:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 3:51 PM
Points: 6, Visits: 155
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?
Post #1272324
Posted Sunday, March 25, 2012 9:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 37,099, Visits: 31,649
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1272350
Posted Saturday, June 23, 2012 9:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 6,604, Visits: 8,910
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...


Wayne
Microsoft Certified Master: SQL Server 2008
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
Post #1320320
Posted Sunday, June 24, 2012 1:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 37,099, Visits: 31,649
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1320382
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse