December 12, 2011 at 10:09 pm
Comments posted to this topic are about the item Using Recursion and Date Tables to Simplify Date Logic
December 13, 2011 at 2:16 am
Hi David
I quite like your idea of using CTEs and recursion to build a calendar, but when I ran your CTE(View) I got a MAXRECURSION error, so I edited it a bit and came up with the following:
WITH Dates AS (
SELECT
CAST(CAST(CAST(GETDATE() AS INT) - (DATENAME(DAYOFYEAR, GETDATE() - 1)) AS DATETIME) AS DATE) AS CalendarDate
UNION ALL
SELECT
DATEADD(DAY , 1, CalendarDate)
FROM Dates
WHERE CalendarDate <= DATEADD(YY, 1, GETDATE())
)
SELECT
CalendarDate,
CalendarYear = YEAR(CalendarDate),
CalendarQuarter = DATENAME(quarter, CalendarDate),
CalendarMonth = MONTH(CalendarDate),
CalendarWeek = DATEPART(wk, CalendarDate),
CalendarDay = DAY(CalendarDate),
CalendarMonthName = DATENAME(MONTH, CalendarDate),
CalendarDayOfYear = DATENAME(dayofyear, CalendarDate),
Weekday = DATENAME(weekday, CalendarDate),
DayOfWeek = DATEPART(weekday, CalendarDate),
IsLeapYear = ISDATE(CAST(YEAR(CalendarDate)AS CHAR(4)) + '-02-29')
FROM Dates
OPTION (MAXRECURSION 731) --365 + 366 (it is possible that a leap year sneaks in)
Note the first date isn't a fixed value, but will default to the first day in the year the CTE is called. I also added the IsLeapYear field to the result and limited the recursion to 731 (365 + 366). Now it runs without any errors (at least on my SQL 2008 server).
Values returned are always for the first day of this year up to and including values for the date one year from now.
I am going to add this one to my repository.
Thanks for your effort.
December 13, 2011 at 3:31 am
Another option without using recursion at all:
;with Dates as (
SELECT TOP (DATEDIFF(dd,'20100101','20110101'))
CalendarDate = DATEADD(dd,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,'20100101')
FROM sys.columns A, sys.columns B
)
SELECT
CalendarDate
FROM
Dates
December 13, 2011 at 3:52 am
I use a table valued function which takes two parameters, a start and end date.
It returns 1 row per day starting on the specified start date up to, but *not* including, the specified end date with various useful (to me anyway) columns.
It uses a Tally function based on Itzik Ben-Gans' cross join method documented here:
http://www.simple-talk.com/sql/database-administration/creative-solutions-by-using-a-number-table/[/url]
CREATE FUNCTION [dbo].[Calendar]
(
@startDate datetime,
@endDate datetime
)
RETURNS TABLE
RETURN
SELECT
[date] ,
[weekday] ,
[weekday2] ,
[dayname] ,
[monthname] ,
d ,
m ,
y ,
ord,
dayname + ' ' + CAST(d AS VARCHAR(2)) + ord + ' ' + monthname AS LongDate,
isodate = CONVERT(char(8),date,112),
iso8601 = CONVERT(NVARCHAR,CAST(date AS DATETIME),126)
FROM
(
SELECT
[date],
DATEPART(weekday,date) [weekday],
(DATEPART(weekday,date) + @@DATEFIRST) % 7 as [weekday2], -- Normalized weekday (0-6) 0=Sat, 6 = Fri
DATENAME(weekday,date) [dayname],
DATENAME(MONTH,date) [monthname],
d = DATEPART(DAY,date),
m = MONTH(date),
y = YEAR(date),
ord = substring('stndrdthththththththththththththththththstndrdthththththththst',(2*datepart(day,date))-1,2)
FROM
(
SELECT
DATEADD(dd,N-1+DATEDIFF(dd,'19000101',@startDate),'19000101') as date
FROM
dbo.Tally(datediff(day,@startDate,@endDate))
) s1
) s2
GO
---- Tally function
CREATE FUNCTION [dbo].[Tally](@n int)
RETURNS TABLE
AS
RETURN(
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT top (@n) n FROM Nums ORDER BY n
)
GO
December 13, 2011 at 6:12 am
The Calendar View in the article is based on a "Counting rCTE". Please see the following article for why you should avoid such a thing.
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2011 at 6:43 am
Jeff, thanks for the link. Your article made a lot of sense. Not to mention that the code for the tally solution is much more simple and easier to write/remember.
I suppose I'll have to change my repository again 🙂
December 13, 2011 at 7:30 am
I'm not sure about this bit:
join CalendarPeriod cp on (cp.CalendarDate = OrderDate and CalendarPeriod = @Period) or (@Period = ‘All’)
That works fine if @Period != 'All', but as soon as @Period = 'All' the join predicate disappears and you have a cartesian product.
I think you want:
join CalendarPeriod cp on (cp.CalendarDate = OrderDate and CalendarPeriod = @Period) or (@Period = ‘All’ AND cp.CalendarDate IS NULL)
December 13, 2011 at 7:52 am
JonFox (12/13/2011)
I'm not sure about this bit:
join CalendarPeriod cp on (cp.CalendarDate = OrderDate and CalendarPeriod = @Period) or (@Period = ‘All’)
That works fine if @Period != 'All', but as soon as @Period = 'All' the join predicate disappears and you have a cartesian product.
I think you want:
join CalendarPeriod cp on (cp.CalendarDate = OrderDate and CalendarPeriod = @Period) or (@Period = ‘All’ AND cp.CalendarDate IS NULL)
or (simpler):
join CalendarPeriod cp on cp.CalendarDate = OrderDate and (CalendarPeriod = @Period or @Period = ‘All’)
[/code]
On the original article: I suggest avoiding a recursive query like this. Either use a Numbers/Tally table, or an actual Calendar table (not a view). Recursive CTEs are a huge performance hog, and the trade-off is just a few kb of storage and buffer space (for the table), which is trivial on any server built in the last 20 years.
The uses of Calendar tables (Dates tables) are significant, and it's a very useful tool in just about any database. But be sure you know what performance and other issues you're dealing with.
If you want a dynamic date-range, a view that uses DateAdd on a Numbers table will give you that much more efficiently than recursion.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2011 at 8:00 am
GSquared (12/13/2011)
or (simpler):
join CalendarPeriod cp on cp.CalendarDate = OrderDate and (CalendarPeriod = @Period or @Period = ‘All’)
Except, the way the original CalendarPeriod table is structured, I'm pretty sure that doing it that way also returns an incorrect number of rows, since there are multiple rows in CalendarPeriod with the same value for CalendarDate...
December 13, 2011 at 11:30 am
I learned a bit here, but it turns out that my database is already set up in days with various attributes on them and then periods with their own attributes.
I use the Itzik Ben-Gans/Jeff Moden (I think I read Jeff's article first, so I'm not sure who I should credit here) Numbers/Tally method to generate dates.
Thank you. I enjoyed this article and this discussion.
December 13, 2011 at 3:54 pm
Sean Cowburn (12/13/2011)
Jeff, thanks for the link. Your article made a lot of sense. Not to mention that the code for the tally solution is much more simple and easier to write/remember.I suppose I'll have to change my repository again 🙂
You bet. Thanks for the feedback, Sean.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2011 at 5:17 pm
ShawnTherrien (12/13/2011)
I learned a bit here, but it turns out that my database is already set up in days with various attributes on them and then periods with their own attributes.I use the Itzik Ben-Gans/Jeff Moden (I think I read Jeff's article first, so I'm not sure who I should credit here) Numbers/Tally method to generate dates.
Thank you. I enjoyed this article and this discussion.
The original method is Itzik's... A lot of us improved it here and there... I just explained it a bit so people would know what it is and how it works. Thanks for the thought, though. I definitely appreciate it, Shawn. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2011 at 12:43 am
Under what circumstances would you use one of these on-the-fly solutions as opposed to a permanent calendar table? I'm probably missing something here - it's not unusual - but the only circumstance that I can think of, in which I'd need an on-the-fly solution, is where I am prevented from creating a permanent calendar table on the server I was using. Because these "utility" tables (tally table is another example) are so useful, I've found that it's usually possible to convince the DBA to let them exist permanently.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
December 14, 2011 at 2:06 am
A situation where the on-the-fly may be useful is when you don't know in advance what range of dates you might need, and don't want 40000+ rows (representing 1-1-1900 to present) in your calendar table.
You can also use it to get months/daynames etc in other languages:
SETLANGUAGE 'French'
SELECT monthname FROM dbo.Calendar('20100101','20110101') WHERE d = 1
SELECT dayname FROM dbo.Calendar('20100104','20100111')
SETLANGUAGE 'us_english'
There are many other ways of doing this. I just like my date/calendar stuff in one place.
December 14, 2011 at 6:08 am
GPO (12/14/2011)
Under what circumstances would you use one of these on-the-fly solutions as opposed to a permanent calendar table? I'm probably missing something here - it's not unusual - but the only circumstance that I can think of, in which I'd need an on-the-fly solution, is where I am prevented from creating a permanent calendar table on the server I was using. Because these "utility" tables (tally table is another example) are so useful, I've found that it's usually possible to convince the DBA to let them exist permanently.
To add to what Nigel said, I'll certainly have some folks disagree with me but a part of the reason why people use Calendar tables is for speed. The problem is that people sometimes go a bit crazy with how many columns of information they put in their table which makes row length longer which means more reads per date range. With the high speed on-the-fly method first coined by Ben-Gan, you'll typically get something that really does live in memory and also has a very narrow row width (again, for performance) if you only calculate what you need.
Of course, if you really need to do something with a particular set of columns in a Calendar table over and over and over, it may be better to reevaluate the content of the Calendar table so it's not so all encompassing. For example, I think it's a mistake to store month and day names in such a table because there are some high performance system functions that easily take care of such a thing and hard-stored names won't change automatically like they will with system functions if the language changes.
On the "row width" thing hurting performance... it's one of the reasons why I never combine a Tally Table with a Calendar Table even if I need to enumerate the days in the Calendar Table. Tally Table base functions would have to read a whole lot more rows if I did and that would slow things down even if they're logical reads.
{EDIT} Of course, proper indexing will help solve that problem but, basically, requires the duplication of data.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply