SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Todd Fifield / Calendar Tables / Latest Posts

RE: Calendar Tables

Jeff Moden — Sun, 24 Jun 2012 13:14:49 GMT

[quote][b]WayneS (6/23/2012)[/b][hr]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).[/quote]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.

RE: Calendar Tables

WayneS — Sat, 23 Jun 2012 21:31:25 GMT

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:[code="sql"]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; [/code]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... :-D:w00t:

RE: Calendar Tables

Jeff Moden — Sun, 25 Mar 2012 09:09:54 GMT

[quote][b]michaelm-746562 (2/27/2012)[/b][hr]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:[u]SequenceNumber[/u] [u]TimeKey[/u]11 20111112 20111213 20120114 201212 & C.[/quote]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?

RE: Calendar Tables

Rod Weir — Sun, 25 Mar 2012 01:14:00 GMT

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?

RE: Calendar Tables

codebyo — Mon, 27 Feb 2012 17:09:40 GMT

[quote][b]Jeff Moden (2/27/2012)[/b][hr]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.[/quote]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.

RE: Calendar Tables

Jeff Moden — Mon, 27 Feb 2012 16:08:05 GMT

[quote][b]codebyo (2/25/2012)[/b][hr]Excellent article! Thank you.Now I have a question for you more experienced guys and gals. :-DIf I use a tally table like the one below would that be alright? I'mtrying to use a single tally table for numbers and dates as well.[code="sql"]IF OBJECT_ID('dbo.Tally', 'U') IS NOT NULL DROP TABLE dbo.Tally;GODECLARE @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;GOALTER 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);GOCREATE 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);GOCREATE 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);GOEXEC sp_spaceused 'dbo.Tally';-- Tally 100000 8512 KB 3328 KB 4432 KB 752 KB[/code]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![/quote]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.

RE: Calendar Tables

michaelm-746562 — Mon, 27 Feb 2012 11:50:41 GMT

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:[u]SequenceNumber[/u] [u]TimeKey[/u]11 20111112 20111213 20120114 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.

RE: Calendar Tables

codebyo — Sat, 25 Feb 2012 08:14:06 GMT

Excellent article! Thank you.Now I have a question for you more experienced guys and gals. :-DIf I use a tally table like the one below would that be alright? I'mtrying to use a single tally table for numbers and dates as well.[code="sql"]IF OBJECT_ID('dbo.Tally', 'U') IS NOT NULL DROP TABLE dbo.Tally;GODECLARE @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;GOALTER 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);GOCREATE 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);GOCREATE 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);GOEXEC sp_spaceused 'dbo.Tally';-- Tally 100000 8512 KB 3328 KB 4432 KB 752 KB[/code]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!

RE: Calendar Tables

David.Poole — Fri, 24 Feb 2012 02:20:16 GMT

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!

RE: Calendar Tables

siva 20997 — Fri, 24 Feb 2012 02:12:13 GMT

I have been doing this for few years now and was wondering if I was the only person doing itIt is good to see that many have come to the same logical reasoningI also import and set the Public holidays of every year in to the system which becomes usefull for payroll type of applications

RE: Calendar Tables

Toby Harman — Thu, 23 Feb 2012 23:45:31 GMT

You may want to consider using a DATENAME() instead of the cumbersome CASE statement. That would also allow some language variation dependant on the default language.

RE: Calendar Tables

Giraffe — Tue, 10 Aug 2010 21:17:16 GMT

