mirror of
https://github.com/acedanger/budget-database.git
synced 2025-12-05 14:40:14 -08:00
128 lines
7.4 KiB
Transact-SQL
128 lines
7.4 KiB
Transact-SQL
/*
|
||
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
|
||
*/ |