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 12»»

Calendar Tables II - The Anchor Expand / Collapse
Author
Message
Posted Saturday, August 14, 2010 4:57 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 II - The Anchor
Post #969435
Posted Monday, August 16, 2010 2:11 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
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
Post #969597
Posted Monday, August 16, 2010 3:44 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: Wednesday, July 2, 2014 1:09 AM
Points: 834, Visits: 329
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".
Post #969648
Posted Monday, August 16, 2010 9:07 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: Today @ 8:37 AM
Points: 861, Visits: 2,356
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.
Post #969870
Posted Monday, August 16, 2010 10:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
Thanks for sharing this with us.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #969917
Posted Wednesday, August 18, 2010 12:00 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #971344
Posted Saturday, June 4, 2011 12:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1119872
Posted Saturday, June 4, 2011 1:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1119877
Posted Saturday, June 4, 2011 1:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1119879
Posted Friday, February 21, 2014 1:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:35 AM
Points: 1, Visits: 20
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.
Post #1543862
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse