WordPress.com



-- Create GenerateCalendar, Calendar and Holiday Tables and Populate.sql

CREATE FUNCTION [dbo].[GenerateCalendar]

(

@FromDate DATETIME

,@NoDays INT

)

-- Generates a calendar table with sequential day numbering (@FromDate = SeqNo 1).

-- See RETURNS table (comments) for meaning of each column.

-- Notes: 1) Max for NoDays is 65536, which runs in just over 2 seconds.

--

-- Example calls to generate the calendar:

-- 1) Forward for 365 days starting today:

-- DECLARE @Date DATETIME

-- SELECT @Date = GETDATE()

-- SELECT *

-- FROM dbo.GenerateCalendar(@Date, 365)

-- ORDER BY SeqNo;

-- 2) Backwards for 365 days back starting today:

-- DECLARE @Date DATETIME

-- SELECT @Date = GETDATE()

-- SELECT *

-- FROM dbo.GenerateCalendar(@Date, -365)

-- ORDER BY SeqNo;

-- 3) For only the FromDate:

-- DECLARE @Date DATETIME

-- SELECT @Date = GETDATE()

-- SELECT *

-- FROM dbo.GenerateCalendar(@Date, 1);

-- 4) Including only the last week days of each month:

-- Note: Seq no in this case are as if all dates were generated

-- DECLARE @Date DATETIME

-- SELECT @Date = GETDATE()

-- SELECT *

-- FROM dbo.GenerateCalendar(@Date, 365)

-- WHERE Last = 1 ORDER BY SeqNo;

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

--===== High speed code provided courtesy of SQL MVP Jeff Moden (idea by Dwain Camps)

--===== Generate sequence numbers from 1 to 65536 (credit to SQL MVP Itzik Ben-Gen)

WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows

E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows

E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows

cteTally(N) AS (

SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)

-- [SeqNo]=Sequential day number (@FromDate always=1) forward or backwards

SELECT [SeqNo] = t.N,

-- [Date]=Date (with 00:00:00.000 for the time component)

[Date] = dt.DT,

-- [Year]=Four digit year

[Year] = dp.YY,

-- [YrNN]=Two digit year

[YrNN] = dp.YY % 100,

-- [YYYYMM]=Integer YYYYMM (year * 100 + month)

[YYYYMM] = dp.YY * 100 + dp.MM,

-- [BuddhaYr]=Year in Buddhist calendar

[BuddhaYr] = dp.YY + 543,

-- [Month]=Month (as an INT)

[Month] = dp.MM,

-- [Day]=Day (as an INT)

[Day] = dp.DD,

-- [WkDNo]=Week day number (based on @@DATEFIRST)

[WkDNo] = DATEPART(dw,dt.DT),

-- Next 3 columns dependent on language setting so may not work for non-English

-- [WkDName]=Full name of the week day, e.g., Monday, Tuesday, etc.

[WkDName] = CONVERT(NCHAR(9),dp.DW),

-- [WkDName2]=Two characters for the week day, e.g., Mo, Tu, etc.

[WkDName2] = CONVERT(NCHAR(2),dp.DW),

-- [WkDName3]=Three characters for the week day, e.g., Mon, Tue, etc.

[WkDName3] = CONVERT(NCHAR(3),dp.DW),

-- [JulDay]=Julian day (day number of the year)

[JulDay] = dp.DY,

-- [JulWk]=Week number of the year

[JulWk] = dp.DY/7+1,

-- [WkNo]=Week number

[WkNo] = dp.DD/7+1,

-- [Qtr]=Quarter number (of the year)

[Qtr] = DATEPART(qq,dt.Dt),

-- [Last]=Number the weeks for the month in reverse

[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,

-- [LdOfMo]=Last day of the month

[LdOfMo] = DATEPART(dd,dp.LDtOfMo),

-- [LDtOfMo]=Last day of the month as a DATETIME

[LDtOfMo] = dp.LDtOfMo

FROM cteTally t

CROSS APPLY

( --=== Create the date

SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)

) dt

CROSS APPLY

( --=== Create the other parts from the date above using a "cCA"

-- (Cascading CROSS APPLY (cCA), courtesy of Chris Morris)

SELECT YY = DATEPART(yy,dt.DT),

MM = DATEPART(mm,dt.DT),

DD = DATEPART(dd,dt.DT),

DW = DATENAME(dw,dt.DT),

Dy = DATEPART(dy,dt.DT),

LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)

) dp;

GO

-- Create a Calendar table

SELECT [Date]=ISNULL([Date], 0), [Year]=ISNULL([Year], 0), [YrNN]=ISNULL([YrNN], 0)

,[YYYYMM]=ISNULL([YYYYMM], 0), [BuddhaYr], [Month], [Day]=ISNULL([Day], 0)

,[WkDNo], [WkDName], [WkDName2], [WkDName3], [JulDay], [JulWk]

,[WkNo], [Qtr], [Last], [LdOfMo], [LDtOfMo]

INTO dbo.Calendar

-- This function can be obtained here:

FROM dbo.GenerateCalendar('1990-01-01', 65536);

-- Change column types to be NOT NULL so we can index them

--

-- Note: not needed thanks to the neat trick with ISNULL above

--ALTER TABLE dbo.Calendar ALTER COLUMN [Date] DATETIME NOT NULL;

--ALTER TABLE dbo.Calendar ALTER COLUMN [Year] INT NOT NULL;

--ALTER TABLE dbo.Calendar ALTER COLUMN [Month] INT NOT NULL;

--ALTER TABLE dbo.Calendar ALTER COLUMN [YYYYMM] INT NOT NULL;

--ALTER TABLE dbo.Calendar ALTER COLUMN [Day] INT NOT NULL;

GO

-- Build some representative INDEXes

ALTER TABLE dbo.Calendar

ADD CONSTRAINT Cal_pk PRIMARY KEY([Date]);

ALTER TABLE dbo.Calendar

ADD CONSTRAINT Cal_ix1 UNIQUE NONCLUSTERED([Year], [Month], [Day]);

ALTER TABLE dbo.Calendar

ADD CONSTRAINT Cal_ix2 UNIQUE NONCLUSTERED([YYYYMM], [Day]);

SELECT TOP 10 [Date], [YYYYMM], [WkDName2], [WkNo], [Last]

FROM dbo.Calendar;

-- Now create a Holidays table, from the same blog

CREATE TABLE dbo.Holidays

(

FromDate DATETIME PRIMARY KEY

,ToDate DATETIME

,Holiday VARCHAR(100)

);

WITH EasterDates (d) AS

(

SELECT '1990-04-15' UNION ALL SELECT '1991-03-31' UNION ALL SELECT '1992-04-19'

UNION ALL SELECT '1993-04-11' UNION ALL SELECT '1994-04-03' UNION ALL SELECT '1995-04-16'

UNION ALL SELECT '1996-04-07' UNION ALL SELECT '1997-03-30' UNION ALL SELECT '1998-04-12'

UNION ALL SELECT '1999-04-04' UNION ALL SELECT '2000-04-23' UNION ALL SELECT '2001-04-15'

UNION ALL SELECT '2002-03-31' UNION ALL SELECT '2003-04-20' UNION ALL SELECT '2004-04-11'

UNION ALL SELECT '2005-03-27' UNION ALL SELECT '2006-04-16' UNION ALL SELECT '2007-04-08'

UNION ALL SELECT '2008-03-23' UNION ALL SELECT '2009-04-12' UNION ALL SELECT '2010-04-04'

UNION ALL SELECT '2011-04-24' UNION ALL SELECT '2012-04-08' UNION ALL SELECT '2013-03-31'

UNION ALL SELECT '2014-04-20' UNION ALL SELECT '2015-04-05' UNION ALL SELECT '2016-03-27'

UNION ALL SELECT '2017-04-16' UNION ALL SELECT '2018-04-01' UNION ALL SELECT '2019-04-21'

UNION ALL SELECT '2020-04-12'

)

INSERT INTO dbo.Holidays (FromDate, ToDate, Holiday)

SELECT FromDate=d, ToDate=d, Holiday='Easter Sunday ' + CAST(YEAR(d) AS CHAR(4))

FROM EasterDates;

INSERT INTO dbo.Holidays (FromDate, ToDate, Holiday)

SELECT FromDate = a.FromDate+b.[days]

,ToDate = a.FromDate+b.[days]

,b.holiday

FROM dbo.Holidays a

CROSS APPLY

(

VALUES(-2, 'Good Friday ' + CAST(YEAR(FromDate) AS CHAR(4)))

,(-46, 'Ash Wednesday ' + CAST(YEAR(FromDate) AS CHAR(4)))

,(-47, 'Mardi Gras (Fat Tuesday) ' + CAST(YEAR(FromDate) AS CHAR(4)))

) b ([days], holiday)

WHERE a.Holiday LIKE 'Easter Sunday%';

SELECT TOP 10 *

FROM dbo.Calendar;

SELECT TOP 10 *

FROM dbo.Holidays;

-- Create CalculateEasterSunday and Add Holidays.sql

CREATE FUNCTION CalculateEasterSunday

(

@Year INT

)

-- Calculate Easter Sunday for any given year

RETURNS TABLE WITH SCHEMABINDING

AS RETURN

WITH CalculatePaschalFullMoon AS

(

-- Original query to calculate the components of the

-- Paschal Full Moon date

SELECT a.[Year] --, [Y MOD 19], Addendum

--,PFMD = CASE WHEN PFMD > 31 THEN PFMD-31 ELSE PFMD END

--,[Month] = CASE WHEN PFMD > 31 THEN 'Apr' ELSE 'Mar' END

--,[MonthNo] = CASE WHEN PFMD > 31 THEN 4 ELSE 3 END

-- Put the date/time pieces together to get a real

-- DATETIME for PFMD

,[PFMD2] =

DATEADD(day, CASE WHEN PFMD > 31

THEN PFMD-31

ELSE PFMD

END-1 -- PFMD

,DATEADD(month, CASE

WHEN PFMD > 31

THEN 4

ELSE 3

END-1 -- [MonthNo]

, a.[Date]))

FROM

(

SELECT [Year], [Y MOD 19], Addendum, [Date]

,PFMD = (45-([Y MOD 19]*11)%30+Addendum)

FROM

(

SELECT [Year]

,[Y MOD 19] = [Year]%19

,Addendum = CASE [Year]%19

WHEN 5 THEN 29

WHEN 16 THEN 29

WHEN 8 THEN 30

ELSE 0

END

,[Date]

FROM dbo.Calendar

WHERE [Month] = 1 AND [Day] = 1 AND [Year] = @Year

) a

) a

)

SELECT a.[Year]

,PaschalFullMoon=CAST(a.PFMD2 AS DATE)

,EasterSunday

FROM CalculatePaschalFullMoon a

-- Easter Sunday follows the Paschal Full Moon date

-- so pick that up from the Calendar table

CROSS APPLY

(

SELECT TOP 1 EasterSunday=CAST([Date] AS DATE)

FROM dbo.Calendar b

WHERE b.[Date] > a.PFMD2 AND b.WkDName3 = 'Sun'

ORDER BY [Date]

) b;

GO

INSERT dbo.Holidays (FromDate, ToDate, Holiday)

SELECT FromDate, ToDate, holiday

FROM

(

SELECT FromDate =DATEADD(day, c.days, b.EasterSunday)

,ToDate=DATEADD(day, c.days, b.EasterSunday)

,c.holiday

FROM dbo.Calendar a

CROSS APPLY dbo.CalculateEasterSunday(a.[Year]) b

CROSS APPLY

(

VALUES (0, 'Easter Sunday ' + CAST(a.[Year] AS CHAR(4)))

,(-2, 'Good Friday ' + CAST(a.[Year] AS CHAR(4)))

,(-46, 'Ash Wednesday ' + CAST(a.[Year] AS CHAR(4)))

,(-47, 'Mardi Gras (Fat Tuesday) ' + CAST(a.[Year] AS CHAR(4)))

) c ([days], holiday)

WHERE a.[Month] = 1 AND a.[Day] = 1

) a

WHERE NOT EXISTS

(

SELECT 1

FROM dbo.Holidays x

WHERE x.FromDate = a.FromDate AND x.ToDate = a.ToDate

);

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download