insert into holi_tab values (convert(datetime,'20140101',112))
...
insert into holi_tab values (convert(datetime,'20141225',112))
as the holidays are different for different country/area
CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
isWeekday BIT,
isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT
)
GO
SET NOCOUNT ON
DECLARE @dt SMALLDATETIME
SET @dt = '20000101'
WHILE @dt < '20300101'
BEGIN
INSERT dbo.Calendar(dt) SELECT @dt
SET @dt = @dt + 1
END
UPDATE dbo.Calendar SET
isWeekday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
isHoliday = 0,
Y = YEAR(dt),
FY = YEAR(dt),
/*
-- if our fiscal year
-- starts on May 1st:
FY = CASE
WHEN MONTH(dt) < 5
THEN YEAR(dt)-1
ELSE YEAR(dt) END,
*/
Q = CASE
WHEN MONTH(dt) <= 3 THEN 1
WHEN MONTH(dt) <= 6 THEN 2
WHEN MONTH(dt) <= 9 THEN 3
ELSE 4 END,
M = MONTH(dt),
D = DAY(dt),
DW = DATEPART(DW, dt),
monthname = DATENAME(MONTH, dt),
dayname = DATENAME(DW, dt),
W = DATEPART(WK, dt)
UPDATE dbo.Calendar
SET IsHoliday = 0;
-- New Year's day
UPDATE Calendar
SET IsHoliday = 1
WHERE M = 1
AND D = 1;
-- New Year's day observed
-- if NYD is a Saturday
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE c1.M = 12
AND c1.D = 31
AND c1.DW = 6
AND EXISTS (SELECT 1
FROM Calendar AS c2
WHERE c2.M = 1
AND c2.D = 1
AND c2.dt > c1.dt
AND DW = 7);
-- if NYD is a Sunday
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE c1.M = 1
AND c1.D = 2
AND c1.DW = 2
AND EXISTS (SELECT 1
FROM Calendar AS c2
WHERE c2.M = 1
AND c2.D = 1
AND c2.dt < c1.dt
AND c2.DW = 1);
-- Martin Luther King Day
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE c1.M = 1
AND c1.DW = 2
AND (SELECT COUNT(*)
FROM Calendar AS c2
WHERE c2.M = 1
AND c2.DW = 2
AND c2.Y = c1.Y
AND c2.dt < c1.dt) = 2;
-- Washington's Birthday
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE c1.M = 2
AND c1.DW = 2
AND (SELECT COUNT(*)
FROM Calendar AS c2
WHERE c2.M = 2
AND c2.DW = 2
AND c2.Y = c1.Y
AND c2.dt < c1.dt) = 2;
-- Memorial Day
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE M = 5
AND DW = 2
AND NOT EXISTS (SELECT 1
FROM Calendar AS c2
WHERE M = 5 AND DW = 2
AND c2.Y = c1.Y
AND c2.dt > c1.dt);
-- Independence Day
UPDATE Calendar
SET IsHoliday = 1
WHERE M = 7
AND D = 4;
-- Independence Day, observed
-- if Independence Day is a Saturday
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE c1.M = 7
AND c1.D = 3
AND c1.DW = 6
AND EXISTS (SELECT 1
FROM Calendar AS c2
WHERE c2.M = 7
AND c2.D = 4
AND c2.DW = 7
AND c2.dt > c1.dt);
-- if Independence Day is a Sunday
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE c1.M = 7
AND c1.D = 5
AND c1.DW = 2
AND EXISTS (SELECT 1
FROM Calendar AS c2
WHERE c2.M = 7
AND c2.D = 4
AND c2.DW = 1
AND c2.dt < c1.dt);
-- Labor Day
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE M = 9
AND DW = 2
AND NOT EXISTS (SELECT 1
FROM Calendar AS c2
WHERE M = 9 AND DW = 2
AND c2.Y = c1.Y
AND c2.dt < c1.dt);
-- Columbus Day
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE c1.M = 10
AND c1.DW = 2
AND (SELECT COUNT(*)
FROM Calendar AS c2
WHERE c2.M = 10
AND c2.DW = 2
AND c2.Y = c1.Y
AND c1.dt > c2.dt) = 1
-- Veterans' Day
UPDATE Calendar
SET IsHoliday = 1
WHERE M = 11
AND D = 11
-- Veterans' Day, observed
-- when Veterans' Day is a Saturday
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE c1.M = 11
AND c1.D = 10
AND c1.DW = 6
AND EXISTS (SELECT 1
FROM Calendar AS c2
WHERE c2.M = 11
AND c2.D = 11
AND c2.DW = 7
AND c2.dt > c1.dt);
-- when Veterans' Day is a Sunday
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE c1.M = 11
AND c1.D = 12
AND c1.DW = 2
AND EXISTS (SELECT 1
FROM Calendar AS c2
WHERE c2.M = 11
AND c2.D = 11
AND c2.DW = 1
AND c2.dt < c1.dt);
-- Thanksgiving Day
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE M = 11
AND DW = 5
AND (SELECT COUNT(*)
FROM Calendar AS c2
WHERE M = 11
AND DW = 5
AND c2.Y = c1.Y
AND c2.dt < c1.dt) = 3;
-- Christmas Day
UPDATE Calendar
SET IsHoliday = 1
WHERE M = 12
AND D = 25;
-- Christmas Day, observed
-- when Christmas Day is a Saturday
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE c1.M = 12
AND c1.D = 24
AND c1.DW = 6
AND EXISTS (SELECT 1
FROM Calendar AS c2
WHERE c2.M = 12
AND c2.D = 25
AND c2.DW = 7
AND c2.dt > c1.dt);
-- when Christmas Day is a Sunday
UPDATE Calendar
SET IsHoliday = 1
FROM Calendar AS c1
WHERE c1.M = 12
AND c1.D = 26
AND c1.DW = 2
AND EXISTS (SELECT 1
FROM Calendar AS c2
WHERE c2.M = 12
AND c2.D = 25
AND c2.DW = 1
AND c2.dt < c1.dt);
Title | # Comments | Views | Activity |
---|---|---|---|
Info Path 2013 with Office 2016 Pro Plus and Standard 2016 | 1 | 31 | |
SQL Performance optimisation - selecting as a column | 29 | 44 | |
Group by and order by clause | 28 | 36 | |
Haw to apply join on 2 tables with this scenario | 4 | 8 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
16 Experts available now in Live!