August 14, 2010 at 4:57 pm
Comments posted to this topic are about the item Calendar Tables II - The Anchor
August 16, 2010 at 2:11 am
Another nice example of using a date table, however when you assign the day name wouldnt you be better removing the Case statement and using the DateName(dw,<date>) function that is embeded in SQL, it removes confusion if a company doesnt use the default DATEFIRST.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
August 16, 2010 at 3:44 am
Nearly excellent.
Two things that made it only good for me is the "typical" fact that the date calculation works only for the US (and Canada). The other is the RBAR in the creation of the date table and the hard coded week day names.
Suggestion for the date table creation below:
-- Set Language 'English'
Set Language 'British'
-- Set Language 'German'
-- Set Language 'Spanish'
-- Set Language 'Italian'
-- Set Language 'French'
---- execute sp_helplanguage -- documentation
--INSERT INTO CalDay
--(
-- DayDate,
-- NextDay,
-- DayNum,
-- NameOfDay
--)
SELECT
-- add days to anchor date, then add number of years to that calculation
DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime))) As DayDate,
DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number, Cast('1900-01-01' As datetime))) As NextDay,
Datepart(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As DayNum,
DateName(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As NameOfDay
FROM dbo.Numbers JulianDays
Cross Join
(
SELECT
Number As YearNumber
FROM dbo.Numbers
WHERE Number Between 2000 And 2011
) Years
WHERE -- ensure that the result is actually a date (safety check)
IsDate(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) = 1
AND -- don't generate double, one year always has 365 days
JulianDays.Number Between 1 and 365
-- add a day for years modulo by 4, but not by 100, except when modulo by 400
-- deduct 1 or 0 from 1 if modulo by 4 = if leapyear 1 - 0 = 1 --> one extra day
-- add 1 if modulo by 100/400 = if 1900,2100,2200 --> 1 - 0 + 0 - 1 --> no extra day
-- if 2000, 2400, 2800 --> 1 - 0 + 0 - 0 --> one extra day
+ (1 - Sign(Years.YearNumber % 4) + Sign(Years.Yearnumber % 100) - Sign(Years.YearNumber % 400))
--AND
-- Year(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) < 2012
Order By DayDate
Certainly only works if there is dbo.Numbers around (how to at http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/). The beauty of above beast is that it handles date names simply by the language that is selected and the week day numbers too.
One option to clear up the code is to create an intermediate step of a temporary table that holds just the date itself, then pass this temporary table to all the functions involved and pass this result set into CalDay.
Very nice and fun article to read otherwise. I really like "the boss".
August 16, 2010 at 9:07 am
In memento of the "as compact as possible", change DayID to smalldatetime and, if necessary, start at -32768 (there are exactly 65,536 days available to the SMALLDATETIME data type).
DayNum should be TINYINT
NameOfDay should be VARCHAR(9)
Personally, I don't agree with having NextDay in there, but that's going to depend on what kind of data you're joining to.
August 16, 2010 at 10:07 am
Thanks for sharing this with us.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2010 at 12:00 pm
nice article
i tried using it today and the only problem was that my source data had hours and minutes and it would have taken too long to figure out a way to join it properly
June 4, 2011 at 12:42 pm
alen teplitsky (8/18/2010)
nice articlei tried using it today and the only problem was that my source data had hours and minutes and it would have taken too long to figure out a way to join it properly
I know this is an old post but figured I'd show how easy that join actually is thanks to Todd's forsight in including a "NextDay" column ...
SELECT whatever
FROM dbo.yourtable yt
INNER JOIN dbo.Calendar c
ON yt.YourDate >= c.DayDate
AND yt.YourDate < c.NextDay
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2011 at 1:19 pm
Knut Boehnert (8/16/2010)
Nearly excellent.Two things that made it only good for me is the "typical" fact that the date calculation works only for the US (and Canada). The other is the RBAR in the creation of the date table and the hard coded week day names.
Suggestion for the date table creation below:
-- Set Language 'English'
Set Language 'British'
-- Set Language 'German'
-- Set Language 'Spanish'
-- Set Language 'Italian'
-- Set Language 'French'
---- execute sp_helplanguage -- documentation
--INSERT INTO CalDay
--(
-- DayDate,
-- NextDay,
-- DayNum,
-- NameOfDay
--)
SELECT
-- add days to anchor date, then add number of years to that calculation
DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime))) As DayDate,
DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number, Cast('1900-01-01' As datetime))) As NextDay,
Datepart(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As DayNum,
DateName(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As NameOfDay
FROM dbo.Numbers JulianDays
Cross Join
(
SELECT
Number As YearNumber
FROM dbo.Numbers
WHERE Number Between 2000 And 2011
) Years
WHERE -- ensure that the result is actually a date (safety check)
IsDate(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) = 1
AND -- don't generate double, one year always has 365 days
JulianDays.Number Between 1 and 365
-- add a day for years modulo by 4, but not by 100, except when modulo by 400
-- deduct 1 or 0 from 1 if modulo by 4 = if leapyear 1 - 0 = 1 --> one extra day
-- add 1 if modulo by 100/400 = if 1900,2100,2200 --> 1 - 0 + 0 - 1 --> no extra day
-- if 2000, 2400, 2800 --> 1 - 0 + 0 - 0 --> one extra day
+ (1 - Sign(Years.YearNumber % 4) + Sign(Years.Yearnumber % 100) - Sign(Years.YearNumber % 400))
--AND
-- Year(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) < 2012
Order By DayDate
Certainly only works if there is dbo.Numbers around (how to at http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/). The beauty of above beast is that it handles date names simply by the language that is selected and the week day numbers too.
One option to clear up the code is to create an intermediate step of a temporary table that holds just the date itself, then pass this temporary table to all the functions involved and pass this result set into CalDay.
Very nice and fun article to read otherwise. I really like "the boss".
Your code labels the 1st of January, 2000 as a Monday. Check a calendar and find out that it actually occurred on a Saturday. 😉
Also, with a Tally or Numbers table, there's just no need for the complexity nor the double hit on the Tally table. Have a look...
--===== Declare some obviously-named variables and preset them
DECLARE @StartYear DATETIME,
@EndYear DATETIME
;
SELECT @StartYear = '2000',
@EndYear = '2011'
;
--===== Return the mini-date calendar
WITH
cteDays AS
(
SELECT DayDate = DATEADD(dd,t.N-1,@StartYear)
FROM dbo.Tally t --works for zero and unit based Tally tables in this case
WHERE t.N BETWEEN 1 AND DATEDIFF(dd,@StartYear,DATEADD(yy,1,@EndYear))
)
SELECT DayDate,
NextDay = DATEADD(dd,1,DayDate),
DayNum = DATEPART(dw,DayDate+@@DATEFIRST-1), --Doesn't care what DATEFIRST is set to.
NameOfDay = DATENAME(dw,DayDate)
FROM cteDays
;
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2011 at 1:25 pm
Almost forgot... I love the idea of having the NextDay column in there because it makes it so easy to join on dates with times while still being SARGable in the Joins and Predicates. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2014 at 1:58 am
I was actually asked recently about a calendar table and I've never really been a big fan. Too much admin.
The request was very similar, a full list of days even if no sales. The analyst that asked said could he create a calendar table and do it that way with a join and I turned him down due to administration, so he asked for another result that would mean it could be dynamic, no matter what dates were put in, past or future.
So, I worked out a quick function to give a calendar result.
create function [dbo].[fn_callist](@startdate date , @enddate date)
returns @results table (caldate date,yearnum int,monthnum int,weeknum int,daynum int,
monname varchar(12),weekday varchar(20))
as
begin
declare @loop int = 0
declare @loopend int = 0
set @loopend=DATEDIFF(d,@startdate,@enddate)
while @loop<>@loopend+1
begin
insert into @results
select DATEADD(d,@loop,@startdate),0,0,0,0,'',''
set @loop=@loop+1
end
update @results
set yearnum=DATEPART(yy,caldate),
monthnum=datepart(m,caldate),
monname=DATENAME(MONTH,caldate),
weeknum=DATEPART(WEEK,caldate),
daynum=DATEPART(d,caldate),
weekday=DATENAME(WEEKDAY,caldate)
return
end
GO
It's pretty fast, does the same kind of thing as a table but needs no real admin and gives you full details of the date. Great for when they don't like you adding tables to a DB. Also makes life a little easier should you wish to add some new functionality to it.
February 21, 2014 at 2:58 am
Really Nice article..it helped me in a project where i have to manage attendance of employee according to days.
February 21, 2014 at 7:59 am
I just created a whole slew of calendar tables so this is something that's going in my briefcase here.
FYL - My calendar tables are wide but still fit on a page. They also have StartDt and EndDt with the EndDt being the very, very, very end of the day in milliseconds because that's how date times work here. The future queries will be easier and faster since that date bracket is already calculated. They also include breakouts of the date values both in numeric and text values, again for ease of getting the info out of the table.
I will certainly be pointing the reporting people here for info on how best to use the tables. Thanks for the clear examples!
February 21, 2014 at 8:23 am
Storing the current and previous day as metadata is also common practice (in some circles) with SQL Server Analysis Services (date dimension) and Powerpivot, and it is also demostrated in a few MS white papers. It definitely limits the complexity of some date calculations.
In Analysis Services, however, you must be mindful that some aggregation rebuilds will be triggered if those aggregations are based on "CurrentDay = 'Y'.
February 21, 2014 at 8:46 am
Chris Cross (2/21/2014)
I was actually asked recently about a calendar table and I've never really been a big fan. Too much admin.The request was very similar, a full list of days even if no sales. The analyst that asked said could he create a calendar table and do it that way with a join and I turned him down due to administration, so he asked for another result that would mean it could be dynamic, no matter what dates were put in, past or future.
So, I worked out a quick function to give a calendar result.
create function [dbo].[fn_callist](@startdate date , @enddate date)
returns @results table (caldate date,yearnum int,monthnum int,weeknum int,daynum int,
monname varchar(12),weekday varchar(20))
as
begin
declare @loop int = 0
declare @loopend int = 0
set @loopend=DATEDIFF(d,@startdate,@enddate)
while @loop<>@loopend+1
begin
insert into @results
select DATEADD(d,@loop,@startdate),0,0,0,0,'',''
set @loop=@loop+1
end
update @results
set yearnum=DATEPART(yy,caldate),
monthnum=datepart(m,caldate),
monname=DATENAME(MONTH,caldate),
weeknum=DATEPART(WEEK,caldate),
daynum=DATEPART(d,caldate),
weekday=DATENAME(WEEKDAY,caldate)
return
end
GO
It's pretty fast, does the same kind of thing as a table but needs no real admin and gives you full details of the date. Great for when they don't like you adding tables to a DB. Also makes life a little easier should you wish to add some new functionality to it.
First, I notice this is your first post, so welcome aboard!
Shifting gears, creating such a function is a great idea but not using a While loop and not using an mTVF (Multi-statement Table Valued Function). I'm on my way to work but I'll be back to show you a much quicker and much less resource intensive method (if someone doesn't beat me to it).
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2014 at 11:10 am
I was interested in the fact that you chose a final report that had no dates at all.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply