December 30, 2014 at 11:18 am
Comments posted to this topic are about the item Calendar Table Function
January 7, 2015 at 2:59 am
Hello
what is a containt of table holiday
i m begining in database
January 7, 2015 at 3:38 am
this is a great function. thanks for posting.
In response to the previous reply, if you want to ignore the Holiday table you can use the following (Feel free to substitute the variable values but this will currently produce the calendar for the current year only).
It's practically the same script with a minor tweak to remove the Holiday table.
DECLARE @StartDate DATE = DATEADD(Year, DATEDIFF(Year, 0, GETDATE()), 0);
DECLARE @EndDate DATE = DATEADD(Year, DATEDIFF(Year, 0, GETDATE())+1, 0)-1;
SELECT DATEADD(DAY,tt.RID-1,@StartDate)
, tt.RID
, DATEADD(DAY,tt.RID-1,@StartDate) AS [Date]
, DATEPART(quarter,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Quarter]
, DATEPART(dayofyear,DATEADD(DAY,tt.RID-1,@StartDate)) AS [DayofYear]
, DATEPART(WEEK,DATEADD(DAY,tt.RID-1,@StartDate)) AS [WeekofYear]
, DATEPART(YEAR,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Year]
, DATEPART(MONTH,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Month]
, DATEPART(DAY,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Day]
, DATEPART(weekday,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Weekday]
, DATENAME(MONTH,DATEADD(DAY,tt.RID-1,@StartDate)) AS [MonthName]
, DATENAME(weekday,DATEADD(DAY,tt.RID-1,@StartDate)) AS [WeekdayName]
, (RIGHT(
REPLICATE('0',(4)) +
CONVERT([VARCHAR],DATEPART(YEAR,DATEADD(DAY,tt.RID-1,@StartDate)),0)
,(4)
)+
RIGHT(
REPLICATE('0',(2)) +
CONVERT([VARCHAR],DATEPART(MONTH,DATEADD(DAY,tt.RID-1,@StartDate)),0)
,(2)
)
) AS [Vintage]
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS [RID]
FROM sys.all_objects WITH (NOLOCK)
) tt
WHERE DATEADD(DAY,tt.RID-1,@StartDate) <= @EndDate;
January 7, 2015 at 5:35 am
Great Post - This will save me a lot of effort !
I'm still SS2005 - The icing on the cake would be to see the ISO week calculated as well, to have the option to start the week on a Monday, and to have weekend dates show up as non working by default without having to put them in the HOLIDAY table...
I'm also guessing that this could then be easily tailored to calculate the 'Datediff_WORKING_DAYS' between two dates ???
And for the grand finale - how could I setup location-specific holidays for a corporate environment (i.e. St. Patrick's day is a holiday in Ireland only, etc.)?
Thanks, Brian.
January 7, 2015 at 6:04 am
Unfortunately, SQL Server 2005 doesn't support ISO Week within DatePart. Some logic could be inserted to allow for this in the function, but the concern would be performance. This function was written to be fast and effecient with minimal logic. If I were to tackle that problem, I would create a separate function just for that purpose and call it within this Calendar Function.
For the question on DateDiff Working Days, you can use windowing, a technique of adding a row number to the result set through RANK or ROW_NUMBER. This part of the result could be used to determine number of business days between two dates. There are 9 business days between 1/1/2015 and 1/15/2015:
SELECT ROW_NUMBER() OVER (ORDER BY RID) AS [RowNumber], *
FROM dbo.CALENDAR('20150101','20150115')
WHERE IsBusinessDay = 1 And [Date] Between '20150101' AND '20150115'
If you want specific holidays, your holiday table could contain a column for an Owner. In your example, the Owner would be 'IRE'. Next, adjust the function to accept an Owner parameter and use this in the join predicate for the Holidays table:
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS [RID]
FROM sys.all_objects WITH (NOLOCK)
) tt LEFT OUTER JOIN
dbo.HOLIDAYS rh WITH (NOLOCK) ON DATEADD(DAY,tt.RID-1,@StartDate) = rh.[CALENDER_DATE] And rh.[Owner] = @Owner
January 7, 2015 at 6:41 am
Perfect!
I'll tailor the [IsBusinessDay] field to ignore Sat / Sun values and I'm sorted.
Thanks, Brian.
January 7, 2015 at 6:47 am
Forgot to include the updated [IsBusnessDay] for what it is worth:
WAS:
, CASE WHEN rh.[CALENDER_DATE] IS NULL THEN 1 ELSE 0 END AS [IsBusinessDay]
NOW (Excludes days 1:Sun and 7:Sat)
, CASE WHEN rh.[CALENDER_DATE] IS NULL AND NOT DATEPART(weekday,DATEADD(DAY,tt.RID-1,@StartDate)) = 1 AND NOT DATEPART(weekday,DATEADD(DAY,tt.RID-1,@StartDate)) = 7 THEN 1 ELSE 0 END AS [IsBusinessDay]
January 7, 2015 at 7:00 am
Remember that the day of the week and the name of the week day are included in the function. This way, you can easily exclude these days. These columns are returned as Weekday and WeekdayName.
January 7, 2015 at 9:31 am
I don't like the extra overhead of generating hundreds of rows every time when you only need a dozen or two.
A "standard" sequential-numbers tally table can easily handle those types of requests, generating only the required output rows:
--Last Day in each month:
SELECT DATEADD(DAY, -1, DATEADD(MONTH, t.tally, start_date)) AS Date
FROM (
SELECT CAST('20140101' AS date) AS start_date
) AS control_dates
INNER JOIN tally t ON
t.tally BETWEEN 1 AND 12
ORDER BY Date
--Last Thursday in each month:
SELECT DATEADD(DAY, -DATEDIFF(DAY, known_base_Thursday, last_day_of_month) % 7, last_day_of_month) AS Date
FROM (
SELECT CAST('20140101' AS date) AS start_date
) AS control_dates
INNER JOIN tally t ON
t.tally BETWEEN 1 AND 12
CROSS APPLY (
SELECT 3 AS known_base_Thursday,
DATEADD(DAY, -1, DATEADD(MONTH, t.tally, start_date)) AS last_day_of_month
) AS assign_alias_names
ORDER BY Date
--First and Third Monday in each month:
SELECT DATEADD(DAY, -DATEDIFF(DAY, known_base_Monday, [7th_day_of_month]) % 7 + days_to_add, [7th_day_of_month]) AS Date
FROM (
SELECT CAST('20140101' AS date) AS start_date
) AS control_dates
INNER JOIN tally t ON
t.tally BETWEEN 1 AND 12
CROSS APPLY (
SELECT 0 AS known_base_Monday,
DATEADD(DAY, 6, DATEADD(MONTH, t.tally - 1, start_date)) AS [7th_day_of_month]
) AS max_possible_first_Monday
CROSS JOIN (
SELECT 0 AS days_to_add UNION ALL --0=first Monday, 14=3rd Monday.
SELECT 14
) AS additional_output_days
ORDER BY Date
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 7, 2015 at 12:09 pm
A tally table would work well. That is basically what I am building with the sub-select against sys.all_objects. The overhead is there to return a larger data set, but for our application of the function, the cost was negligible.
I appreciate the point and it may help others decide if this is the appropriate solution for their application or they may want to take a different route.
January 9, 2015 at 4:45 am
I contributed a similar UDF to return holidays for any given year. Basically, the holidays are computed based on rules (such as the same date every year, the 4th Thursday in a month), etc.
SQL Calendar
http://www.sqlservercentral.com/scripts/Date+Manipulation/74302/
This might be a nice addition to your work
January 9, 2015 at 6:05 am
Thanks, Joe. That is a great add to this process. It would be nice to calculate the holidays rather than persist them and have a yearly task to add the next year.
January 27, 2015 at 8:12 pm
RunnerIE (1/7/2015)
I'm still SS2005 - The icing on the cake would be to see the ISO week calculated as well,
Please see the following article for an easy formula to incorporate.
http://www.sqlservercentral.com/articles/T-SQL/97910/
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2015 at 8:27 pm
Thanks Joe for sharing.
January 29, 2015 at 5:18 pm
Works a charm. Thanks!
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply