/*
	Store federal holidays from 2019 to 40 years in the future.

	Holiday table   : dbo.Holidays
	Source of logic : https://www.codeproject.com/Tips/5284659/Holidays-Table-and-GetWorkDays-Function-in-SQL-Ser
	********************************************************
	Holidays are calculated in function dbo.GetHoliday
	
	To repopulate the dbo.Holiday table
		truncate table dbo.Holidays
		exec dbo.PROC_UPDATE_HOLIDAYS_TABLE
*/

alter FUNCTION [dbo].[ShiftHolidayToWorkday](@date date)
RETURNS date
AS
BEGIN
    IF DATENAME( dw, @date ) = 'Saturday'
        SET @Date = DATEADD(day, -1, @Date)

    ELSE IF DATENAME( dw, @date ) = 'Sunday'
        SET @Date = DATEADD(day, 1, @Date)

    RETURN @date
END
go

alter FUNCTION [dbo].[GetHoliday](@date date)
RETURNS varchar(50)
AS
BEGIN
    declare @s varchar(50)

    SELECT @s = 
		CASE
			WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-01-01') = @date 
				THEN 'New Year'
			WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]+1) + '-01-01') = @date 
				THEN 'New Year'
			WHEN [Month] = 1 AND [DayOfMonth] BETWEEN 15 AND 21 AND [DayName] = 'Monday' 
				THEN 'Martin Luther King Jr Day'
			when [Month] = 2 and [DayOfMonth] between 15 and 21 and [DayName] = 'Monday' 
				then 'Presidents'' Day'
			WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' 
				THEN 'Memorial Day'
			WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-06-19') = @date 
				THEN 'Juneteenth'
			WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-07-04') = @date 
				THEN 'Independence Day'
			WHEN [Month] = 9 AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' 
				THEN 'Labor Day'
			WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year])   + '-11-11') = @date 
				THEN 'Veteran''s Day'
			when [Month] = 10 and [DayOfMonth] between 8 and 14 and [DayName] = 'Monday' 
				then 'Columbus Day'
			WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' 
				THEN 'Thanksgiving Day'
			--WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 23 AND 29 AND [DayName] = 'Friday' 
			--	THEN 'Day After Thanksgiving'
			WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-12-25') = @date 
				THEN 'Christmas Day'
			--WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year])   + '-12-31') = @date 
			--	THEN 'New Years Eve'
			ELSE NULL 
		END
    FROM (
        SELECT
            [Year] = YEAR(@date),
            [Month] = MONTH(@date),
            [DayOfMonth] = DAY(@date),
            [DayName] = DATENAME(weekday,@date)
    ) c

    RETURN @s
END
go

alter FUNCTION [dbo].GetHolidays(@year int)
RETURNS TABLE 
AS
RETURN (  
    select dt, dbo.GetHoliday(dt) as Holiday
    from (
        select dateadd(day, number, convert(varchar,@year) + '-01-01') dt
        from master..spt_values spt
        where type='p' 
        ) d
    where year(dt) = @year and dbo.GetHoliday(dt) is not null
)

go


create proc dbo.PROC_UPDATE_HOLIDAYS_TABLE
as

if not exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Holidays')
    create table Holidays(dt date primary key clustered, Holiday varchar(50))

declare @year int = 2019


while @year < year(getdate()) + 40
begin
    insert into Holidays(dt, Holiday)
    select a.dt, a.Holiday
    from dbo.GetHolidays(@year) a
        left join Holidays b on b.dt = a.dt
    where b.dt is null

    set @year = @year + 1
end

go
/*
truncate table dbo.Holidays
exec dbo.UpdateHolidaysTable

select dt, Holiday, DayOfWeek=DATENAME(dw, dt) from dbo.GetHolidays(2021)
order by dt

select * from dbo.Holidays

select YR=year(dt), HolidayCount=count(1)
from dbo.Holidays
group by year(dt)
order by year(dt) asc
*/