Thanks for the article! As a relative newcomer to SQL I'd be intested in comments on using a UDF to generate a similar table of dates. below is one I've been using with parameters to set the date range and switch whether it returns days, months and years. Are there performance issues with this approach? set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo/* * PAREMETERS: @DatePart: 0 = Day, 1 = Month, 2 = Year * USE: SELECT * FROM dbo.fn_Generate_Dates_V2 ('2000-01-01', '2025-01-01', 0)*/CREATE FUNCTION [dbo].[fn_Generate_Dates_V2](@StartDate SmallDateTime, @EndDate SmallDateTime, @DatePart int = 0) RETURNS @returntable TABLE ( Date_Key int IDENTITY, Date_Value SmallDatetime, Last_Month smalldatetime, Next_Month smalldatetime, Day_Value int, Day_Name varchar (20), Month_Value int, Month_Name varchar(20), Year_Value int) ASBEGIN WHILE (@StartDate < @EndDate) BEGIN INSERT @returntable ( Date_Value, Last_Month, Next_Month, Day_Value, Day_Name, Month_Value, Month_Name, Year_Value) VALUES ( @StartDate, DateAdd(Month, -1, @StartDate), DateAdd(Month, 1, @StartDate), DatePart(Day, @StartDate), Datename(dw,@StartDate), DatePart(Month, @StartDate), Datename(Month, @StartDate), DatePart(yyyy, @StartDate) ) SELECT @StartDate = CASE @DatePart WHEN 1 THEN DateAdd(Month, 1, @StartDate) WHEN 2 THEN DateAdd(Year, 1, @StartDate) ELSE DateAdd(Day, 1, @StartDate) END END RETURNEND

RE: Calendar Tables

Jeff Moden — Sat, 31 Jul 2010 20:27:26 GMT

Looking at the "Tally" table code that someone else wrote, I can certainly see why it's so slow. No chance of the clustered index being used there!Thanks for the honorable mention (heh... I think ;-)), Todd. Considering how badly someone abused the Tally table, you've just got to know that I'll be doing a full blown code review there. :-PAlso, do you happen to have the link where that "Tally" code originated? I need to set things straight with the author of that code (BWAAA-HAAA!!! Hopefully, it wasn't me under the influence of cold medicine. :w00t::hehe:)

RE: Calendar Tables

Jeff Moden — Fri, 16 Jul 2010 12:56:08 GMT

Now [font="Arial Black"][i]there's[/i] [/font]an idea for an article... comparing the pros and cons of using a DateTime dates compared to INT dates.

RE: Calendar Tables

Jason-299789 — Fri, 16 Jul 2010 02:15:58 GMT

[quote][b]Samuel Vella (7/15/2010)[/b][hr]There is logic behind not using the date as the key to the date dimension... it stops developers from falling into the easy trap of using the key as the date and not bothering to join onto the date dimension table![/quote]True, however you do loose the ability to maintain a sequential set of date information, as its possible that 02-Jan-2011 is not the next key in sequence to 01-Jan-2011 thus it slows performance in not only the ETL process but also the reporting of the data. The idea behind a Key on a table is a unique reference, and time can never have more than one occurance thus it makes sense to use the date in a data warehouse as the key on both the date dimension and also on the Fact table as it cannot have more than one entry.You then only have to lookup to the date column to get any flags such as Public Holidays, Seasons, Financial Periods (Open, Closed, current), etc. One way to alieveate this is to convert the Datetime to an INT, however this has a few draw backs, the main one being due rounding when cast a date that has gone over Midday, it gets rounded up to the next day, but as long as you know the pitfalls its pretty easy to avoid.edit : dates wrong in the first paragraph

RE: Calendar Tables

SQLRNNR — Thu, 15 Jul 2010 18:57:37 GMT

Thanks for taking the time to put this together and share with us.

RE: Calendar Tables

Samuel Vella — Thu, 15 Jul 2010 14:56:27 GMT

There is logic behind not using the date as the key to the date dimension... it stops developers from falling into the easy trap of using the key as the date and not bothering to join onto the date dimension table!

RE: Calendar Tables

Bill Talada — Thu, 15 Jul 2010 14:53:51 GMT

The example doesn't really show off the capabilities of a "Time" table. I'm used to very wide tables that contain columns to help you count work days, holidays, fiscal days, gregorian, julian, and week days in any given time period by adding up the ones and zeros in a given column where there is one row per day. It also allows you to cross reference work day 145 with a calendar date, or to find which fiscal month it relates to.

RE: Calendar Tables

amenjonathan — Thu, 15 Jul 2010 12:33:50 GMT

Chiming in with my agreement with other BI/Warehouse guys, this is a 10 year old practice with time dimensions. It makes perfect sense in almost any system where rows are generated at a specific interval of time.As far as the type of key, I remember one instance were an int IDENTITY(1,1) key was the appropriate choice because our time dimension was at the hourly granularity, and we had to take into consideration DST. We had an extra hour in the fall with the exact same time stamp. In this regard, the auto-incrementing ID kept the two separated in the fact table as two unique time periods even though logically they were the exact same point in time. We could have solved this using a datetime field by using UTC, but since our system didn't care what the UTC was, we left the integer field in as ID.At that point if we wanted to constrain by time period, we would just put a join on D_Time, like I find happens in most queries anyway in a warehouse (between this month and that). So far warehouses I've worked with have not had a lot to gain out of using the datetime as a primary column, usually because I'm already joining on D_Time for a separate constraint or to get a time reference for the time I'm working with (ex: end of previous month when doing comparisons between today and previous month end).Another thing to consider about using datetime as key and as constraint is if you put any converts or dateparts on your datetime field in the where or join clause, as far as I know and have tested, SQL will not use the index associated with that field. So it is better for performance if you join on an appropriate field in the time dimension without changing the raw data in the field and also then putting an index on that column if it is used in many queries.From my experience, unless you're working with a very large chunk of time (greater than 5 years) and have a very tiny time granularity (less than an hour), a join on a time table really doesn't affect performance significantly, again assuming the appropriate indexes have been created.If anyone thinks to the contrary, please post. I've never learned so much I think I have nothing left to learn. =DCorrection: It will do an index scan rather than index seek, because it will convert every time using the function you've given it before it uses the index.

RE: Calendar Tables

David Data — Thu, 15 Jul 2010 11:06:11 GMT

A question for people re performance.As I said above, we use a date table, and as our significant dates are of type [date] (with no time-of-day), we can join [b]ON a.Datefield = b.Datefield[/b], which is fast.However, some new data will use [datetime] fields, where the time of day is not 00:00:00. We can match using BETWEEN or CONVERT(DATE,a.Datefield), but is that going to slow down the join? Should we add computed or pre-computed fields of type [date] to use in joins, or would this not gain anything? (Assume all fields being joined on are indexed.)

RE: Calendar Tables

scott.pletcher — Thu, 15 Jul 2010 10:49:05 GMT

I don't object to a calendar table per se, and a specific calendar table does have certain advantages over a tally table.But I'm not sure that big of a performance difference should necessarily be one of them.When using the tally table, I don't think it's necessary to use functions on the table column in the JOIN clause. That will automatically limit the usefulness of any index on that column.Couldn't we do something like this instead?:[code="sql"]...FROM Tally LEFT JOIN #Stay ON N <= 12 AND ArrivalDate >= @StartYear1 AND (ArrivalDate BETWEEN DATEADD(MM, N - 1, @StartYear1) AND DATEADD(MS, -3, DATEADD(MM, N, @StartYear1)) OR ArrivalDate BETWEEN DATEADD(MM, N - 1, @StartYear2) AND DATEADD(MS, -3, DATEADD(MM, N, @StartYear2)))GROUP BY ...[/code]I.e., put all the JOIN criteria in the JOIN and make sure we don't use functions on the ArrivalDate. I'm not sure the optimizer is sophisticated enough to recognize that it should be able to use the index here, but I think at least it gives it a chance :-) .I know for a single month I usually an index seek vs. a scan using this style of coding, but for the side-by-side month, I'm not sure.

RE: Calendar Tables

rchantler — Thu, 15 Jul 2010 10:14:40 GMT

We advocate and use the calendar table approach too. We deal with reams (tech term) of daily financial data and this approach makes many difficult things easier and some impossible things possible ;-)If not for the calendar table, we'd be recomputing date ranges, etc. to the nth degree. With the table, they're computed once (and referenced many times). It helps with the load.Thanks for the article.

RE: Calendar Tables

Tom Garth — Thu, 15 Jul 2010 09:39:04 GMT

Nice article. Thanks for the time and effort that you took to write it.

RE: Calendar Tables

Tom Williams-175034 — Thu, 15 Jul 2010 08:28:57 GMT

[quote][b]katesl (7/15/2010)[/b][hr]>>So it's OK to depend on a job that runs infrequently, but it's not OK to depend on a table that has to be maintained infrequently? Where's your DBA?[/quote]No, neither are OK, but I believe the former might just be safer than the latter. If the DBA leaves, and doesn't tell his replacement about this magic table of numbers, do we wait for the new DBA to find this table, and work out why it's there? I think you're marginally *less* likely to have a well tested job screw up, than have a DBA remember to infrequently remember to maintain that table.

RE: Calendar Tables

katesl — Thu, 15 Jul 2010 08:19:08 GMT

>>Here we've always been of the belief that you shouldn't have tables with date ranges, because tables get created and populated, the query in question works, and the table gets forgotten about. You come into work on Jan 2nd, and something has stopped working, because there are no valid dates in the table, and the query that relies on this has failed because no-one updated the table, or the people who know about the table have left.I understand about the performance benefit, and it makes sense. Perhaps there could be a recurring job that runs every Dec XXth, that adds to the contents of that table so that the table always holds at least a years extra data, for instance. That way, the table will never run out of dates.>>So it's OK to depend on a job that runs infrequently, but it's not OK to depend on a table that has to be maintained infrequently? Where's your DBA?

RE: Calendar Tables

GRScow — Thu, 15 Jul 2010 08:18:48 GMT

Good article on a key topic for db/warehouse developers like me. It seems that from a maintenance viewpoint, it would be much easier to define all of the variations on the main date as computed fields. Adding date formats and derived dates is as simple as adding a calculated field to the table.We have several significant data collection and reporting systems and various data sources which use various date formats (including mainframes where records are defined with YY fields and MM fields - text and int types, combined and split). Any date fields in a data table can be joined to the date table on the appropriate date field(s) and of course we always try to store the resulting records in a Sql native datetime field.Seems like this provides the performance benefits of joining to a table along with the super-simple maintenance - there is only one step on one field when adding months to the table. Cheers!

RE: Calendar Tables

awilbournsqlcentral — Thu, 15 Jul 2010 08:16:13 GMT

[quote][b]Victor Kirkpatrick (7/15/2010)[/b][hr]The key isn't random just because it's an identity, as long as when the records for the date table were created in the correct "month" order. And no, it isn't dangerous.Nice article. I already have a numbers table that I use regularly, maybe it's time for a dates table like this.As for for Tom's comments concerning staying away from tables like this where you are worried about not have data for a given year. Relax. You could load this table through 2500 and it would still easily reside in memory.[/quote]As a BI developer that is dealing with a Date Dimension that used such a means to create the key, it is a royal pain when you are trying to sift through millions of rows of data and where you are looking for a certain date range. Rather than having to go look up what value 5234 is, it would be nice to have the value be the date. This also helps developers if they want certain data and know the date, but only need to query on the date itself, so no need to join the extra table to do so, only need it if you need more dimension data. So a date table would be like 20100715 for today, something very easy for application developer to format and pass along.Anyhow, I have been on the dev and DBA side and find that if you can help make your normalized data make a little since (with dates at least) it helps make finding issues in the data easier, because is it the app or the data is always the question.

RE: Calendar Tables

Daniel Bowlin — Thu, 15 Jul 2010 07:42:31 GMT

I was first introduced to calendar and time tables when I started learning about data warehousing. I have always thought they would be useful in the relational world as well. It is nice to see someone using them.

RE: Calendar Tables

wagner crivelini — Thu, 15 Jul 2010 07:36:57 GMT

Nice article! People tend to avoid time tables relying on SQL functions. This is a good approach if you don't need to do those calculations frequently.I remember a project where people wanted to kill me when I designed a table with time period and correspondent details as worked hours in that period and so on.When somebody decided to change those periods from calendar months to something different, programmers got crazy and came to me to see what to do.... Just to hear all we had to do was to run a couple of UPDATE statements in the previously infamous time table :-D

RE: Calendar Tables

Victor Kirkpatrick — Thu, 15 Jul 2010 06:03:38 GMT

The key isn't random just because it's an identity, as long as when the records for the date table were created in the correct "month" order. And no, it isn't dangerous.Nice article. I already have a numbers table that I use regularly, maybe it's time for a dates table like this.As for for Tom's comments concerning staying away from tables like this where you are worried about not have data for a given year. Relax. You could load this table through 2500 and it would still easily reside in memory.

RE: Calendar Tables

awilbournsqlcentral — Thu, 15 Jul 2010 05:58:27 GMT

One thing I think could be improved is the key for the tables should not be a random key, I would suggest to use the date as the key. So in your month table you make the key be YEARMONTH, so 200001. Further example:201001201002201003201004201005201006201007201008201009201010201011201012

RE: Calendar Tables

Karen Lopez - InfoAdvisors — Thu, 15 Jul 2010 05:56:04 GMT

With an Identity, isn't it a risk to assume the value is sequential and ordered?

RE: Calendar Tables

Michael Nicol — Thu, 15 Jul 2010 05:28:13 GMT

Hey Todd,Nice article. As I'm primarily a developer and have recently dipped my feet in the DBA world, I'd like to ask some questions around some points that I am unsure of.1. Wouldn't the join between CalMonth and Sales.SalesOrderHeader be more efficient if you added a Foreign key relationship from Sales.SalesOrderHeader to the MonthID in CalMonth? There would have to be some initial update to history data to populate the data (in Sales.SalesOrderHeader), and the data could then be maintained, either through changes to the application, or by adding a (don't kill me yet, I know.. I hate them too, but they have their place) FOR INSERT and perhaps FOR UPDATE trigger (ONLY if an application code change is a barrier)?2. You mentioned creating a covering index on Stay_ArrivalRevenue to avoid excessive key lookups. Would it not be better to create the index and only INCLUDE the fields to satisfy the SELECT portion?i.e. Instead of [code]CREATE INDEX #Stay_ArrivalRevenue ON #STAY (ArrivalDate, TotalRevenue)[/code]rather use[code]CREATE INDEX #Stay_ArrivalRevenue ON #STAY (ArrivalDate) INCLUDE (TotalRevenue)[/code]This would preserve a better key density at higher levels of the index while still avoiding excessive key lookups.As mentioned previously, I'm primarily a Dev and, at the moment, am still learning more and more about the DBA world. My points above are purely to help me understand better. Any insights would be appreciated.RegardsMike Nicol

RE: Calendar Tables

David Data — Thu, 15 Jul 2010 04:50:34 GMT

I use a date table allowing lookup of Month, Season etc. by date and customer, provided by Marketing! This may sound crazy but it's a good way to deal with the fact that different customers have different 'Sales' periods, start their Summer Season on different dates, etc.Of course we make sure the calendar runs ahead of the current year, AND automatically email out a clear error report if a date appears that's not in the table.

RE: Calendar Tables

Tom Williams-175034 — Thu, 15 Jul 2010 04:22:57 GMT

