103 lines
2.6 KiB
Transact-SQL
103 lines
2.6 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
IF NOT EXISTS(SELECT * FROM sys.columns
|
|
WHERE Name = N'StartDate' AND Object_ID = Object_ID(N'Holiday'))
|
|
BEGIN
|
|
ALTER TABLE Holiday ADD StartDate datetime NULL
|
|
END
|
|
GO
|
|
|
|
IF NOT EXISTS(SELECT * FROM sys.columns
|
|
WHERE Name = N'EndDate' AND Object_ID = Object_ID(N'Holiday'))
|
|
BEGIN
|
|
ALTER TABLE Holiday ADD EndDate datetime NULL
|
|
END
|
|
GO
|
|
|
|
IF NOT EXISTS(SELECT * FROM sys.columns
|
|
WHERE Name = N'CompanyImpact' AND Object_ID = Object_ID(N'Holiday'))
|
|
BEGIN
|
|
ALTER TABLE Holiday ADD CompanyImpact bit NULL default 1
|
|
END
|
|
GO
|
|
|
|
CREATE FUNCTION GetHolidayDate(@occurrenceType AS SMALLINT, @month AS SMALLINT, @day AS SMALLINT, @weekDay AS SMALLINT)
|
|
RETURNS datetime AS
|
|
BEGIN
|
|
DECLARE @Result datetime;
|
|
DECLARE @Today datetime;
|
|
DECLARE @TmpDate datetime;
|
|
DECLARE @DayOfWeek INT;
|
|
SET @Today = GetDate();
|
|
|
|
IF (@occurrenceType IN (0, 6, 7))
|
|
SET @Result = DateAdd(day, @day - 1, DateAdd(month, @month - 1, DateAdd(Year, YEAR(@Today)-1900, 0)))
|
|
|
|
IF (@occurrenceType IN (1, 2, 3, 4))
|
|
BEGIN
|
|
SET @TmpDate = DateAdd(day, 1 - 1, DateAdd(month, @month - 1, DateAdd(Year, YEAR(@Today)-1900, 0)))
|
|
SET @DayOfWeek = datepart(dw, @TmpDate);
|
|
|
|
WHILE (@DayOfWeek <> @weekDay)
|
|
BEGIN
|
|
SET @TmpDate = DATEADD(DD, 1, @TmpDate)
|
|
SET @DayOfWeek = datepart(dw, @TmpDate);
|
|
END
|
|
|
|
SET @Result = @TmpDate
|
|
|
|
IF (@occurrenceType > 1)
|
|
SET @Result = DATEADD(DD , 7 * (@occurrenceType - 1) , @TmpDate)
|
|
END
|
|
|
|
IF (@occurrenceType = 5)
|
|
BEGIN
|
|
SET @TmpDate = DateAdd(day, 1 - 1, DateAdd(month, @month - 1, DateAdd(Year, YEAR(@Today)-1900, 0)))
|
|
SET @DayOfWeek = datepart(dw, @TmpDate);
|
|
|
|
WHILE ((@DayOfWeek <> @weekDay) AND (MONTH(@TmpDate) = MONTH(DATEADD(DD, 1, @TmpDate))))
|
|
BEGIN
|
|
SET @TmpDate = DATEADD(DD , 1 , @TmpDate)
|
|
SET @DayOfWeek = datepart(dw, @TmpDate);
|
|
END
|
|
|
|
WHILE (MONTH(@TmpDate) = MONTH(DATEADD(DD, 7, @TmpDate)))
|
|
SET @TmpDate = DATEADD(DD, 7, @TmpDate)
|
|
|
|
SET @Result = @TmpDate
|
|
END
|
|
|
|
RETURN @Result
|
|
END
|
|
GO
|
|
|
|
UPDATE Holiday SET CompanyImpact = 1 WHERE CompanyImpact IS NULL
|
|
GO
|
|
|
|
UPDATE Holiday SET StartDate =
|
|
dbo.GetHolidayDate(OccurrenceType, OccurrenceMonth, OccurrenceMonthDay, OccurrenceWeekDay) WHERE StartDate IS NULL
|
|
GO
|
|
|
|
UPDATE Holiday SET EndDate = StartDate WHERE EndDate IS NULL
|
|
GO
|
|
|
|
ALTER TABLE Holiday ALTER COLUMN CompanyImpact bit NOT NULL
|
|
GO
|
|
|
|
ALTER TABLE Holiday ALTER COLUMN StartDate datetime NOT NULL
|
|
GO
|
|
|
|
ALTER TABLE Holiday ALTER COLUMN EndDate datetime NOT NULL
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.columns
|
|
WHERE Name = N'WorkingDay' AND Object_ID = Object_ID(N'Holiday'))
|
|
BEGIN
|
|
EXEC sp_rename 'Holiday.WorkingDay', 'WorkingDays', 'COLUMN';
|
|
end
|
|
go
|
|
|
|
DROP FUNCTION GetHolidayDate
|
|
GO
|