November 17, 2010 at 5:24 am
Wonderful code... could someone assist with a function that uses dim_Date table(s) and works something like http://www.sqlservercentral.com/Forums/Topic207135-23-1.aspx but also accommodates for holidays???
Thinking something like this:
weekday_dateadd(dateTime,<# weekdays>,<0/1>Skip holidays,<0/1>DayOnly -drop time)
Select weekday_dateadd(getdate(),10,1,1)
Thank you in advance!!!
November 17, 2010 at 1:59 pm
Anyone... would be a great help... thank you.
November 17, 2010 at 3:18 pm
Here is how to drop the time from a date/time. I use GetDate() in the example but you can use a column just as well.
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, GetDate() ))
ATBCharles Kincaid
November 17, 2010 at 3:29 pm
thank you but unfortunately I don't think that will accommodate my need. I need to be able to add days (weekdays only excluding holidays) to a date, my basic need would always be getdate() but more flexible as a function parameter so I can do "BASICALY" getdate()+10 (no weekend day or holiday).
This dim_Date table has everything to do it... but not sure the most efficient way to do it.
Me doing it...I would just give a start date and get X rows in the future that are weekday and not holiday and take max(date) returned... It would work... but most efficient??
November 18, 2010 at 5:22 pm
Guess will just post my version maybe will be of some help to someone else.
CREATE FUNCTION [dbo].[weekday_dateadd] ( @startDate SMALLDATETIME, @Days INT, @SkipHoliday BIT = 1, @DayOnly BIT = 1 )
RETURNS SMALLDATETIME
AS
BEGIN
/*
Title: weekday_dateadd(2)
Author: Mark Wolff
Date: 11/18/2010
Purpose: Adds a specified number of weekdays to a given date allowing to SkipHolidays also
Examples:
SELECT dbo.weekday_dateadd(GETDATE(),90,DEFAULT,DEFAULT)
SELECT dbo.weekday_dateadd(GETDATE(),10,DEFAULT,0)
NOTE: Will not work past a few years
*/
DECLARE @endDate SMALLDATETIME
SELECT @endDate = date
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY date) Sequence
,CASE WHEN @DayOnly = 1 THEN Date ELSE Date + CONVERT(VARCHAR,@startDate,108) END Date
FROM dim_Date(NOLOCK)
WHERE DAYOFWEEK NOT IN ('Saturday','Sunday')
AND (CASE WHEN @SkipHoliday = 1 AND HolidayText IS NOT NULL THEN 0 ELSE 1 END) = 1
AND Date BETWEEN @startDate-1 AND DATEADD(d,@Days+@Days/7*3+20,@startDate)
)d
WHERE Sequence = @Days
RETURN @endDate
END
Did some basic testing seems to work š
March 8, 2011 at 10:18 am
Sorry for the criticism, but I find this article very poorly written. There is no information given why I would want to like into this code, what purpose it serves, or what problem it cures. It is just lacking background information as a whole. Personally, Iām not prone to dig into code without the slightest hint or tease as to why I would want to.
May 3, 2011 at 8:07 am
Since we're sharing our Date Dimensions...
I did my latest incarnation of it mostly using derived columns. So far performance seems to have improved over when I was using all typed columns. We've got combined Canadian and US businesses working on the same data, so that's why there's a US and CA values.
It's populated using Jeff Moden's Tally table to generate Calendar_dates, the other values are imparted by loading data from OLTP managed calendar tables to indicate holidays and partial holidays.
CREATE TABLE [dim_calendar](
[Calendar_Key] [int] IDENTITY(1,1) NOT NULL,
[Calendar_Date] [date] NOT NULL,
[US_Holiday_Indicator] [varchar](10) NOT NULL,
[CA_Holiday_Indicator] [varchar](10) NOT NULL,
[US_Working_Days_In_Month] [int] NOT NULL,
[CA_Working_Days_In_Month] [int] NOT NULL,
[US_Working_Hours_In_Day] [int] NOT NULL,
[CA_Working_Hours_In_Day] [int] NOT NULL,
[Week_Ending_Date] AS (dateadd(day,(7)-datepart(weekday,[Calendar_Date]),[Calendar_date])),
[Year_Numeric] AS (datepart(year,[calendar_date])),
[Year_Text] AS (datename(year,[Calendar_Date])),
[Quarter_Numeric] AS (datepart(quarter,[Calendar_Date])),
[Quarter_Smart_key] AS (datepart(year,[Calendar_Date])*(100)+datepart(quarter,[Calendar_date])),
[Quarter_Text_QQYYYY] AS ((('Q'+datename(quarter,[Calendar_Date]))+' ')+datename(year,[Calendar_Date])),
[Quarter_Text_YYYYQQ] AS ((datename(year,[Calendar_Date])+' Q')+datename(quarter,[Calendar_Date])),
[Month_Numeric] AS (datepart(month,[Calendar_Date])),
[Month_Smart_Key] AS (datepart(year,[Calendar_Date])*(100)+datepart(month,[Calendar_Date])),
[Month_Text] AS ((datename(month,[Calendar_Date])+' ')+datename(year,[CAlendar_Date])),
[Day_Numeric] AS (datepart(day,[Calendar_Date])),
[Day_Smart_Key] AS (((datepart(year,[Calendar_Date])*(1000000)+datepart(quarter,[Calendar_Date])*(10000))+datepart(month,[Calendar_Date])*(100))+datepart(day,[Calendar_Date])),
[Day_In_Week] AS (datepart(weekday,[Calendar_Date])),
[Day_Text] AS ((((datename(month,[Calendar_Date])+' ')+datename(day,[Calendar_Date]))+', ')+datename(year,[Calendar_Date])),
[Day_Name] AS (datename(weekday,[Calendar_Date])),
[Day_Type] AS (case when datename(weekday,[Calendar_Date])='Sunday' OR datename(weekday,[Calendar_Date])='Saturday' then 'Weekend' else 'Weekday' end),
[Thirty_Day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(30) else datediff(day,[Calendar_Date],getdate())/(30) end),
[Thirty_Day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(30)),(0))+' 30 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(30) when (0) then 'Current 30 Day Period' when (1) then 'Prior 30 Day Period' when (2) then 'Tertiary 30 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(30),(0))+' 30 Day Periods Ago' end end),
[Ninety_day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(90) else datediff(day,[Calendar_Date],getdate())/(90) end),
[Ninety_day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(90)),(0))+' 90 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(90) when (0) then 'Current 90 Day Period' when (1) then 'Prior 90 Day Period' when (2) then 'Tertiary 90 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(90),(0))+' 90 Day Periods Ago' end end),
[Threehundredsixtyfive_day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(365) else datediff(day,[Calendar_Date],getdate())/(365) end),
[Threehundredsixtyfive_day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(365)),(0))+' 365 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(365) when (0) then 'Current 365 Day Period' when (1) then 'Prior 365 Day Period' when (2) then 'Tertiary 365 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(365),(0))+' 365 Day Periods Ago' end end),
[Six_Month_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(month,[Calendar_Date],getdate())/(6) else datediff(month,[Calendar_Date],getdate())/(6) end),
[Six_Month_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(month,[Calendar_Date],getdate())/(6)),(0))+' 6 Month Period(s) in the Future' else case datediff(month,[Calendar_Date],getdate())/(6) when (0) then 'Current 6 Month Period' when (1) then 'Prior 6 Month Period' when (2) then 'Tertiary 6 Month Period' else CONVERT([varchar],datediff(month,[Calendar_Date],getdate())/(6),(0))+' 6 Month Periods Ago' end end),
[Quarters_Periods] AS (datediff(quarter,[Calendar_Date],getdate())),
[Quarters_Periods_Text] AS (case when datediff(quarter,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(quarter,[Calendar_Date],getdate())),(0))+' Quarter(s) in the future' else case datediff(quarter,[Calendar_Date],getdate()) when (0) then 'Current Quarter' when (1) then 'Prior Quarter' when (2) then 'Tertiary Quarter' else CONVERT([varchar],datediff(quarter,[Calendar_Date],getdate()),(0))+' Quarters Ago' end end),
[Months_Periods] AS (datediff(month,[Calendar_Date],getdate())),
[Months_Periods_Text] AS (case when datediff(month,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(month,[Calendar_Date],getdate())),(0))+' Month(s) in the Future' else case datediff(month,[Calendar_Date],getdate()) when (0) then 'Current Month' when (1) then 'Prior Month' when (2) then 'Tertiary Month' else CONVERT([varchar],datediff(month,[Calendar_Date],getdate()),(0))+' Months Ago' end end),
[Day_Periods] AS (datediff(day,[Calendar_Date],getdate())),
[Day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then case datediff(day,[Calendar_Date],getdate()) when (-1) then 'Tomorrow' else CONVERT([varchar],abs(datediff(day,[Calendar_Date],getdate())),(0))+' Days in the future' end when datediff(day,[Calendar_Date],getdate())=(0) then 'Today' when datediff(day,[Calendar_Date],getdate())=(1) then 'Yesterday' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate()),(0))+' Days Ago' end),
[Week_Periods] AS (datediff(week,[Calendar_Date],getdate())),
[Week_Periods_Text] AS (case when datediff(week,[Calendar_Date],getdate())<(0) then case datediff(week,[Calendar_Date],getdate()) when (-1) then 'Next Week' else CONVERT([varchar],abs(datediff(week,[Calendar_Date],getdate())),(0))+' Weeks in the future' end when datediff(week,[Calendar_Date],getdate())=(0) then 'This Week' when datediff(week,[Calendar_Date],getdate())=(1) then 'Last Week' else CONVERT([varchar],datediff(week,[Calendar_Date],getdate()),(0))+' Weeks Ago' end),
[Days_In_Month] AS (datediff(day,dateadd(month,datediff(month,(0),[Calendar_Date]),(0)),dateadd(month,(1)+datediff(month,(0),[Calendar_Date]),(0)))),
CONSTRAINT [PK__dim_cale__3C52D19446486B8E] PRIMARY KEY CLUSTERED
(
[Calendar_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
December 6, 2011 at 8:53 am
daleintacoma (3/8/2011)
Sorry for the criticism, but I find this article very poorly written. There is no information given why I would want to like into this code, what purpose it serves, or what problem it cures. It is just lacking background information as a whole. Personally, Iām not prone to dig into code without the slightest hint or tease as to why I would want to.
Most people that would search on this article, or come into it, know what it is and what it is used for. It not so much an article as it is script... and that is why it is found in the scripts section and not the articles. If you have a sandbox you can run the code against you can see it's output tables. If you know warehousing, you'll know why/how you'd use it. If you don't, I'd suggest you read a datawarehousing book first.
February 4, 2014 at 6:12 am
Couple typos:
- Independence Day
- Martin Luther King Jr
May 17, 2016 at 6:36 am
Thanks for the script.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply