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 Friday, February 21, 2014 2:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:56 AM
Points: 3, Visits: 6
Really Nice article..it helped me in a project where i have to manage attendance of employee according to days.
Post #1543882
Posted Friday, February 21, 2014 7:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 260, Visits: 598
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!
Post #1544009
Posted Friday, February 21, 2014 8:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 2,148, 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'.
Post #1544028
Posted Friday, February 21, 2014 8:46 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(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 #1544049
Posted Friday, February 21, 2014 11:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 141, Visits: 1,736
I was interested in the fact that you chose a final report that had no dates at all.
Post #1544137
Posted Friday, February 21, 2014 4:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:44 AM
Points: 109, 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;
Post #1544215
Posted Sunday, February 23, 2014 12:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 56, Visits: 1,039
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...
Post #1544278
Posted Sunday, February 23, 2014 7:07 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(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 #1544338
Posted Monday, February 24, 2014 7:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:44 AM
Points: 109, 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.
Post #1544483
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse