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

Holiday table Expand / Collapse
Author
Message
Posted Tuesday, January 07, 2014 12:24 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
Hello and happy new year!

I wrote a stored proc that basically aggregates some data based on how many Monday's, Tuesday's, Wednesday's etc in a current month (date range parameters). The proc works great, however I need to filter out holidays to get the correct number of days for the divisor.

My question is... What is the best way to approach this? I was considering creating a holiday table and manually entering holidays up until 2020, but that seems like a rookie move.

What are some dynamic approaches to this?
Any help is greatly appreciated.

Dave
Post #1528631
Posted Tuesday, January 07, 2014 12:34 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 4,828, Visits: 11,183
DaveDB (1/7/2014)
Hello and happy new year!

I wrote a stored proc that basically aggregates some data based on how many Monday's, Tuesday's, Wednesday's etc in a current month (date range parameters). The proc works great, however I need to filter out holidays to get the correct number of days for the divisor.

My question is... What is the best way to approach this? I was considering creating a holiday table and manually entering holidays up until 2020, but that seems like a rookie move (still need to impress my boss, as I am on a contract-to-hire role).

What are some dynamic approaches to this?
Any help is greatly appreciated.

Dave


Does your proc use a calendar table? If yes, I would think that adding a 'Holiday' column to it would be the easiest solution. If not, consider implementing one - it will speed up and simplify your proc, as well as helping solve this additional problem.

On a separate note, you may impress your boss even more if you omit the apostrophes when typing out day-name plurals (Mondays, Tuesdays, ...)



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1528637
Posted Tuesday, January 07, 2014 12:36 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
Hi Phil,

Thanks for the tip. Didn't even notice the apostrophes :)
Post #1528638
Posted Tuesday, January 07, 2014 12:45 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 4,828, Visits: 11,183
DaveDB (1/7/2014)
Hi Phil,

Thanks for the tip. Didn't even notice the apostrophes :)


No problem. My colleagues love the fact that I'm an amateur proofreader as well as a SQL Server professional

And if you need any assistance with your calendar table, please post back - though there are numerous resources out there already to get you going. I'd set the new Calendar column to be Tinyint Not Null and set its value to 0 (no holiday) or 1 (holiday).

(Tinyints can be easier than bits when it comes to sums and counts.)




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1528641
Posted Wednesday, January 08, 2014 6:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 3,590, Visits: 5,099
Phil Parkin (1/7/2014)

Does your proc use a calendar table? If yes, I would think that adding a 'Holiday' column to it would be the easiest solution. If not, consider implementing one - it will speed up and simplify your proc, as well as helping solve this additional problem.



There is nothing wrong with the advice that Phil is giving you here, but there is another approach that I personally prefer. It is not a "rookie" move to create a holidays table. In fact I like that because where I reside holidays are added to the calendar sort of ad-hoc, so it is easier to maintain a holiday table and either keep a separate calendar table or use a calendar generating function like the one below:


CREATE FUNCTION [dbo].[GenerateCalendar]
(
@FromDate DATETIME,
@NoDays INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== High speed code provided courtesy of Jeff Moden (idea by Dwain Camps)
--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
SELECT [SeqNo] = t.N,
[Date] = dt.DT,
[Year] = dp.YY,
[YrNN] = dp.YY % 100,
[YYYYMM] = dp.YY * 100 + dp.MM,
[BuddhaYr] = dp.YY + 543,
[Month] = dp.MM,
[Day] = dp.DD,
[WkDNo] = DATEPART(dw,dt.DT),
[WkDName] = CONVERT(NCHAR(9),dp.DW),
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
[JulDay] = dp.DY,
[JulWk] = dp.DY/7+1,
[WkNo] = dp.DD/7+1,
[Qtr] = DATEPART(qq,dt.Dt),
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY, Acourtesy of ChrisM)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)

) dp

GO
SELECT *
FROM dbo.GenerateCalendar('2014-01-01', 365);


You can JOIN the results from this function to your holiday table.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1529169
Posted Wednesday, January 08, 2014 10:59 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 4,828, Visits: 11,183
A few tasty techniques in that function Dwain. I have a quick follow-up question for you: as the function does not reference any external tables or views, does 'WITH SCHEMABINDING' have any effect?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1529203
Posted Wednesday, January 08, 2014 11:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 3,590, Visits: 5,099
Phil Parkin (1/8/2014)
A few tasty techniques in that function Dwain. I have a quick follow-up question for you: as the function does not reference any external tables or views, does 'WITH SCHEMABINDING' have any effect?


Strictly there as a performance improvement. The same technique Jeff Moden uses in DelimitedSplit8K.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1529207
Posted Thursday, January 09, 2014 12:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 4,828, Visits: 11,183
dwain.c (1/8/2014)
Phil Parkin (1/8/2014)
A few tasty techniques in that function Dwain. I have a quick follow-up question for you: as the function does not reference any external tables or views, does 'WITH SCHEMABINDING' have any effect?


Strictly there as a performance improvement. The same technique Jeff Moden uses in DelimitedSplit8K.


I see the technique, but Jeff's article does not justify its use in relation to 'with schemabinding'. No doubt it's buried somewhere in the supporting investigations. I'll do some more digging sometime.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1529221
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse