SQL Disco

IF OBJECT_ID('fEasterByYear',N'FN') IS NOT NULL DROP FUNCTION fEasterByYear
 
GO
 
CREATE FUNCTION [dbo].[fEasterByYear] (@Year SMALLINT) RETURNS DATE AS
BEGIN 
	DECLARE
		@c INT,
		@n INT,
		@k INT, 
		@i INT, 
		@j INT, 
		@l INT, 
		@m INT, 
		@d INT, 
		@Easter DATE 
 
	SET @c = (@Year / 100) 
	SET @n = @Year - 19 * (@Year / 19) 
	SET @k = (@c - 17) / 25 
	SET @i = @c - @c / 4 - ( @c - @k) / 3 + 19 * @n + 15 
	SET @i = @i - 30 * ( @i / 30 ) 
	SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11)) 
	SET @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4 
	SET @j = @j - 7 * (@j / 7) 
	SET @l = @i - @j 
	SET @m = 3 + (@l + 40) / 44 
	SET @d = @l + 28 - 31 * ( @m / 4 ) 
 
	SET @Easter = CAST((SELECT CONVERT(CHAR(4),@Year) + '-' + RIGHT('0' + CONVERT(VARCHAR(2),@m),2) + '-' + RIGHT('0' + CONVERT(VARCHAR(2),@d),2) + 'T00:00:00') AS DATE)
 
	RETURN @Easter 
END

IF OBJECT_ID('fIsWorkday',N'FN') IS NOT NULL DROP FUNCTION fIsWorkDay
 
GO
 
CREATE FUNCTION [dbo].[fIsWorkDay] (@Date DATE) RETURNS BIT AS
BEGIN
	DECLARE @IsWorkDay BIT = 1
 
	SELECT @IsWorkDay = 
	CASE
		--Exclude Saturday and Sunday (Datefirst-independent)
		WHEN ((@@DATEFIRST + DATEPART(dw,@Date)) % 7) IN (0,1) THEN 0 
		--Exclude National Day
		WHEN MONTH(@Date)=5 AND DAY(@Date)=17 THEN 0
		--Exclude Workers' Day
		WHEN MONTH(@Date)=5 AND DAY(@Date)=1 THEN 0
		--Exclude Christmas
		WHEN MONTH(@Date)=12 AND DAY(@Date) IN (25,26) THEN 0
		--Exclude New Years
		WHEN MONTH(@Date)=1 AND DAY(@Date)=1 THEN 0
		--Exclude Easter, Pentecost and Ascension of Jesus
		WHEN @Date IN (
			DATEADD(DAY,-3,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,-2,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,1,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,39,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,50,dbo.fEasterByYear(YEAR(@Date)))
			) THEN 0
		ELSE 1
	END
 
	RETURN @IsWorkDay
END

IF OBJECT_ID('DimCalendar',N'U') IS NOT NULL DROP TABLE DimCalendar
 
CREATE TABLE [DimCalendar] (
	[DateKey] [INT] NOT NULL PRIMARY KEY,
	[Date] [DATE] NOT NULL,
	[Day] [TINYINT] NOT NULL,
	[Week] [TINYINT] NOT NULL,
	[Month] [TINYINT] NOT NULL,
	[Year] [SMALLINT] NOT NULL,
	[WeekYear] [SMALLINT] NOT NULL,
	[WeekdayName] [VARCHAR](10) NOT NULL,
	[WeekdayNameShort] [VARCHAR](3) NOT NULL,
	[MonthName] [VARCHAR](20) NOT NULL,
	[MonthNameShort] [VARCHAR](3) NOT NULL,
	[Quarter] [TINYINT] NOT NULL,
	[Workday] [BIT] NOT NULL,
	[WorkHours] [DECIMAL](6,2) NOT NULL
)

IF OBJECT_ID('sPopulateDimCalendar',N'P') IS NOT NULL DROP PROCEDURE sPopulateDimCalendar
 
GO
 
CREATE PROCEDURE sPopulateDimCalendar
 
@FromDate DATE = '1990-01-01',
@ToDate DATE = '2020-12-31'
 
AS
 
BEGIN
 
--Setting language
SET LANGUAGE English
 
--Populating Dimension
WHILE @FromDate <= @ToDate
	BEGIN
		INSERT INTO DimCalendar(
			[DateKey], 
			[Date], 
			[Day], 
			[Week], 
			[Month], 
			[Year], 
			[WeekYear], 
			[WeekdayName], 
			[WeekdayNameShort], 
			[MonthName], 
			[MonthNameShort], 
			[Quarter],
			[WorkDay],
			[WorkHours]
            )
            SELECT 
                YEAR(@FromDate)*10000 + MONTH(@FromDate) * 100 + DAY(@FromDate) AS DateKey, 
		@FromDate AS [Date],
                DAY(@FromDate) AS [Day],
		DATEPART(ISO_WEEK, @FromDate) AS [Week],
                MONTH(@FromDate) AS [Month],
                YEAR(@FromDate) AS [Year],
		CASE 
			WHEN DATEPART(ISO_WEEK, @FromDate) = 53 AND MONTH(@FromDate) = 1 THEN YEAR(@FromDate) - 1 
			WHEN DATEPART(ISO_WEEK, @FromDate) = 1 AND MONTH(@FromDate) = 12 THEN YEAR(@FromDate) + 1
			ELSE YEAR(@FromDate) 
		END AS [WeekYear],
                DATENAME(WEEKDAY,@FromDate) AS WeekDayName,
                LEFT(DATENAME(WEEKDAY,@FromDate), 3) AS WeekDayNameShort,
                DATENAME(MONTH, @FromDate) AS MonthName,
                LEFT(DATENAME(MONTH, @FromDate), 3) AS MonthNameShort,
                DATENAME(QUARTER,@FromDate) AS [Quarter],
                dbo.fIsWorkDay(@FromDate) AS WorkDay,
                CASE WHEN dbo.fIsWorkDay(@FromDate) = 1 THEN 8 ELSE 0 END AS WorkHours
 
      SET @FromDate = DATEADD(DAY, 1, @FromDate)
END
 
END

SELECT RTRIM(so.name) + '.' + RTRIM(sc.name) AS DBObjectName 
FROM SYSOBJECTS so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.type = 'U' AND (sc.name+' '+so.name) LIKE '%