Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calendar Tables II - The Anchor


Calendar Tables II - The Anchor

Author
Message
tfifield
tfifield
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1183 Visits: 2890
Comments posted to this topic are about the item Calendar Tables II - The Anchor
Jason-299789
Jason-299789
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1839 Visits: 3232
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>Wink function that is embeded in SQL, it removes confusion if a company doesnt use the default DATEFIRST.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Knut Boehnert
Knut Boehnert
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1043 Visits: 379
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".
Nadrek
Nadrek
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1552 Visits: 2714
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.
SQLRNNR
SQLRNNR
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26710 Visits: 18328
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

alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2242 Visits: 4654
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67067 Visits: 40697
alen teplitsky (8/18/2010)
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


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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67067 Visits: 40697
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67067 Visits: 40697
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Chris Cross
Chris Cross
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 28
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search