You are viewing limited content. For full access, please sign in.

Question

Question

Automated Holiday Dates Token Calculation

Workflow
Updated May 23, 2018
asked on May 15, 2018 • Show version history

Hello,

I am trying to create a workflow that populates SQL with Holiday Dates annually for two to four years. The calculations for certain holidays such as Good Friday are complicated due to the nature of Easter's calculation surrounding moon cycles.

Workflow as of 10.2 allows users in the admin console to observe holiday dates this is a useful feature, but I can't find a way to use it as a token to capture specific dates to populate SQL with.

The use case is tied to a forms process lookup that is using network days and excluding specific dates from the holiday table to calculate the total days based on workdays.

So far this works great, but there should be an easy way to automatically keep the table up to date to make it more accessible from a management, continuity standpoint. 

Below is a screenshot of the date logic I am trying to establish calculations for.

Screen Shot 2018-05-15 at 4.00.53 PM.png
Screen Shot 2018-05-15 at 4.00.53 PM.png (215.8 KB)
Download
0 0

Answer

SELECTED ANSWER
replied on May 23, 2018 • Show version history

So this worked out great we made some changes in the design by creating a workflow that looks at the most recent year and adds +1 to identify the year that needs to be populated this workflow then runs once a year. We are also using some stored procedures to populate and handle the calculations on forms for the date ranges to identify holiday dates and subtract from the total NETWORKDAYS.

Below is the updated SQL Script based on the Ontario Province Holiday Date Logic for businesses that are not open on Saturdays or Sundays.

USE [ExternalTables]
GO
/****** Object:  UserDefinedFunction [dbo].[Holiday_List_Ontario]    Script Date: 5/23/2018 10:35:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Holiday_List_Ontario] (@nYear INT)
RETURNS  @Holidays TABLE
(
	Holiday_Name VARCHAR(32),
	Holiday_Date SMALLDATETIME,
	Holiday_Country VARCHAR(32),
	Holiday_Province VARCHAR(32),
	Holiday_Year INT
)
AS
BEGIN
-- Calculate Easter Sunday
DECLARE @g INT
DECLARE @c INT
DECLARE @h INT
DECLARE @i INT
DECLARE @j INT
DECLARE @l INT
DECLARE @Month INT
DECLARE @Day INT
DECLARE @Easter SMALLDATETIME
DECLARE @WorkDT SMALLDATETIME

SET @g = @nYear % 19
SET @c = @nYear / 100
SET @h = ((@c - (@c / 4) - ((8 * @c + 13) / 25) + (19 * @g) + 15) % 30)
SET @i = @h - ((@h / 28) * (1 - (@h /28) * (29 / (@h + 1)) * ((21 - @g) / 11)))
SET @j = ((@nYear + (@nYear / 4) + @i + 2 - @c + (@c / 4)) % 7)
SET @l = @i - @j
SET @Month = 3 + ((@l + 40) / 44)
SET @Day = @l + 28 - (31 * (@Month / 4))
SET @Easter = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@nYear AS VARCHAR(4))

------------------------------------------------------------------------------------------------
-- Add Easter Sunday to holiday list, and get holidays based around Easter
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Easter',@Easter,'Canada','Ontario',@nYear);
-- Good Friday
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Good Friday',DateAdd(d,-2,@Easter),'Canada','Ontario',@nYear);

-------------------------------------------------------------------------------------------------

-- Fixed date holidays are loaded next
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('New Year''s Day',CONVERT(Date,'1/1/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Halloween',CONVERT(SmallDateTime,'10/31/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)



INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Family Day',dbo.FloatingDate(3,2,2,@nYear),'Canada','Ontario',@nYear) -- 3rd Monday in February

INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Labour Day',dbo.FloatingDate(1,2,9,@nYear),'Canada','Ontario',@nYear) -- 1rst Monday in September

INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Thanksgiving',dbo.FloatingDate(2,2,10,@nYear),'Canada','Ontario',@nYear) -- 2nd Monday in October

INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Civic Holiday',dbo.FloatingDate(1,2,8,@nYear),'Canada','Ontario',@nYear) -- 1st Monday in August

DECLARE @CanadaDayWD as int;
SELECT @CanadaDayWD = DATEPART(WEEKDAY, CAST(CAST(@nYear AS VARCHAR(4))+ '-07-01' as Date));
if(@CanadaDayWD = 1) -- Canada Day on Sunday
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Canada Day',CONVERT(Date,'7/2/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
if(@CanadaDayWD = 7) -- Canada Day on Saturday
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Canada Day',CONVERT(Date,'7/3/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
if(@CanadaDayWD = 2) OR (@CanadaDayWD = 3) OR (@CanadaDayWD = 4) OR (@CanadaDayWD = 5) OR (@CanadaDayWD = 6)
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Canada Day',CONVERT(Date,'7/1/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)

DECLARE @ChristmasWD as int;
SELECT @ChristmasWD = DATEPART(WEEKDAY, CAST(CAST(@nYear AS VARCHAR(4))+ '-12-25' as Date));
if(@ChristmasWD = 1) -- Christmas on Sunday
BEGIN
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/26/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/27/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END
if(@ChristmasWD = 7) -- Christmas on Saturday
BEGIN
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/27/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/28/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END
if(@ChristmasWD = 6) -- Christmas on FRIDAY
BEGIN
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/24/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/28/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END
if(@ChristmasWD = 2) OR (@ChristmasWD = 3) OR (@ChristmasWD = 4) OR (@ChristmasWD = 5) -- Christmas on Monday to Thursday
BEGIN
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/25/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/26/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END

-- Victoria Day
DECLARE @VictoriaDay as date;

SELECT @VictoriaDay = (dbo.FloatingDate(5,2,5,@nYear));
if(@VictoriaDay < CAST(CAST(@nYear AS VARCHAR(4))+ '-05-24' as Date))
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
Else
BEGIN 
	SELECT @VictoriaDay = (dbo.FloatingDate(4,2,5,@nYear));
	if(@VictoriaDay < CAST(CAST(@nYear AS VARCHAR(4))+ '-05-24' as Date))
		INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
	Else
	BEGIN 
		SELECT @VictoriaDay = (dbo.FloatingDate(3,2,5,@nYear));
		if(@VictoriaDay < CAST(CAST(@nYear AS VARCHAR(4))+ '-05-24' as Date))
			INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
		Else
		BEGIN 
			SELECT @VictoriaDay = (dbo.FloatingDate(3,2,5,@nYear));
			INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
		END

	END

END
RETURN
END

Screen Shot 2018-05-23 at 10.33.18 AM.png
Screen Shot 2018-05-23 at 10.33.18 AM.png (1.77 MB)
Download
1 0

Replies

replied on May 15, 2018

Hi Armando,

So, I found a function that might benefit you. So instead of creating the holidays in Workflow, you can create a user-defined function in SQL that stores holiday rules as procedural logic and computes the holiday per year. 

So you can apply the following query in SQL:

---------------------------------------------------------------------------------
-- Returns a virtual table containing all holidays for a given year
---------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[Holiday_List] (@nYear INT)
RETURNS @Holidays TABLE
(Holiday_name VARCHAR(32),
Holiday_date SMALLDATETIME
)
AS
BEGIN
-- Calculate Easter Sunday
DECLARE @g INT
DECLARE @c INT
DECLARE @h INT
DECLARE @i INT
DECLARE @j INT
DECLARE @l INT
DECLARE @Month INT
DECLARE @Day INT
DECLARE @Easter SMALLDATETIME
DECLARE @WorkDT SMALLDATETIME

-- Bizarre Algorithm to determine Easter Sunday
SET @g = @nYear % 19
SET @c = @nYear / 100
SET @h = ((@c - (@c / 4) - ((8 * @c + 13) / 25) + (19 * @g) + 15) % 30)
SET @i = @h - ((@h / 28) * (1 - (@h /28) * (29 / (@h + 1)) * ((21 - @g) / 11)))
SET @j = ((@nYear + (@nYear / 4) + @i + 2 - @c + (@c / 4)) % 7)
SET @l = @i - @j
SET @Month = 3 + ((@l + 40) / 44)
SET @Day = @l + 28 - (31 * (@Month / 4))
SET @Easter = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@nYear AS VARCHAR(4))

------------------------------------------------------------------------------------------------
-- Add Easter Sunday to holiday list, and get holidays based around Easter
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Easter',@Easter)
-- Good Friday
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Good Friday',DateAdd(d,-2,@Easter))
-- Palm Sunday
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Palm Sunday',DateAdd(ww,-1,@Easter))
-------------------------------------------------------------------------------------------------

-- Fixed date holidays are loaded next
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('New Year''s Day',CONVERT(Date,'1/1/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Groundhog Day',CONVERT(Date,'2/2/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Lincoln''s Birthday',CONVERT(SmallDateTime,'2/12/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Valentines Day',CONVERT(SmallDateTime,'2/14/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('St. Patrick''s Day',CONVERT(SmallDateTime,'3/17/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('April Fools Day',CONVERT(SmallDateTime,'4/1/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Flag Day',CONVERT(SmallDateTime,'6/14/'+CAST(@nYear AS VARCHAR(4))))
IF @nYear >=1776 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Independence Day',CONVERT(SmallDateTime,'7/4/'+CAST(@nYear AS VARCHAR(4))))
IF @nYear >=1958 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Boss''s Day',CONVERT(SmallDateTime,'10/16/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Halloween',CONVERT(SmallDateTime,'10/31/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Christmas',CONVERT(SmallDateTime,'12/25/'+CAST(@nYear AS VARCHAR(4))))
IF @nYear >=1966 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Kwanzaa',CONVERT(SmallDateTime,'12/26/'+CAST(@nYear AS VARCHAR(4))))

-- Holidays that full on the same day of the week (based on the year they were officially established)
IF @nYear>=1983 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Martin Luther King Day',dbo.FloatingDate(3,2,1,@nYear)) -- 3rd Monday in January
IF @nYear>=1993 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Take your Daughter to Work Day',dbo.FloatingDate(4,5,4,@nYear)) -- 4th Thursday in April
IF @nYear>=1908 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Mothers Day',dbo.FloatingDate(2,1,5,@nYear)) -- 2nd Sunday in May
IF @nYear>=1950 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Armed Forces Day',dbo.FloatingDate(3,7,5,@nYear)) -- 3rd Saturday in May
IF @nYear>=1910 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Fathers Day',dbo.FloatingDate(3,1,6,@nYear)) -- 3rd Sunday in June
IF @nYear>=1894 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Labor Day',dbo.FloatingDate(1,2,9,@nYear)) -- 1st Monday in September
IF @nYear>=1941 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Thanksgiving',dbo.FloatingDate(4,5,11,@nYear)) -- 4th Thursday in November

---------------------------------------------------------------------------------------
-- Federal holidays that only come every 4 years
---------------------------------------------------------------------------------------
IF (@nYear >= 1937) AND ((@nYear-1937) % 4)=0
BEGIN
 SET @WorkDT=CONVERT(SmallDateTime,'01/20/'+CAST(@nYear AS VARCHAR(4))) -- Get Inauguration day
 IF DatePart(dw,@WorkDT)=1 SET @WorkDt=DateAdd(d,1,@WorkDt) -- Move to Monday if it falls on Sunday
 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Inauguration Day',@WorkDt)
END

IF (@nYear < 1937) AND ((@nYear-1937) % 4)=0
BEGIN
-- Get Inauguration day for years prior to 1937
 SET @WorkDT = CONVERT(SmallDateTime,'03/04/'+CAST(@nYear AS VARCHAR(4)))
 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Inauguration Day',@WorkDt)
END
---------------------------------------------------------------------------------------
-- Holidays that change based upon year
---------------------------------------------------------------------------------------
IF @nYear >= 1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Presidents Day',dbo.FloatingDate(3,2,2,@nYear)) -- 3rd Monday in February
IF @nYear < 1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Washington''s Birthday',CONVERT(SmallDateTime,'2/22/'+CAST(@nYear AS VARCHAR(4))))

IF (@nYear >=1954)
 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Veteran''s Day',CONVERT(SmallDateTime,'11/11/'+CAST(@nYear AS VARCHAR(4))))
IF (@nYear >=1921 and @nYear < 1954)
 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Armistice',CONVERT(SmallDateTime,'11/11/'+CAST(@nYear AS VARCHAR(4))))

IF @nYear>=1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Columbus Day',dbo.FloatingDate(2,2,10,@nYear)) -- 2nd Monday in October
IF @nYear>=1937 and @nYear <1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Columbus Day',CONVERT(SmallDateTime,'10/12/'+CAST(@nYear AS VARCHAR(4))))
---------------------------------------------------------------------------------------
-- Day light savings start and end, based on US Federal law only
---------------------------------------------------------------------------------------
IF @nYear >= 1987 AND @nYear <2007
BEGIN
     INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Daylight Savings -Start',dbo.FloatingDate(1,1,4,@nYear)) -- 1st Sunday in April
 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Daylight Savings -End',dbo.FloatingDate(4,1,10,@nYear)) -- 4th Sunday in October
END

IF @nYear >= 2007
BEGIN
     INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Daylight Savings -Start',dbo.FloatingDate(2,1,3,@nYear)) -- 2nd Sunday in March
 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Daylight Savings -End',dbo.FloatingDate(1,1,11,@nYear)) -- 1st Sunday in November
END
RETURN
END
GO
CREATE FUNCTION dbo.FloatingDate(@Occur INT,@WeekDay INT,@Month INT,@Year INT)
RETURNS
SMALLDATETIME
AS
BEGIN
DECLARE @Result SMALLDATETIME
DECLARE @StartDate SMALLDATETIME

-- Get Starting date, which is first day of the month
SET @StartDate = CONVERT(SmallDateTime,CAST(@Month AS VARCHAR(2))+'/1/'+CAST(@Year AS VARCHAR(4)))

SET @Result = cast(str(@Month)+'/'+ str((7+ @Weekday-datepart(dw,@StartDate))%7+1) +'/'+str(@Year) AS datetime)+(@Occur-1)*7 

RETURN @Result
END

Note: this query is set for US holidays. So, I would highly recommend that you review the code and delete any holidays you don't wish to have or add any additional holidays. However, the calculations for Easter, Good Friday and Palm Sunday are already included in this query!

You can test out the accuracy of the function by using:

SELECT * FROM holiday_list(2018) ORDER BY Holiday_date

After you test the function, create a table in SQL that will house the holiday data. Make sure to include the two columns: Holiday Name and Holiday Date.

Then in Workflow, add the Custom Query activity to your canvas, select the correct database that has your function, and query:

SELECT * FROM holiday_list(?) ORDER BY Holiday_date

Type the Parameter name ? and type in the year you want to search for (e.g., 2018). Then click ok. After that, put a For Each Row activity on the canvas and put an Insert Data activity in there. Connect the Insert Data activity to the table you want to populate with holidays. After you've selected your table, insert the resulting values for the column. It should look similar to the image below.

Publish the workflow and run it. You should have all of your holidays in your designated SQL table.

Hope this helps!

 

1 0
replied on May 15, 2018

Let me look into this method and see what I can do!

SELECTED ANSWER
replied on May 23, 2018 • Show version history

So this worked out great we made some changes in the design by creating a workflow that looks at the most recent year and adds +1 to identify the year that needs to be populated this workflow then runs once a year. We are also using some stored procedures to populate and handle the calculations on forms for the date ranges to identify holiday dates and subtract from the total NETWORKDAYS.

Below is the updated SQL Script based on the Ontario Province Holiday Date Logic for businesses that are not open on Saturdays or Sundays.

USE [ExternalTables]
GO
/****** Object:  UserDefinedFunction [dbo].[Holiday_List_Ontario]    Script Date: 5/23/2018 10:35:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Holiday_List_Ontario] (@nYear INT)
RETURNS  @Holidays TABLE
(
	Holiday_Name VARCHAR(32),
	Holiday_Date SMALLDATETIME,
	Holiday_Country VARCHAR(32),
	Holiday_Province VARCHAR(32),
	Holiday_Year INT
)
AS
BEGIN
-- Calculate Easter Sunday
DECLARE @g INT
DECLARE @c INT
DECLARE @h INT
DECLARE @i INT
DECLARE @j INT
DECLARE @l INT
DECLARE @Month INT
DECLARE @Day INT
DECLARE @Easter SMALLDATETIME
DECLARE @WorkDT SMALLDATETIME

SET @g = @nYear % 19
SET @c = @nYear / 100
SET @h = ((@c - (@c / 4) - ((8 * @c + 13) / 25) + (19 * @g) + 15) % 30)
SET @i = @h - ((@h / 28) * (1 - (@h /28) * (29 / (@h + 1)) * ((21 - @g) / 11)))
SET @j = ((@nYear + (@nYear / 4) + @i + 2 - @c + (@c / 4)) % 7)
SET @l = @i - @j
SET @Month = 3 + ((@l + 40) / 44)
SET @Day = @l + 28 - (31 * (@Month / 4))
SET @Easter = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@nYear AS VARCHAR(4))

------------------------------------------------------------------------------------------------
-- Add Easter Sunday to holiday list, and get holidays based around Easter
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Easter',@Easter,'Canada','Ontario',@nYear);
-- Good Friday
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Good Friday',DateAdd(d,-2,@Easter),'Canada','Ontario',@nYear);

-------------------------------------------------------------------------------------------------

-- Fixed date holidays are loaded next
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('New Year''s Day',CONVERT(Date,'1/1/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Halloween',CONVERT(SmallDateTime,'10/31/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)



INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Family Day',dbo.FloatingDate(3,2,2,@nYear),'Canada','Ontario',@nYear) -- 3rd Monday in February

INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Labour Day',dbo.FloatingDate(1,2,9,@nYear),'Canada','Ontario',@nYear) -- 1rst Monday in September

INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Thanksgiving',dbo.FloatingDate(2,2,10,@nYear),'Canada','Ontario',@nYear) -- 2nd Monday in October

INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Civic Holiday',dbo.FloatingDate(1,2,8,@nYear),'Canada','Ontario',@nYear) -- 1st Monday in August

DECLARE @CanadaDayWD as int;
SELECT @CanadaDayWD = DATEPART(WEEKDAY, CAST(CAST(@nYear AS VARCHAR(4))+ '-07-01' as Date));
if(@CanadaDayWD = 1) -- Canada Day on Sunday
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Canada Day',CONVERT(Date,'7/2/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
if(@CanadaDayWD = 7) -- Canada Day on Saturday
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Canada Day',CONVERT(Date,'7/3/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
if(@CanadaDayWD = 2) OR (@CanadaDayWD = 3) OR (@CanadaDayWD = 4) OR (@CanadaDayWD = 5) OR (@CanadaDayWD = 6)
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Canada Day',CONVERT(Date,'7/1/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)

DECLARE @ChristmasWD as int;
SELECT @ChristmasWD = DATEPART(WEEKDAY, CAST(CAST(@nYear AS VARCHAR(4))+ '-12-25' as Date));
if(@ChristmasWD = 1) -- Christmas on Sunday
BEGIN
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/26/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/27/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END
if(@ChristmasWD = 7) -- Christmas on Saturday
BEGIN
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/27/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/28/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END
if(@ChristmasWD = 6) -- Christmas on FRIDAY
BEGIN
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/24/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/28/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END
if(@ChristmasWD = 2) OR (@ChristmasWD = 3) OR (@ChristmasWD = 4) OR (@ChristmasWD = 5) -- Christmas on Monday to Thursday
BEGIN
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/25/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/26/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END

-- Victoria Day
DECLARE @VictoriaDay as date;

SELECT @VictoriaDay = (dbo.FloatingDate(5,2,5,@nYear));
if(@VictoriaDay < CAST(CAST(@nYear AS VARCHAR(4))+ '-05-24' as Date))
	INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
Else
BEGIN 
	SELECT @VictoriaDay = (dbo.FloatingDate(4,2,5,@nYear));
	if(@VictoriaDay < CAST(CAST(@nYear AS VARCHAR(4))+ '-05-24' as Date))
		INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
	Else
	BEGIN 
		SELECT @VictoriaDay = (dbo.FloatingDate(3,2,5,@nYear));
		if(@VictoriaDay < CAST(CAST(@nYear AS VARCHAR(4))+ '-05-24' as Date))
			INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
		Else
		BEGIN 
			SELECT @VictoriaDay = (dbo.FloatingDate(3,2,5,@nYear));
			INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
		END

	END

END
RETURN
END

Screen Shot 2018-05-23 at 10.33.18 AM.png
Screen Shot 2018-05-23 at 10.33.18 AM.png (1.77 MB)
Download
1 0
replied on May 15, 2018 • Show version history

Using Workflow exclusively, this can be done, but it'll take a lot of token calculators, date token calculators and conditionals. Easiest way I can think of doing this is to run a loop with every date in the year as a cycle, and count the days of the week in regards to what month it is, and fire a condition to push it to SQL when the conditions are met. 

 

For example, you can use the Assign Token Value task, create a token with the date, and use the Token Dialog to apply the formatting dddd, which returns the day of the week from that date. Then every time you see a date that matches Monday when the month is say, January, increment a Monday counter by 1. If it equals 1, push that date to SQL for New Years. Repeat for all the months and holidays. 

 

Like I said, not the most elegant solution, but it should be easy to whip up, and work pretty reliably.

 

EDIT--

In case it isn't super clear, in order to find Good Friday, we'd first need to find the conditions of Easter Sunday, but without observing the moon. Looks like there are some methods to do this with math instead, which would make it easier. You can try looking here https://en.wikipedia.org/wiki/Computus for resources. Barring that, you may need to manually plug in Good Friday if a calculation method isn't available, or reach out to an external data resource to pull from automatically.

0 0
replied on May 15, 2018

It requires several external tables for the calculations to get to Good Friday. The easiest would be if we could use the existing workflow admin console Holiday data somehow? If the feature exists in workflow why cant we create some sort of token from that? 

Maybe this should be put in as a feature request as date manipulation is becoming more and more important especially within forms as things like timesheets, intake processes, scheduling tools etc are becoming more and more popular.

0 0
replied on May 15, 2018

It is possible to go right into the LF databases and poke around, but it isn't explicitly condoned by LF, so you'd be on your own with that. It would be nice to not need to reinvent the wheel, but that's not really an option to reliably use. It could absolutely be a feature request.

Alternately, I did find a VB script you can drop into a Script activity (and modify slightly to work with Workflow tokens) what calculates the date of Easter Sunday here: https://ss64.com/vb/syntax-easter.html. 

 

Frankly, it would be almost easier to just write up a quick piece of software to populate SQL or a CSV for you in VB/C++/C#/Python, as a workflow like this wouldn't really have much bearing to the rest of Laserfiche, and isn't super well suited for the job. Can it work? Absolutely. Will it be easy? Not really.

0 0
replied on May 17, 2018

So for Ontario revised and updated the script works great :), workflow can then each year create and update the table for the next two years.

 

0 0
replied on May 23, 2018

SQL Script for Canadian Holidays

``USE [ExternalTables]
GO
/****** Object:  UserDefinedFunction [dbo].[Holiday_List_Ontario]    Script Date: 5/23/2018 10:35:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Holiday_List_Ontario] (@nYear INT)
RETURNS  @Holidays TABLE
(
    Holiday_Name VARCHAR(32),
    Holiday_Date SMALLDATETIME,
    Holiday_Country VARCHAR(32),
    Holiday_Province VARCHAR(32),
    Holiday_Year INT
)
AS
BEGIN
-- Calculate Easter Sunday
DECLARE @g INT
DECLARE @c INT
DECLARE @h INT
DECLARE @i INT
DECLARE @j INT
DECLARE @l INT
DECLARE @Month INT
DECLARE @Day INT
DECLARE @Easter SMALLDATETIME
DECLARE @WorkDT SMALLDATETIME

SET @g = @nYear % 19
SET @c = @nYear / 100
SET @h = ((@c - (@c / 4) - ((8 * @c + 13) / 25) + (19 * @g) + 15) % 30)
SET @i = @h - ((@h / 28) * (1 - (@h /28) * (29 / (@h + 1)) * ((21 - @g) / 11)))
SET @j = ((@nYear + (@nYear / 4) + @i + 2 - @c + (@c / 4)) % 7)
SET @l = @i - @j
SET @Month = 3 + ((@l + 40) / 44)
SET @Day = @l + 28 - (31 * (@Month / 4))
SET @Easter = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@nYear AS VARCHAR(4))

------------------------------------------------------------------------------------------------
-- Add Easter Sunday to holiday list, and get holidays based around Easter
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Easter',@Easter,'Canada','Ontario',@nYear);
-- Good Friday
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Good Friday',DateAdd(d,-2,@Easter),'Canada','Ontario',@nYear);

-------------------------------------------------------------------------------------------------

-- Fixed date holidays are loaded next
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('New Year''s Day',CONVERT(Date,'1/1/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Halloween',CONVERT(SmallDateTime,'10/31/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)

 

INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Family Day',dbo.FloatingDate(3,2,2,@nYear),'Canada','Ontario',@nYear) -- 3rd Monday in February

INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Labour Day',dbo.FloatingDate(1,2,9,@nYear),'Canada','Ontario',@nYear) -- 1rst Monday in September

INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Thanksgiving',dbo.FloatingDate(2,2,10,@nYear),'Canada','Ontario',@nYear) -- 2nd Monday in October

INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Civic Holiday',dbo.FloatingDate(1,2,8,@nYear),'Canada','Ontario',@nYear) -- 1st Monday in August

DECLARE @CanadaDayWD as int;
SELECT @CanadaDayWD = DATEPART(WEEKDAY, CAST(CAST(@nYear AS VARCHAR(4))+ '-07-01' as Date));
if(@CanadaDayWD = 1) -- Canada Day on Sunday
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Canada Day',CONVERT(Date,'7/2/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
if(@CanadaDayWD = 7) -- Canada Day on Saturday
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Canada Day',CONVERT(Date,'7/3/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
if(@CanadaDayWD = 2) OR (@CanadaDayWD = 3) OR (@CanadaDayWD = 4) OR (@CanadaDayWD = 5) OR (@CanadaDayWD = 6)
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Canada Day',CONVERT(Date,'7/1/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)

DECLARE @ChristmasWD as int;
SELECT @ChristmasWD = DATEPART(WEEKDAY, CAST(CAST(@nYear AS VARCHAR(4))+ '-12-25' as Date));
if(@ChristmasWD = 1) -- Christmas on Sunday
BEGIN
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/26/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/27/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END
if(@ChristmasWD = 7) -- Christmas on Saturday
BEGIN
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/27/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/28/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END
if(@ChristmasWD = 6) -- Christmas on FRIDAY
BEGIN
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/24/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/28/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END
if(@ChristmasWD = 2) OR (@ChristmasWD = 3) OR (@ChristmasWD = 4) OR (@ChristmasWD = 5) -- Christmas on Monday to Thursday
BEGIN
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Christmas',CONVERT(Date,'12/25/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Boxing Day',CONVERT(Date,'12/26/'+CAST(@nYear AS VARCHAR(4))),'Canada','Ontario',@nYear)
END

-- Victoria Day
DECLARE @VictoriaDay as date;

SELECT @VictoriaDay = (dbo.FloatingDate(5,2,5,@nYear));
if(@VictoriaDay < CAST(CAST(@nYear AS VARCHAR(4))+ '-05-24' as Date))
    INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
Else
BEGIN 
    SELECT @VictoriaDay = (dbo.FloatingDate(4,2,5,@nYear));
    if(@VictoriaDay < CAST(CAST(@nYear AS VARCHAR(4))+ '-05-24' as Date))
        INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
    Else
    BEGIN 
        SELECT @VictoriaDay = (dbo.FloatingDate(3,2,5,@nYear));
        if(@VictoriaDay < CAST(CAST(@nYear AS VARCHAR(4))+ '-05-24' as Date))
            INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
        Else
        BEGIN 
            SELECT @VictoriaDay = (dbo.FloatingDate(3,2,5,@nYear));
            INSERT INTO @Holidays (Holiday_name,Holiday_date,Holiday_Country,Holiday_Province,Holiday_Year) VALUES ('Victoria Day',@VictoriaDay,'Canada','Ontario',@nYear);
        END

    END

END
RETURN
END``

You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Attachments
Related Posts
Loading ...