[quote][b]ziangij (7/15/2010)[/b][hr]i have populated data till year 2050 as a contigency measure :-D[quote][b]Tom Williams-175034 (7/15/2010)[/b][hr]Here we've always been of the belief that you shouldn't have tables with date ranges, because tables get created and populated, the query in question works, and the table gets forgotten about. You come into work on Jan 2nd, and something has stopped working, because there are no valid dates in the table, and the query that relies on this has failed because no-one updated the table, or the people who know about the table have left.I understand about the performance benefit, and it makes sense. Perhaps there could be a recurring job that runs every Dec XXth, that adds to the contents of that table so that the table always holds at least a years extra data, for instance. That way, the table will never run out of dates.Just my £0.02.[/quote][/quote]This is how the Y2K problem came about; programmers who figured their application wouldn't be about in 40 years time... Have some faith in your applications - People might still be using it then!

RE: Calendar Tables

ziangij — Thu, 15 Jul 2010 04:21:02 GMT

i have populated data till year 2050 as a contigency measure :-D[quote][b]Tom Williams-175034 (7/15/2010)[/b][hr]Here we've always been of the belief that you shouldn't have tables with date ranges, because tables get created and populated, the query in question works, and the table gets forgotten about. You come into work on Jan 2nd, and something has stopped working, because there are no valid dates in the table, and the query that relies on this has failed because no-one updated the table, or the people who know about the table have left.I understand about the performance benefit, and it makes sense. Perhaps there could be a recurring job that runs every Dec XXth, that adds to the contents of that table so that the table always holds at least a years extra data, for instance. That way, the table will never run out of dates.Just my £0.02.[/quote]

RE: Calendar Tables

Jason-299789 — Thu, 15 Jul 2010 02:19:23 GMT

Rather than using the Tally method have you considered using a CTE, the code below would work just as well and be a little more flexible. [code="sql"]DECLARE @DateFrom as DateTimeDeclare @DateTo As DateTimeSelect @DateFrom = '01-Jan-2000', @DateTo='31-Dec-2010';WITH GetDateId(Id,MonthDate) AS ( SELECT CAST(@DateFrom as Int) AS Id,@DateFrom DaysDate UNION ALL SELECT Id + 1,Cast(Id+1 as DATETIME) MonthDate FROM GetDateId gr Where Cast(Id as SmallDateTime) < @DateTo )SELECT MonthDate, Upper(Left(DateName(M,MonthDate),3))+'-'+Right(Year(MonthDate),2)FROM GetDateIdWHERE MonthDate>=@DateFrom OPTION (MAXRECURSION 0);[/code]

RE: Calendar Tables

Tom Williams-175034 — Thu, 15 Jul 2010 02:15:04 GMT

Here we've always been of the belief that you shouldn't have tables with date ranges, because tables get created and populated, the query in question works, and the table gets forgotten about. You come into work on Jan 2nd, and something has stopped working, because there are no valid dates in the table, and the query that relies on this has failed because no-one updated the table, or the people who know about the table have left.I understand about the performance benefit, and it makes sense. Perhaps there could be a recurring job that runs every Dec XXth, that adds to the contents of that table so that the table always holds at least a years extra data, for instance. That way, the table will never run out of dates.Just my £0.02.

RE: Calendar Tables

René Berends — Thu, 15 Jul 2010 00:59:51 GMT

Good article, I couldn't agree more! Some things regarding dates and calendars cannot (easily) be caught in formulae, take for example:1. Weeknumbering around newyear. Some don't want a week 53 and sure don't want January 1st to be part of week 53 of the last year2. Working days: is a specific date a working day or a non-working day?3. Rare, but I now some cases: for example invoice dates should always fall on a working day. What if we invoice on a Sunday? Move it to the next working day4. Quarter / Period based on a 4-4-5 week scheduleThings like these I always solve using a calendar table. Seems more logic to the customer as well!René

Calendar Tables

tfifield — Wed, 14 Jul 2010 22:05:00 GMT

Comments posted to this topic are about the item [B]Calendar Tables[/B]