tsql_exec/LoadCalendar.sql at main · ahkarm/tsql_exec · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
CREATE TABLE D_CALENDAR (
	ID			  	  NUMERIC(19,0) IDENTITY(1,1) NOT NULL,
    SQNDATE           DATE,
    SQNDAY            INT,
    SQNDAYSUFFIX      CHAR(2),
    SQNDAYNAME        VARCHAR(20),
    SQNDAYOFWEEK      INT,
    SQNDAYOFWEEKINMONTH TINYINT,
    SQNDAYOFYEAR      INT,
    ISWEEKEND         BIT,
    SQNWEEK           INT,
    SQNISOWEEK        INT,
    SQNFIRSTOFWEEK    DATE,
    SQNLASTOFWEEK     DATE,
    SQNWEEKOFMONTH    TINYINT,
    SQNMONTH          INT,
    SQNMONTHNAME      VARCHAR(20),
    SQNFIRSTOFMONTH   DATE,
    SQNLASTOFMONTH    DATE,
    SQNFIRSTOFNEXTMONTH DATE,
    SQNLASTOFNEXTMONTH DATE,
    SQNQUARTER        INT,
    SQNFIRSTOFQUARTER DATE,
    SQNLASTOFQUARTER  DATE,
    SQNYEAR           INT,
    SQNISOYEAR        INT,
    SQNFIRSTOFYEAR    DATE,
    SQNLASTOFYEAR     DATE,
    ISLEAPYEAR        BIT,
    HAS53WEEKS        BIT,
    HAS53ISOWEEKS     BIT,
    MMYYYY            CHAR(6),
    STYLE101          VARCHAR(10),
    STYLE103          VARCHAR(10),
    STYLE112          CHAR(8),
    STYLE120          VARCHAR(10)
);

-- POPULATE THE TABLE USING YOUR QUERY
DECLARE @STARTDATE DATE = '20250101';
DECLARE @CUTOFFDATE DATE = DATEADD(DAY, -1, DATEADD(YEAR, 30, @STARTDATE));

;WITH SEQ(N) AS
(
  SELECT 0
  UNION ALL
  SELECT N + 1
  FROM SEQ
  WHERE N < DATEDIFF(DAY, @STARTDATE, @CUTOFFDATE)
),
D(D) AS
(
  SELECT DATEADD(DAY, N, @STARTDATE)
  FROM SEQ
),
SRC AS
(
  SELECT
    THEDATE = CONVERT(DATE, D),
    THEDAY = DATEPART(DAY, D),
    THEDAYNAME = DATENAME(WEEKDAY, D),
    THEWEEK = DATEPART(WEEK, D),
    THEISOWEEK = DATEPART(ISO_WEEK, D),
    THEDAYOFWEEK = DATEPART(WEEKDAY, D),
    THEMONTH = DATEPART(MONTH, D),
    THEMONTHNAME = DATENAME(MONTH, D),
    THEQUARTER = DATEPART(QUARTER, D),
    THEYEAR = DATEPART(YEAR, D),
    THEFIRSTOFMONTH = DATEFROMPARTS(YEAR(D), MONTH(D), 1),
    THELASTOFYEAR = DATEFROMPARTS(YEAR(D), 12, 31),
    THEDAYOFYEAR = DATEPART(DAYOFYEAR, D)
  FROM D
),
DIM AS
(
  SELECT
    THEDATE,
    THEDAY,
    THEDAYSUFFIX = CONVERT(CHAR(2), CASE WHEN THEDAY / 10 = 1 THEN 'TH' ELSE
                            CASE RIGHT(THEDAY, 1) WHEN '1' THEN 'ST' WHEN '2' THEN 'ND'
                            WHEN '3' THEN 'RD' ELSE 'TH' END END),
    THEDAYNAME,
    THEDAYOFWEEK,
    THEDAYOFWEEKINMONTH = CONVERT(TINYINT, ROW_NUMBER() OVER
                            (PARTITION BY THEFIRSTOFMONTH, THEDAYOFWEEK ORDER BY THEDATE)),
    THEDAYOFYEAR,
    ISWEEKEND = CASE WHEN THEDAYOFWEEK IN (CASE @@DATEFIRST WHEN 1 THEN 6 WHEN 7 THEN 1 END,7)
                            THEN 1 ELSE 0 END,
    THEWEEK,
    THEISOWEEK,
    THEFIRSTOFWEEK = DATEADD(DAY, 1 - THEDAYOFWEEK, THEDATE),
    THELASTOFWEEK = DATEADD(DAY, 6, DATEADD(DAY, 1 - THEDAYOFWEEK, THEDATE)),
    THEWEEKOFMONTH = CONVERT(TINYINT, DENSE_RANK() OVER
                            (PARTITION BY THEYEAR, THEMONTH ORDER BY THEWEEK)),
    THEMONTH,
    THEMONTHNAME,
    THEFIRSTOFMONTH,
    THELASTOFMONTH = MAX(THEDATE) OVER (PARTITION BY THEYEAR, THEMONTH),
    THEFIRSTOFNEXTMONTH = DATEADD(MONTH, 1, THEFIRSTOFMONTH),
    THELASTOFNEXTMONTH = DATEADD(DAY, -1, DATEADD(MONTH, 2, THEFIRSTOFMONTH)),
    THEQUARTER,
    THEFIRSTOFQUARTER = MIN(THEDATE) OVER (PARTITION BY THEYEAR, THEQUARTER),
    THELASTOFQUARTER = MAX(THEDATE) OVER (PARTITION BY THEYEAR, THEQUARTER),
    THEYEAR,
    THEISOYEAR = THEYEAR - CASE WHEN THEMONTH = 1 AND THEISOWEEK > 51 THEN 1
                            WHEN THEMONTH = 12 AND THEISOWEEK = 1  THEN -1 ELSE 0 END,
    THEFIRSTOFYEAR = DATEFROMPARTS(THEYEAR, 1,  1),
    THELASTOFYEAR,
    ISLEAPYEAR = CONVERT(BIT, CASE WHEN (THEYEAR % 400 = 0)
                            OR (THEYEAR % 4 = 0 AND THEYEAR % 100 <> 0)
                            THEN 1 ELSE 0 END),
    HAS53WEEKS = CASE WHEN DATEPART(WEEK, THELASTOFYEAR) = 53 THEN 1 ELSE 0 END,
    HAS53ISOWEEKS = CASE WHEN DATEPART(ISO_WEEK, THELASTOFYEAR) = 53 THEN 1 ELSE 0 END,
    MMYYYY = CONVERT(CHAR(2), CONVERT(CHAR(8), THEDATE, 101)) + CONVERT(CHAR(4), THEYEAR),
    STYLE101 = CONVERT(CHAR(10), THEDATE, 101),
    STYLE103 = CONVERT(CHAR(10), THEDATE, 103),
    STYLE112 = CONVERT(CHAR(8), THEDATE, 112),
    STYLE120 = CONVERT(CHAR(10), THEDATE, 120)
  FROM SRC
)
INSERT INTO D_CALENDAR
SELECT *
FROM DIM
ORDER BY THEDATE
OPTION (MAXRECURSION 0);