-- Create a Calendar table
CREATE TABLE dbo.dim_calendar (
calendar_date DATE NOT NULL,
day_of_week TINYINT NOT NULL,
is_business_day TINYINT NOT NULL,
is_holiday TINYINT NULL,
holidy_name nvarchar(MAX)
CONSTRAINT PK_dim_calendar PRIMARY KEY (calendar_date)
);
-- 2015-01-01 부터 30년 동안의 날짜 입력
DECLARE @StartDate DATE = '20150101', @NumberOfYears INT = 30;
DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);
INSERT dim_calendar([calendar_date], [day_of_week], [is_business_day])
SELECT d, (select DATEPART(dw, d)), 1
FROM
(
SELECT d = DATEADD(DAY, rn - 1, @StartDate)
FROM
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]
) AS x
) AS y;
-- Update data
update hh_calendar set day_of_week = (select DATEPART(dw, calendar_date));
update hh_calendar set is_holiday = 0;
* 참고:
1) DATEPART (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver15
DATEPART (Transact-SQL) - SQL Server
Transact-SQL reference for the DATEPART function. This function returns an integer corresponding to the datepart of a specified date.
docs.microsoft.com
2) Query
https://gist.github.com/gavincampbell/4a4d4aa581038c261d89eddddfcaf45c
Creates a calendar table in Azure SQL Data Warehouse
Creates a calendar table in Azure SQL Data Warehouse - CreateDateAzureSQLDW.sql
gist.github.com