--Source Code for Functions

-- dbo.GetCalendar was created and developed by Thomas Allan 

CREATE function [dbo].[GetCalendar](@StartDate date, @EndDate date) 
	returns @Calendar table (
		[DateID] int,
		[Date] date, 
		[Year] int, 
		YearMonth int, 
		Qtr nchar(2), 
		[Month] int, 
		[Week] int,
		[DayOfYear] int, 
		[DayOfMonth] int, 
		[DayOfWeek] int, 
		MonthName nvarchar(10), 
		ShortMonthName nchar(3), 
		DayName nvarchar(10), 
		ShortDayName nchar(3),
		WeekStarting date,
		WeekEnding date)
	as
begin
	insert into @Calendar (
		[DateID],		
		[Date], 
		[Year], 
		YearMonth, 
		Qtr, 
		[Month], 
		[Week],
		[DayOfYear], 
		[DayOfMonth], 
		[DayOfWeek], 
		MonthName, 
		ShortMonthName, 
		DayName, 
		ShortDayName,
		WeekStarting,
		WeekEnding)
	select 
		year(d) * 10000 + month(d) * 100 + day(d) as DateID,
		d as [Date],
		year(d) as [Year],
		year(d) * 100 + month(d) as YearMonth,
		N'Q' + datename(qq, d) as Qtr,
		month(d) as [Month],
		datepart(wk, d) as [Week],
		datepart(dy, d) as [DayOfYear],
		day(d) as [DayOfMonth],
		datepart(dw, d) as [DayOfWeek],
		datename(mm, d) as MonthName,
		cast(datename(mm, d) as nchar(3)) as ShortMonthName,
		datename(weekday, d) as DayName,
		left(datename(weekday, d), 3) as ShortDayName,
		dateadd(dd, -1 * (datepart(dw, d) - 1), d) as WeekStarting, 
		dateadd(dd, 7 - datepart(dw, d), d) as WeekEnding
	from dbo.GetDates(@StartDate, @EndDate)
	order by d
	return
end

GO

--The author of the following function, GetDates, is Itzak ben-Gan and the code can be
--found in his book Microsoft SQL Server 2012 High-Performance T-SQL Using Window 
--Functions (Microsoft Press, 2012) on page 137.

create function [dbo].[GetDates](@StartDate date, @EndDate date) returns @GetDates table (d date) as
	begin
		declare @Days int = datediff(dd, @StartDate, @EndDate) + 1;
	    	insert into @GetDates 
		select dateadd(dd, n, @StartDate) as Date from dbo.GetNums(0, @Days - 1)
		return
	end

GO

--The author of the following function, GetNums, is Itzak ben-Gan and the code can 
--be found in his book Microsoft SQL Server 2012 High-Performance T-SQL Using Window
--Functions (Microsoft Press, 2012) on pages 103, 135 and 146.

create function [dbo].[GetNums](@Low as bigint, @High as bigint) returns table as
	return with 
		L0 as (select c from (values(1), (1)) as D(c)),
		L1 as (select 1 as c from L0 as A cross join L0 as B),
		L2 as (select 1 as c from L1 as A cross join L1 as B),
		L3 as (select 1 as c from L2 as A cross join L2 as B),
		L4 as (select 1 as c from L3 as A cross join L3 as B),
		L5 as (select 1 as C from L4 as A cross join L4 as B),
		Nums as (select row_number() over (order by (select null)) as RowNum
			from L5)
		select top(@High - @Low + 1) @Low + RowNum - 1 as n
		from Nums 
		order by RowNum

GO