Calendar and Number tables for Microsoft SQL Server

0 - Tally (numbers) table and Calendar table T-SQL

Refer to other files.

gistfile1.txt

----------------------------------------------------------------------------------------------------
-- UTILITY TABLE INSERTIONS
----------------------------------------------------------------------------------------------------

--------------------------------------------------
-- Delete and re-insert Number table records.
--------------------------------------------------
DELETE util.Number

;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv4)

INSERT INTO util.Number (Number)
SELECT n - 1 as Number
FROM Tally


--------------------------------------------------
-- Delete and re-insert Calendar table records.
--------------------------------------------------

-- NOTE: The @@DATEFIRST value must be set to 1 for
-- the following Calendar insertions to be correct.
declare @OriginalDateFirst tinyint = (select @@DATEFIRST)
set datefirst 1 -- Sets the first day of the week to Monday.

delete util.Calendar

insert into util.Calendar
select
	Dt,
	PrecedingMonday = DATEADD(day, 1 - (DATEPART(dw, Dt)), Dt),
	FollowingSunday = DATEADD(day, 7 - (DATEPART(dw, Dt)), Dt),
	FirstDayOfMonth = convert(DATE, DATEADD(mm, DATEDIFF(mm,0,Dt), 0)),
	LastDayOfMonth = convert(date, DATEADD(mm, DATEDIFF(mm, 0, Dt) + 1, -1)),
	FirstDayOfQuarter = convert(date, DATEADD(qq,DATEDIFF(qq,0,Dt),0)),
	LastDayOfQuarter = convert(date, DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1)),
	FirstDayOfYear = convert(date, DATEADD(yy, DATEDIFF(yy,0,Dt), 0)),
	LastDayOfYear = convert(date, DATEADD(yy, DATEDIFF(yy, 0, Dt) + 1, -1)),
	CalendarYear = year(Dt),
	FinancialYear = year(dateadd(month, 6, Dt)),
	IsBusinessDay = (
		case when (select datename(dw, Dt) where datename(dw, Dt) in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')) is not null then 1
		else 0
	end),
	IsPublicHoliday = 0,
	DayFullName = datename(dw, Dt),
	DayShortName = substring(datename(dw, Dt), 1, 3)
from (
	select Number = -n.Number, convert(date, dateadd(d, -Number, getdate())) as Dt
	from util.Number n
	union
	select Number = n.Number, convert(date, dateadd(d, Number, getdate())) as Dt
	from util.Number n
) sub
where year(Dt) >= 2016 and year(Dt) <= 2040

-- Reset the @@DATEFIRST value to whatever it was prior.
set datefirst @OriginalDateFirst

----------------------------------------------------------------------------------------------------
-- TODO: Run UPDATE queries here to set IsPublicHoliday to TRUE for any public holidays observed.
----------------------------------------------------------------------------------------------------


----------------------------------------------------------------------------------------------------
-- END OF UTILITY TABLE INSERTIONS
----------------------------------------------------------------------------------------------------
go

select * from util.Number
select * from util.Calendar

gistfile2.txt

/****** Object:  Table [util].[Calendar]    Script Date: 07/11/2016 11:46:36 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [util].[Calendar](
	[Date] [date] NOT NULL,
	[PrecedingMonday] [date] NOT NULL,
	[FollowingSunday] [date] NOT NULL,
	[FirstDayOfMonth] [date] NOT NULL,
	[LastDayOfMonth] [date] NOT NULL,
	[FirstDayOfQuarter] [date] NOT NULL,
	[LastDayOfQuarter] [date] NOT NULL,
	[FirstDayOfYear] [date] NOT NULL,
	[LastDayOfYear] [date] NOT NULL,
	[CalendarYear] [int] NOT NULL,
	[FinancialYear] [int] NOT NULL,
	[IsBusinessDay] [bit] NOT NULL,
	[IsPublicHoliday] [bit] NOT NULL,
	[DayFullName] [nchar](10) NOT NULL,
	[DayShortName] [nchar](3) NOT NULL,
 CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED 
(
	[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

gistfile3.txt

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [util].[Number](
	[Number] [int] NOT NULL,
 CONSTRAINT [PK_Number] PRIMARY KEY CLUSTERED 
(
	[Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO