January 7, 2014 at 12:24 pm
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
January 7, 2014 at 12:34 pm
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, ...) 😉
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 7, 2014 at 12:36 pm
Hi Phil,
Thanks for the tip. Didn't even notice the apostrophes 🙂
January 7, 2014 at 12:45 pm
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:w00t:
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.)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 8, 2014 at 6:08 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 8, 2014 at 10:59 pm
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?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 8, 2014 at 11:21 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 9, 2014 at 12:47 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply