From: http://sqlfiddle.com/#!6/425db/1
CREATE TABLE LogData (
Id INT IDENTITY NOT NULL
,Dt DATETIME NOT NULL
,Data VARCHAR(max) NOT NULL
);
CREATE TABLE Ranges (
Id INT IDENTITY NOT NULL
,Name VARCHAR(50) NOT NULL
,DtStart DATETIME NOT NULL
,DtEnd DATETIME NOT NULL
);
CREATE TABLE Groups (
Id INT IDENTITY NOT NULL
,Name VARCHAR(50) NOT NULL
);
CREATE TABLE RangeGroups (
RangeId INT NOT NULL
,GroupId INT NOT NULL
);
INSERT INTO LogData (Dt, Data)
VALUES ('2011-11-23 11:00', 'before thanksgiving day')
,('2011-11-24 11:00', 'on thanksgiving day')
,('2011-12-24 11:00', 'on christmas eve')
,('2011-12-25 11:00', 'on christmas day')
,('2012-11-21 11:00', 'before thanksgiving day')
,('2012-11-22 11:00', 'on thanksgiving day')
,('2012-12-24 11:00', 'on christmas eve')
,('2012-12-25 11:00', 'on christmas day');
INSERT INTO Ranges (Name, DtStart, DtEnd)
VALUES ('THANKSGIVING2011', '2011-11-24', '2011-11-25')
,('CHRISTMASEVE2011', '2011-12-24', '2011-12-25')
,('CHRISTMASDAY2011', '2011-12-25', '2011-12-26')
,('BOXINGDAY2011', '2011-12-26', '2011-12-27')
,('NEWYEARSEVE2011', '2011-12-31', '2012-01-01')
,('NEWYEARSDAY2012', '2012-01-01', '2012-01-02')
,('THANKSGIVING2012', '2012-11-22', '2012-11-23')
,('CHRISTMASEVE2012', '2012-12-24', '2012-12-25')
,('CHRISTMASDAY2012', '2012-12-25', '2012-12-26')
,('BOXINGDAY2012', '2012-12-26', '2012-12-27')
,('NEWYEARSEVE2012', '2012-12-31', '2013-01-01')
,('NEWYEARSDAY2013', '2013-01-01', '2013-01-02');
INSERT INTO Groups (Name)
VALUES ('HOLIDAYS2011')
,('HOLIDAYS2012')
,('ANYCHRISTMAS');
INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2011')
FROM Ranges WHERE Name LIKE '%2011';
INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2012')
FROM Ranges WHERE Name LIKE '%2012';
INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'ANYCHRISTMAS')
FROM Ranges WHERE Name LIKE 'CHRISTMAS%';
SELECT *
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = 'HOLIDAYS2011';
SELECT *
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = 'HOLIDAYS2012';
SELECT *
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = 'ANYCHRISTMAS';