Exclude weekends and stat holidays from query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Exclude weekends and stat holidays from query

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2012-08-22 : 20:56:30

Hello,

I have a DB with the following tables and example data:

DECLARE @Company TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](50) NOT NULL);

DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[CompanyId] [int] NOT NULL,
[Name] [varchar](25) NOT NULL);

DECLARE @AbsenceType TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[CompanyId] [int] NOT NULL,
[AbsenceTypeDesc] [varchar](25) NOT NULL);

DECLARE @AbsenceLedger TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[AbsenceType] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL);

DECLARE @StatHolidays TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[CompanyId] [int] NOT NULL,
[StatDate] [date] NOT NULL,
[StatDesc] [varchar](50) NOT NULL)

INSERT INTO @Company (CompanyName) VALUES ('CompanyNameA');
INSERT INTO @Company (CompanyName) VALUES ('CompanyNameB');

INSERT INTO @EMPLOYEE (CompanyId, Name) VALUES (1, 'Steve');
INSERT INTO @EMPLOYEE (CompanyId, Name) VALUES (1, 'Bill');
INSERT INTO @EMPLOYEE (CompanyId, Name) VALUES (2, 'John');

INSERT INTO @AbsenceType (CompanyId, AbsenceTypeDesc) VALUES (1, 'Vacation');
INSERT INTO @AbsenceType (CompanyId, AbsenceTypeDesc) VALUES (1, 'FlexTime');
INSERT INTO @AbsenceType (CompanyId, AbsenceTypeDesc) VALUES (2, 'Vacation');

INSERT INTO @StatHolidays (CompanyId, StatDate, StatDesc) VALUES (1, '08-22-2012', 'Fake StatDay For Testing')

INSERT INTO @AbsenceLedger (EmployeeID, AbsenceType, StartDate, EndDate) VALUES (1, 1, '08-20-2012', '08-28-2012')
INSERT INTO @AbsenceLedger (EmployeeID, AbsenceType, StartDate, EndDate) VALUES (1, 2, '09-01-2012', '09-01-2012')
INSERT INTO @AbsenceLedger (EmployeeID, AbsenceType, StartDate, EndDate) VALUES (2, 1, '08-17-2012', '08-20-2012')

I need a query that will return a daily breakdown of an employees absence history.

Using this example data, the query needs to return the data like so:


Name      Date          AbsType
=====================================
Steve     08-20-2012    Vacation
Steve     08-21-2012    Vacation
Steve     08-23-2012    Vacation
Steve     08-24-2012    Vacation
Steve     08-27-2012    Vacation
Steve     08-28-2012    Vacation
Steve     09-01-2012    FlexTime
Bill      08-17-2012    Vacation
Bill      08-20-2012    Vacation

If an entry in the AbsenceLedger overlaps with an entry in the StatHolidays table or a weekend (Sat and Sun) it is not included in the results.

Thanks in advance, any help would be much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 22:24:52

[code]
;With AbsenceMatrix
AS
(
SELECT EmployeeID,AbsenceType,StartDate AS DateVal,EndDate
FROM @AbsenceLedger
UNION ALL
SELECT EmployeeID,AbsenceType,DATEADD(dd,1,DateVal),EndDate
FROM AbsenceMatrix
WHERE DATEADD(dd,1,DateVal) <=EndDate
)

SELECT e.Name,am.DateVal,at.AbsenceTypeDesc
FROM @EMPLOYEE e
INNER JOIN AbsenceMatrix am
ON am.EmployeeID = e.EmployeeID
INNER JOIN @AbsenceType at
ON at.AbsenceTypeID = am.AbsenceType
WHERE am.DateVal NOT IN (SELECT StatDate FROM @StatHolidays)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rypi
Yak Posting Veteran

55 Posts

Posted - 2012-08-23 : 00:42:57

Thanks visakh16

Any idea on how to filter out the weekends (sat and sun) too?

Thanks again, appreciate the help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 10:11:58

[code]
;With AbsenceMatrix
AS
(
SELECT EmployeeID,AbsenceType,StartDate AS DateVal,EndDate
FROM @AbsenceLedger
UNION ALL
SELECT EmployeeID,AbsenceType,DATEADD(dd,1,DateVal),EndDate
FROM AbsenceMatrix
WHERE DATEADD(dd,1,DateVal) <=EndDate
)

SELECT e.Name,am.DateVal,at.AbsenceTypeDesc
FROM @EMPLOYEE e
INNER JOIN AbsenceMatrix am
ON am.EmployeeID = e.EmployeeID
INNER JOIN @AbsenceType at
ON at.AbsenceTypeID = am.AbsenceType
WHERE am.DateVal NOT IN (SELECT StatDate FROM @StatHolidays)
AND DATEDIFF(dd,0,am.DateVal) % 7 < 5
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources