Created
August 19, 2014 04:30
-
-
Save kiwipiet/5cfa6ecaced3c469a521 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE FUNCTION [dbo].[GetWellingtonHolidays] | |
| ( | |
| @startdate DATE, | |
| @enddate DATE | |
| ) | |
| RETURNS TABLE | |
| WITH SCHEMABINDING | |
| AS RETURN | |
| ( | |
| WITH alldates ([date]) AS ( | |
| SELECT | |
| DATEADD(day, [Id] - 1, @startdate) | |
| FROM [dbo].[GetIntegerSequence] (CASE WHEN DATEDIFF(day, @startdate, @enddate) + 1 >= 0 THEN DATEDIFF(day, @startdate, @enddate) + 1 ELSE 0 END) | |
| ) | |
| SELECT | |
| [date] AS [Holiday] | |
| FROM | |
| alldates | |
| WHERE (DATEPART(weekday,[date]) + @@DATEFIRST) % 7 IN (0,1) | |
| UNION | |
| SELECT [date] | |
| FROM | |
| ( | |
| VALUES | |
| -- Imported public holidays from http://www.dol.govt.nz/ | |
| (DATEFROMPARTS(2008,12,25)) -- Christmas Day | |
| , ('2008-12-26') -- Boxing Day | |
| , ('2009-01-01') -- New Year's Day | |
| , ('2009-01-02') -- Day after New Year's Day | |
| , ('2009-01-19') -- Wellington Anniversary Day | |
| , ('2009-02-06') -- Waitangi Day | |
| , ('2009-04-10') -- Good Friday | |
| , ('2009-04-13') -- Easter Monday | |
| , ('2009-04-25') -- ANZAC Day | |
| , ('2009-06-01') -- Queen's Birthday | |
| , ('2009-10-26') -- Labour Day | |
| , ('2009-12-25') -- Christmas Day | |
| , ('2009-12-26') -- Boxing Day (Public Holiday for employees who would normally work on Saturday) | |
| , ('2009-12-28') -- Boxing Day Public Holiday for employees who would not normally work on Saturday | |
| , ('2010-01-01') -- New Year's Day | |
| , ('2010-01-02') -- Day after New Year's Day (Public Holiday for employees who would normally work on Saturday) | |
| , ('2010-01-04') -- Day after New Year's Day Public Holiday for employees who would not normally work on Saturday | |
| , ('2010-01-25') -- Wellington Anniversary Day | |
| , ('2010-02-06') -- Waitangi Day | |
| , ('2010-04-02') -- Good Friday | |
| , ('2010-04-05') -- Easter Monday | |
| , ('2010-04-25') -- ANZAC Day | |
| , ('2010-06-07') -- Queen's Birthday | |
| , ('2010-10-25') -- Labour Day | |
| , ('2010-12-25') -- Christmas Day (Public Holiday for employees who would normally work on Saturday) | |
| , ('2010-12-26') -- Boxing Day (Public Holiday for employees who would normally work on Sunday) | |
| , ('2010-12-27') -- Christmas Day Public Holiday for employees who would not normally work on Saturday | |
| , ('2010-12-28') -- Boxing Day Public Holiday for employees who would not normally work on Sunday | |
| , ('2011-01-01') -- New Year's Day (Public Holiday for employees who would normally work on Saturday) | |
| , ('2011-01-02') -- Day after New Year's Day (Public Holiday for employees who would normally work on Sunday) | |
| , ('2011-01-03') -- New Year's Day Public Holiday for employees who would not normally work on Saturday | |
| , ('2011-01-04') -- Day after New Year's Day Public Holiday for employees who would not normally work on Sunday | |
| , ('2011-01-24') -- Wellington Anniversary Day | |
| , ('2011-02-06') -- Waitangi Day | |
| , ('2011-04-22') -- Good Friday | |
| , ('2011-04-25') -- Easter Monday | |
| , ('2011-04-25') -- ANZAC Day | |
| , ('2011-06-06') -- Queen's Birthday | |
| , ('2011-10-24') -- Labour Day | |
| , ('2011-12-25') -- Christmas Day (Public Holiday for employees who would normally work on Sunday) | |
| , ('2011-12-26') -- Boxing Day | |
| , ('2011-12-27') -- Christmas Day (Public Holiday for employees who would not normally work on Sunday) | |
| , ('2012-01-01') -- New Year's Day (Public Holiday for employees who would normally work on Sunday) | |
| , ('2012-01-02') -- Day after New Year's Day | |
| , ('2012-01-03') -- New Year's Day Public Holiday for employees who would not normally work on Sunday | |
| , ('2012-01-23') -- Wellington Anniversary Day | |
| , ('2012-02-06') -- Waitangi Day | |
| , ('2012-04-06') -- Good Friday | |
| , ('2012-04-09') -- Easter Monday | |
| , ('2012-04-25') -- ANZAC Day | |
| , ('2012-06-04') -- Queen's Birthday | |
| , ('2012-10-22') -- Labour Day | |
| , ('2012-12-25') -- Christmas Day | |
| , ('2012-12-26') -- Boxing Day | |
| , ('2013-01-01') -- New Year's Day | |
| , ('2013-01-02') -- Day after New Year's Day | |
| , ('2013-01-21') -- Wellington Anniversary Day | |
| , ('2013-02-06') -- Waitangi Day | |
| , ('2013-03-29') -- Good Friday | |
| , ('2013-04-01') -- Easter Monday | |
| , ('2013-04-25') -- ANZAC Day | |
| , ('2013-06-03') -- Queen's Birthday | |
| , ('2013-10-28') -- Labour Day | |
| , ('2013-12-25') -- Christmas Day | |
| , ('2013-12-26') -- Boxing Day | |
| , ('2014-01-01') -- New Year's Day | |
| , ('2014-01-02') -- Day after New Year's Day | |
| , ('2014-01-20') -- Wellington Anniversary Day | |
| , ('2014-02-06') -- Waitangi Day | |
| , ('2014-04-18') -- Good Friday | |
| , ('2014-04-21') -- Easter Monday | |
| , ('2014-04-25') -- ANZAC Day | |
| , ('2014-06-02') -- Queen's Birthday | |
| , ('2014-10-27') -- Labour Day | |
| , ('2014-12-25') -- Christmas Day | |
| , ('2014-12-26') -- Boxing Day | |
| , ('2015-01-01') -- New Year's Day | |
| , ('2015-01-02') -- Day after New Year's Day | |
| , ('2015-01-19') -- Wellington Anniversary Day | |
| , ('2015-02-06') -- Waitangi Day | |
| , ('2015-04-03') -- Good Friday | |
| , ('2015-04-06') -- Easter Monday | |
| , ('2015-04-25') -- ANZAC Day | |
| , ('2015-04-27') -- ANZAC Day (Mondayised) | |
| , ('2015-06-01') -- Queen's Birthday | |
| , ('2015-10-26') -- Labour Day | |
| , ('2015-12-25') -- Christmas Day | |
| , ('2015-12-26') -- Boxing Day (Public Holiday for employees who would normally work on Saturday) | |
| , ('2015-12-28') -- Boxing Day (Public Holiday for employees who would not normally work on Saturday) | |
| , ('2016-01-01') -- New Year's Day | |
| , ('2016-01-02') -- Day after New Year's Day (Public Holiday for employees who would normally work on Saturday) | |
| , ('2016-01-04') -- Day after New Year's Day (Public Holiday for employees who would not normally work on Saturday) | |
| , ('2016-01-25') -- Wellington Anniversary Day | |
| , ('2016-02-06') -- Waitangi Day | |
| , ('2016-02-08') -- Waitangi Day (Mondayised) | |
| , ('2016-03-25') -- Good Friday | |
| , ('2016-03-28') -- Easter Monday | |
| , ('2016-04-25') -- ANZAC Day | |
| , ('2016-06-06') -- Queen's Birthday | |
| , ('2016-10-24') -- Labour Day | |
| , ('2016-12-25') -- Christmas Day (Public Holiday for employees who would normally work on Sunday) | |
| , ('2016-12-26') -- Boxing Day | |
| , ('2016-12-27') -- Christmas Day (Public Holiday for employees who would not normally work on Sunday) | |
| , ('2017-01-01') -- New Year's Day (Public Holiday for employees who would normally work on Sunday) | |
| , ('2017-01-02') -- Day after New Year's Day | |
| , ('2017-01-03') -- New Year's Day (Public Holiday for employees who would not normally work on Sunday) | |
| , ('2017-01-23') -- Wellington Anniversary | |
| , ('2017-02-06') -- Waitangi Day | |
| , ('2017-04-14') -- Good Friday | |
| , ('2017-04-17') -- Easter Monday | |
| , ('2017-04-25') -- ANZAC Day | |
| , ('2017-06-05') -- Queen's Birthday | |
| , ('2017-10-23') -- Labour Day | |
| , ('2017-12-25') -- Christmas Day | |
| , ('2017-12-26') -- Boxing Day | |
| , ('2018-01-01') -- New Year's Day | |
| , ('2018-01-02') -- Day after New Year's Day | |
| , ('2018-01-22') -- Wellington Anniversary | |
| , ('2018-02-06') -- Waitangi Day | |
| , ('2018-03-30') -- Good Friday | |
| , ('2018-04-02') -- Easter Monday | |
| , ('2018-04-25') -- ANZAC Day | |
| , ('2018-06-04') -- Queen's Birthday | |
| , ('2018-10-22') -- Labour Day | |
| , ('2018-12-25') -- Christmas Day | |
| , ('2018-12-26') -- Boxing Day | |
| , ('2019-01-01') -- New Year's Day | |
| , ('2019-01-02') -- Day after New Year's Day | |
| , ('2019-01-21') -- Wellington Anniversary | |
| , ('2019-02-06') -- Waitangi Day | |
| , ('2019-04-19') -- Good Friday | |
| , ('2019-04-22') -- Easter Monday | |
| , ('2019-04-25') -- ANZAC Day | |
| , ('2019-06-03') -- Queen's Birthday | |
| , ('2019-10-28') -- Labour Day | |
| , ('2019-12-25') -- Christmas Day | |
| , ('2019-12-26') -- Boxing Day | |
| , ('2020-01-01') -- New Year's Day | |
| , ('2020-01-02') -- Day after New Year's Day | |
| , ('2020-01-20') -- Wellington Anniversary | |
| , ('2020-02-06') -- Waitangi Day | |
| , ('2020-04-10') -- Good Friday | |
| , ('2020-04-13') -- Easter Monday | |
| , ('2020-04-25') -- ANZAC Day | |
| , ('2020-04-27') -- ANZAC Day (Mondayised) | |
| , ('2020-06-01') -- Queen's Birthday | |
| , ('2020-10-26') -- Labour Day | |
| , ('2020-12-25') -- Christmas Day | |
| , ('2020-12-26') -- Boxing Day (Public Holiday for employees who would normally work on Saturday) | |
| , ('2020-12-28') -- Boxing Day (Public Holiday for employees who would not normally work on Saturday) | |
| , ('2021-01-01') -- New Year's Day | |
| , ('2021-01-02') -- Day after New Year's Day (Public Holiday for employees who would normally work on Saturday) | |
| , ('2021-01-04') -- Day after New Year's Day (Public Holiday for employees who would not normally work on Saturday) | |
| , ('2021-01-25') -- Wellington Anniversary | |
| , ('2021-02-06') -- Waitangi Day | |
| , ('2021-02-08') -- Waitangi Day (Mondayised) | |
| , ('2021-04-02') -- Good Friday | |
| , ('2021-04-05') -- Easter Monday | |
| , ('2021-04-25') -- ANZAC Day | |
| , ('2021-04-26') -- ANZAC Day (Mondayised) | |
| , ('2021-06-07') -- Queen's Birthday | |
| , ('2021-10-25') -- Labour Day | |
| , ('2021-12-25') -- Christmas Day (Public Holiday for employees who would normally work on Saturday) | |
| , ('2021-12-26') -- Boxing Day (Public Holiday for employees who would normally work on Sunday) | |
| , ('2021-12-27') -- Christmas Day (Public Holiday for employees who would not normally work on Saturday) | |
| , ('2021-12-28') -- Boxing Day (Public Holiday for employees who would not normally work on Sunday) | |
| ) dt([date]) | |
| WHERE | |
| [date] BETWEEN @startdate AND @enddate | |
| ) | |
| GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment