/****** Object:  UserDefinedFunction [dbo].[fnGetUKHolidays] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--Returns a list of Calculated Dates for a given year
--Is accurate for years 2000 - 2019 
--May be accurate for years outside this range
--Legislation may add new Dates, or move the calculated Dates around
--Writen By PJR 20160205
--Modified By PJR 20190609 - Change announced to MayDay holiday in 2020 because of VE day 75th anniversary
--Code from many prior giants borrowed gratefully and built on
-- =============================================
Create Function [dbo].[fnGetUKHolidays]( @siYear Int = 0 )
Returns @tHolidays Table(
ItemID Int,
HolidayName VarChar(50),
HolidayDate Date
)
As
Begin
	Declare @siMonth Int
	Declare @siDay Int
	Declare @strHolidayName VarChar(50)
	Declare @dtHolidayDate Date
	Declare @dtTmpDate Date
	Declare @siCount Int
	Declare @X Int

	Set @siCount = 1

-- Calculate New Years Day
	Set @strHolidayName = 'New Years Day'
	Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/1/1' As Date)
	Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
	Set @siCount = @siCount + 1

	If DateName(DW, @dtHolidayDate) = 'Saturday'
	Begin
		Set @strHolidayName = 'New Years Bank Holiday'
		Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/1/3' As Date)
		Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
		Set @siCount = @siCount + 1
	End
	If DateName(DW, @dtHolidayDate) = 'Sunday'
	Begin
		Set @strHolidayName = 'New Years Bank Holiday'
		Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/1/2' As Date)
		Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
		Set @siCount = @siCount + 1
	End

-- Calculate Easter Sunday
-- Alogrithm modeled after VB2TheMax code
	Declare @g Int
	Declare @c Int
	Declare @h Int
	Declare @i Int
	Declare @j Int
	Declare @l Int

	Set @g = @siYear % 19
	Set @c = @siYear / 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 = ((@siYear + (@siYear / 4) + @i + 2 - @c + (@c / 4)) % 7)
	Set @l = @i - @j
	Set @siMonth = 3 + ((@l + 40) / 44)
	Set @siDay = @l + 28 - (31 * (@siMonth / 4))
	Set @strHolidayName = 'Easter Sunday'
	Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/' + Cast(@siMonth As VarChar(2)) + '/' + Cast(@siDay As VarChar(2)) As Date)

	Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, 'Good Friday', DateAdd(dd, -2, @dtHolidayDate))
	Set @siCount = @siCount + 1
	Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
	Set @siCount = @siCount + 1
	Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, 'Easter Monday', DateAdd(dd, 1, @dtHolidayDate))
	Set @siCount = @siCount + 1

--One-Off Royal Wedding holiday
	If @siYear = 2011
	Begin
		Set @dtHolidayDate = '2011/4/29'
		Set @strHolidayName = 'Royal Wedding'
		Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
		Set @siCount = @siCount + 1
	End


-- Calculate May Day
	Set @siMonth = 5
	Set @strHolidayName = 'May Day Bank Holiday'
	Set @X = 1
-- Modified By PJR 20190609 - Change announced to MayDay holiday in 2020 because of VE day 75th anniversary
	If @siYear In (1995, 2020)
	Begin
		Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/5/8' As Date)
	End 
	Else
	Begin
		While @X <= 31
		Begin
			If DateName(DW, Cast(Cast(@siYear As VarChar(4)) + '/5/' + Cast(@X As VarChar(2)) As Date)) = 'Monday'
			Begin
				Set @siDay = @X
				Set @X = 99
			End
			Set @X = @X + 1
		End
		Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/' + Cast(@siMonth As VarChar(2)) + '/' + Cast(@siDay As VarChar(2)) As Date)
	End 

	Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
	Set @siCount = @siCount + 1


-- Calculate Spring Bank Holiday
	Set @siMonth = 5
	Set @X = 31

	If @siYear = 2002
	Begin
		Set @dtHolidayDate = '2002/6/3'
		Set @strHolidayName = 'Golden Jubilee'
		Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
		Set @siCount = @siCount + 1
		Set @dtHolidayDate = '2002/6/4'
		Set @strHolidayName = 'Spring Bank Holiday'
		Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
		Set @siCount = @siCount + 1
	End
	Else
	Begin
		If @siYear = 2012
		Begin
			Set @dtHolidayDate = '2012/6/4'
			Set @strHolidayName = 'Spring Bank Holiday'
			Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
			Set @siCount = @siCount + 1
			Set @dtHolidayDate = '2012/6/5'
			Set @strHolidayName = 'Diamond Jubilee'
			Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
			Set @siCount = @siCount + 1
		End
		Else
		Begin
			Set @strHolidayName = 'Spring Bank Holiday'
			While @X >= 1
			Begin
				If DateName(DW, Cast(Cast(@siYear As VarChar(4)) + '/5/' + Cast(@X As VarChar(2)) As Date)) = 'Monday'
				Begin
					Set @siDay = @X
					Set @X = 0
				End
				Set @X = @X - 1
			End
			Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/' + Cast(@siMonth As VarChar(2)) + '/' + Cast(@siDay As VarChar(2)) As Date)

			Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
			Set @siCount = @siCount + 1
		End
	End

-- Calculate Summer Bank Holiday
	Set @siMonth = 8
	Set @strHolidayName = 'Summer Bank Holiday'
	Set @X = 31

	While @X >= 1
	Begin
		If DateName(DW, Cast(Cast(@siYear As VarChar(4)) + '/8/' + Cast(@X As VarChar(2)) As Date)) = 'Monday'
		Begin
			Set @siDay = @X
			Set @X = 0
		End
		Set @X = @X - 1
	End
	Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/' + Cast(@siMonth As VarChar(2)) + '/' + Cast(@siDay As VarChar(2)) As Date)

	Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
	Set @siCount = @siCount + 1


-- Calculate Christmas and Boxing Day
	Set @strHolidayName = 'Christmas Day'
	Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/12/25' As Date)
	Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
	Set @siCount = @siCount + 1
	Set @dtTmpDate = @dtHolidayDate

	Set @strHolidayName = 'Boxing Day'
	Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/12/26' As Date)
	Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
	Set @siCount = @siCount + 1

	If DateName(DW, @dtTmpDate) = 'Friday'
	Begin
		Set @strHolidayName = 'Boxing Day Bank Holiday'
		Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/12/28' As Date)
		Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
		Set @siCount = @siCount + 1
	End
	If DateName(DW, @dtTmpDate) = 'Saturday'
	Begin
		Set @strHolidayName = 'Christmas Bank Holiday'
		Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/12/27' As Date)
		Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
		Set @siCount = @siCount + 1
		Set @strHolidayName = 'Boxing Day Bank Holiday'
		Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/12/28' As Date)
		Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
		Set @siCount = @siCount + 1
	End
	If DateName(DW, @dtTmpDate) = 'Sunday'
	Begin
		Set @strHolidayName = 'Christmas Bank Holiday'
		Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/12/27' As Date)
		Insert Into @tHolidays (ItemID, HolidayName, HolidayDate) Values (@siCount, @strHolidayName, @dtHolidayDate)
		Set @siCount = @siCount + 1
	End

	Return
End