SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calendar Tables II - The Anchor


Calendar Tables II - The Anchor

Author
Message
somillohani
somillohani
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 6
Really Nice article..it helped me in a project where i have to manage attendance of employee according to days.
JustMarie
JustMarie
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: 1033 Visits: 1301
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!
sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2510 Visits: 487
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'.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89145 Visits: 41143
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

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
Robert.Sterbal
Robert.Sterbal
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 2000
I was interested in the fact that you chose a final report that had no dates at all.
SalvageDog
SalvageDog
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 336
I too am not a fan of the NextDay column. In this age of the DATE data type, why not convert SaleDate to DATE and equate it to the calendar table date? The query plans are roughly the same, and it makes the join a bit simpler.

SELECT
CD.DayDate, CD.NameOfDay
, COUNT(S.SaleID) AS NumberOfSales
, SUM(ISNULL(S.SaleAmt, 0)) AS DaySales
FROM CalDay CD
LEFT JOIN Sales S ON
CONVERT(DATE, S.SaleDate) = CD.DayDate
AND S.SlsRepID IN (0, 3)
WHERE CD.DayID BETWEEN @FirstDay And @FirstDay + 6
GROUP BY CD.DayDate, CD.NameOfDay
ORDER BY CD.DayDate;
Ryan C. Price
Ryan C. Price
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 1198
Jeff, challenge accepted.

something like this?:


create function [dbo].[fn_callist](@startdate date , @enddate date)
returns table as
-- CTE Tally table from http://www.sqlservercentral.com/blogs/never_say_never/2010/03/19/tally_2D00_table_2D00_cte/
return
WITH
t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
-- t5 AS (SELECT 1 N FROM t4 x, t4 y), -- if 64K days aren't enough, you could expand to include t5.
cteTally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t4 x, t4 y)
--- from Jeff's code as above...
, cteDays AS
(
SELECT DayDate = DATEADD(dd,t.N-1,@startdate)
FROM cteTally t --works for zero and unit based Tally tables in this case
WHERE t.N BETWEEN 1 AND DATEDIFF(dd,@startdate,@enddate)
)
SELECT DayDate,
NextDay = DATEADD(dd,1,DayDate),
DayNum = DATEPART(dw,dateadd(day, @@DATEFIRST-1, DayDate)), --Doesn't care what DATEFIRST is set to.
NameOfDay = DATENAME(dw,DayDate)
FROM cteDays
;


go

select * from [dbo].[fn_callist]('2011-01-01', '2011-10-31')



using both a dynamic tally table and a dynamic calendar table...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89145 Visits: 41143
SalvageDog (2/21/2014)
I too am not a fan of the NextDay column. In this age of the DATE data type, why not convert SaleDate to DATE and equate it to the calendar table date? The query plans are roughly the same, and it makes the join a bit simpler.

SELECT
CD.DayDate, CD.NameOfDay
, COUNT(S.SaleID) AS NumberOfSales
, SUM(ISNULL(S.SaleAmt, 0)) AS DaySales
FROM CalDay CD
LEFT JOIN Sales S ON
CONVERT(DATE, S.SaleDate) = CD.DayDate
AND S.SlsRepID IN (0, 3)
WHERE CD.DayID BETWEEN @FirstDay And @FirstDay + 6
GROUP BY CD.DayDate, CD.NameOfDay
ORDER BY CD.DayDate;


Unless that turns out to somehow be SARGable code (I don't believe it will but haven't tested it), that would be the reason why.

--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
SalvageDog
SalvageDog
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 336
I agree, that is always something to watch for, as using a function on a column in the where or join clause will usually cause a scan plan. In my experience, one exception to this rule is converting the longer date types to DATE, which has always resulted in SARGable code for me. I still test every time though.
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