Greek working days

calculateWorkingDays.sql

DECLARE @START_DATE DATE
DECLARE @END_DATE DATE
SET @START_DATE = '2014-01-01'
SET @END_DATE = '2054-01-01'

CREATE TABLE #DATES(
[DATE] DATE,
[MONTH] INT,
WEEK_DAY VARCHAR(20),
BUSINESS_DAY VARCHAR(3),
PRIMARY KEY ([DATE])
)

CREATE TABLE #EOM(
[MONTH] INT,
MAX_WORKING_DATE DATE,
PRIMARY KEY ([MONTH])
)

WHILE @START_DATE <= @END_DATE
	BEGIN

	DECLARE @YEAR INT;
	SET @YEAR = YEAR(@START_DATE)

	DECLARE @TEMP_EASTER DATE;
	SELECT
		@TEMP_EASTER= CASE 
				WHEN ((44-((((@YEAR-2)%19)*11)%30))+13) <= 31
				THEN CAST(@YEAR AS VARCHAR(4))+'-03-'+ CAST(((44-((((@YEAR-2)%19)*11)%30))+13) AS VARCHAR(2))
				ELSE
				CAST(@YEAR AS VARCHAR(4))+'-04-'+ CAST((((44-((((@YEAR-2)%19)*11)%30))+13)-31) AS VARCHAR(2))
			       END

	DECLARE @EASTER_DATE DATE;
	SELECT
		@EASTER_DATE=CASE
				WHEN DATENAME(DW,@TEMP_EASTER)='Sunday'	THEN DATEADD(DAY,7,@TEMP_EASTER)
				WHEN DATENAME(DW,@TEMP_EASTER)='Monday' THEN DATEADD(DAY,6,@TEMP_EASTER)
				WHEN DATENAME(DW,@TEMP_EASTER)='Tuesday' THEN DATEADD(DAY,5,@TEMP_EASTER)
				WHEN DATENAME(DW,@TEMP_EASTER)='Wednesday' THEN DATEADD(DAY,4,@TEMP_EASTER)
				WHEN DATENAME(DW,@TEMP_EASTER)='Thursday' THEN DATEADD(DAY,3,@TEMP_EASTER)
				WHEN DATENAME(DW,@TEMP_EASTER)='Friday'	THEN DATEADD(DAY,2,@TEMP_EASTER)
				WHEN DATENAME(DW,@TEMP_EASTER)='Saturday' THEN DATEADD(DAY,1,@TEMP_EASTER)
				ELSE '0001-01-01'
			      END 

		INSERT INTO #DATES
		SELECT 
			@START_DATE AS [DATE],
			CAST((SUBSTRING(CAST(@START_DATE AS VARCHAR(10)),1,4)+SUBSTRING(CAST(@START_DATE AS VARCHAR(10)),6,2)) AS INT) AS [MONTH],
			DATENAME(DW,@START_DATE) AS WEEK_DAY,
			CASE
			  WHEN RIGHT(CAST(@START_DATE AS VARCHAR(10)),5) IN ('01-01','01-06','03-25','05-01','08-15','10-28','12-25','12-26') 
			  	OR 
			 	@START_DATE IN (@EASTER_DATE,DATEADD(DAY,-48,@EASTER_DATE),DATEADD(DAY,-2,@EASTER_DATE),DATEADD(DAY,-1,@EASTER_DATE),DATEADD(DAY,1,@EASTER_DATE),DATEADD(DAY,50,@EASTER_DATE))
			  THEN 'NO'
			  ELSE CASE 
				 WHEN DATENAME(DW,@START_DATE) IN ('Sunday','Saturday') THEN 'NO'
				 ELSE 'YES'
			       END
			 END AS BUSINESS_DAY

		SET @START_DATE = DATEADD(DAY,1,@START_DATE)

	END

	INSERT INTO #EOM
	SELECT
		[MONTH],
		MAX([DATE]) AS MAX_WORKING_DATE
	FROM 
		#DATES
	WHERE
		BUSINESS_DAY='YES'
	GROUP BY
		[MONTH]

SELECT 
	#DATES.*,
	CASE 
		WHEN #EOM.MAX_WORKING_DATE IS NULL THEN 'NO'
		ELSE 'YES'
	END AS LAST_WORKING_DATE
FROM
	#DATES
	LEFT OUTER JOIN #EOM
	ON #DATES.[MONTH] = #EOM.[MONTH]
		AND
		#DATES.[DATE] = #EOM.MAX_WORKING_DATE 
	
DROP TABLE #DATES 
DROP TABLE #EOM