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 Wednesday, July 14, 2010 10:05 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: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
Comments posted to this topic are about the item Calendar Tables
Post #952833
Posted Thursday, July 15, 2010 12:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:41 AM
Points: 2, Visits: 169
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 year
2. 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 day
4. Quarter / Period based on a 4-4-5 week schedule

Things like these I always solve using a calendar table. Seems more logic to the customer as well!

René


Kind regards,
René Berends
Post #952884
Posted Thursday, July 15, 2010 2:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 16, 2011 2:01 AM
Points: 7, Visits: 37
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.
Post #952915
Posted Thursday, July 15, 2010 2:19 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, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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.

DECLARE @DateFrom as DateTime
Declare @DateTo As DateTime

Select @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
GetDateId
WHERE MonthDate>=@DateFrom
OPTION (MAXRECURSION 0);



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #952917
Posted Thursday, July 15, 2010 4:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 1,975, Visits: 369
i have populated data till year 2050 as a contigency measure

Tom Williams-175034 (7/15/2010)
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.
Post #952970
Posted Thursday, July 15, 2010 4:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 16, 2011 2:01 AM
Points: 7, Visits: 37
ziangij (7/15/2010)
i have populated data till year 2050 as a contigency measure

Tom Williams-175034 (7/15/2010)
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.


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!
Post #952972
Posted Thursday, July 15, 2010 4:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:39 AM
Points: 115, Visits: 745
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.
Post #952987
Posted Thursday, July 15, 2010 5:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:17 AM
Points: 3, Visits: 46
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
CREATE INDEX #Stay_ArrivalRevenue ON #STAY (ArrivalDate, TotalRevenue)

rather use

CREATE INDEX #Stay_ArrivalRevenue ON #STAY (ArrivalDate) INCLUDE (TotalRevenue)

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.

Regards
Mike Nicol
Post #953008
Posted Thursday, July 15, 2010 5:56 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 8:12 AM
Points: 36, Visits: 147
With an Identity, isn't it a risk to assume the value is sequential and ordered?
Post #953037
Posted Thursday, July 15, 2010 5:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 14, 2013 1:42 PM
Points: 19, Visits: 44
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:
201001
201002
201003
201004
201005
201006
201007
201008
201009
201010
201011
201012
Post #953039
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse