Employee Absence Query

Question

Hi,

I have a table called HRMABSENCETRANS which has a field called TRANSDATE which holds a holiday date.For each day off an employee takes a record will be populated in this table.

I currently have the below query which will show a new record for each day an employee takes. I wish to amend it so that the report will instead a time off begin date and end date. If days off occur together they should be output as one block of holidays. Can anybody please show me how I would do this? The query would need to take into accound that leave taken on either side of a weekend should also be included as one block.

DECLARE @EMPLID AS NVARCHAR(10)
DECLARE @ABSENCEDATESTART AS DATETIME
DECLARE @ABSENCEDATEEND AS DATETIME
DECLARE @ABSENCECODE AS NVARCHAR(10)

SET @EMPLID = '00005'
SET @ABSENCEDATESTART = '2010-01-01'
SET @ABSENCEDATEEND = '2012-01-01'
SET @ABSENCECODE = 'Bereave-P'

SELECT DISTINCT	a.EMPLID AS [Employee Number], 
		a.NAME AS [Employee Name], 
		e.HRMABSENCECODEGROUPID AS [Absence Group],
		d.HRMABSENCECODEID AS [Absence Code],
		c.TRANSDATE AS [Absence Date]
FROM	dbo.EMPLTABLE a INNER JOIN
		dbo.HRMABSENCETABLE b ON a.EMPLID = b.EMPLID INNER JOIN
		dbo.HRMABSENCETRANS c ON b.HRMABSENCETABLEID = c.HRMABSENCETABLEID INNER JOIN
		dbo.HRMABSENCECODE d ON c.HRMABSENCECODEID = d.HRMABSENCECODEID INNER JOIN
		dbo.HRMABSENCECODEGROUP e ON d.HRMABSENCECODEGROUPID = e.HRMABSENCECODEGROUPID
WHERE 
a.EMPLID = @EMPLID AND
c.TRANSDATE BETWEEN @ABSENCEDATESTART AND @ABSENCEDATEEND AND
d.HRMABSENCECODEID = @ABSENCECODE AND
a.PROJPERIODID = 'Monthly'

Answer 1

Hi,

I have a table called HRMABSENCETRANS which has a field called TRANSDATE which holds a holiday date.For each day off an employee takes a record will be populated in this table.

I currently have the below query which will show a new record for each day an employee takes. I wish to amend it so that the report will instead a time off begin date and end date. If days off occur together they should be output as one block of holidays. Can anybody please show me how I would do this? The query would need to take into accound that leave taken on either side of a weekend should also be included as one block.

DECLARE @EMPLID AS NVARCHAR(10)
DECLARE @ABSENCEDATESTART AS DATETIME
DECLARE @ABSENCEDATEEND AS DATETIME
DECLARE @ABSENCECODE AS NVARCHAR(10)

SET @EMPLID = '00005'
SET @ABSENCEDATESTART = '2010-01-01'
SET @ABSENCEDATEEND = '2012-01-01'
SET @ABSENCECODE = 'Bereave-P'

SELECT DISTINCT	a.EMPLID AS [Employee Number], 
		a.NAME AS [Employee Name], 
		e.HRMABSENCECODEGROUPID AS [Absence Group],
		d.HRMABSENCECODEID AS [Absence Code],
		c.TRANSDATE AS [Absence Date]
FROM	dbo.EMPLTABLE a INNER JOIN
		dbo.HRMABSENCETABLE b ON a.EMPLID = b.EMPLID INNER JOIN
		dbo.HRMABSENCETRANS c ON b.HRMABSENCETABLEID = c.HRMABSENCETABLEID INNER JOIN
		dbo.HRMABSENCECODE d ON c.HRMABSENCECODEID = d.HRMABSENCECODEID INNER JOIN
		dbo.HRMABSENCECODEGROUP e ON d.HRMABSENCECODEGROUPID = e.HRMABSENCECODEGROUPID
WHERE 
a.EMPLID = @EMPLID AND
c.TRANSDATE BETWEEN @ABSENCEDATESTART AND @ABSENCEDATEEND AND
d.HRMABSENCECODEID = @ABSENCECODE AND
a.PROJPERIODID = 'Monthly'

Answer 2

Hi,

Please post your table DDL with some sample data + the expected output. It may help us to provide much better help.

Thanks,

Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 3

duplicate thread. See http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/94a38c66-193d-4627-9dfb-f5468a154f97/#da0820a4-ede6-4d95-b342-91b617cf4fda

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 4

My table is like the below:

CREATE TABLE [dbo].[HRMABSENCETRANS](
	[HRMABSENCETABLEID] [nvarchar](10) NOT NULL DEFAULT (''),
	[TRANSTEXT] [nvarchar](30) NOT NULL DEFAULT (''),
	[HRMABSENCECODEID] [nvarchar](10) NOT NULL DEFAULT (''),
	[TRANSDATE] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
	[HOURS] [numeric](28, 12) NOT NULL DEFAULT ((0)),
	[OPEN_] [int] NOT NULL DEFAULT ((0)),
	[REFTABLEID] [int] NOT NULL DEFAULT ((0)),
	[REFRECID] [bigint] NOT NULL DEFAULT ((0)),
	[VIPDISPUTED] [int] NOT NULL DEFAULT ((0)),
	[VIPRECREF] [bigint] NOT NULL DEFAULT ((0)),
	[DATAAREAID] [nvarchar](3) NOT NULL DEFAULT ('dat'),
	[RECVERSION] [int] NOT NULL DEFAULT ((1)),
	[RECID] [bigint] NOT NULL

The field I am interested in is HRMABSENCETRANS.TRANSDATE. For each day an employee takes off a record is added to this table One of the things it details is the date of the absence.

My current report looks like this:

Employee Number | Employee Name | Absence Group | Absence Code | Absence Date

00005                     Alan Jones           Bereave             Bereave-P         2011-06-17 00:00:00:000

00005                     Alan Jones           Bereave             Bereave-P         2011-06-20 00:00:00:000

00005                     Alan Jones           Bereave             Bereave-P         2011-06-21 00:00:00:000

00005                     Alan Jones           Bereave             Bereave-P         2011-11-07 00:00:00:000

00005                     Alan Jones           Bereave             Bereave-P         2011-11-08 00:00:00:000

00005                     Alan Jones           Bereave             Bereave-P         2011-11-09 00:00:00:000

I would like to change my report so that I get the following output:

Employee Number | Employee Name | Absence Group | Absence Code | Absence Start Date            | Absence End Date

00005                     Alan Jones           Bereave             Bereave-P         2011-06-17 00:00:00:000   2011-06-21 00:00:00:000

00005                     Alan Jones           Bereave             Bereave-P         2011-11-07 00:00:00:000   2011-11-07 00:00:00:000

Answer 5

This is an "Islands and Gaps" type problem.  You can google or bing the term for for info on the general problem and solutions.  In this case, I would start by building a Calendar table.  That will greatly simplify determining which days are weekdays and calendar tables are very useful in general.  See

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

for a description  of what a calendar table is, how to build one and some of there many uses.

Once you have one, then you all you need to do is wrap your original query in a cte, add a cte to get the appropriate calendar data, and the final result looks like

-- Build Sample Data
DECLARE @HRMABSENCETRANS TABLE(
	[HRMABSENCETABLEID] [nvarchar](10) NOT NULL DEFAULT (''),
	[HRMABSENCECODEID] [nvarchar](10) NOT NULL DEFAULT (''),
	[TRANSDATE] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'));
Insert @HRMABSENCETRANS(HRMABSENCETABLEID, HRMABSENCECODEID, TRANSDATE) Values
(1, 'Bereave-P', '2011-06-17 00:00:00:000'),
(1, 'Bereave-P', '2011-06-20 00:00:00:000'),
(1, 'Bereave-P', '2011-06-21 00:00:00:000'),
(1, 'Bereave-P', '2011-11-07 00:00:00:000'),
(1, 'Bereave-P', '2011-11-08 00:00:00:000'),
(1, 'Bereave-P', '2011-11-09 00:00:00:000');

Declare @EMPLTABLE Table(EMPLID nvarchar(10), NAME nvarchar(20), PROJPERIODID nvarchar(20));
Insert @EMPLTABLE(EMPLID, NAME, PROJPERIODID) Values('00005', 'Alan Jones', 'Monthly')

Declare @HRMABSENCETABLE Table(EMPLID nvarchar(10), HRMABSENCETABLEID int);
Insert @HRMABSENCETABLE(EMPLID, HRMABSENCETABLEID) Values('00005', 1)

Declare @HRMABSENCECODE Table(HRMABSENCECODEID nvarchar(10), HRMABSENCECODEGROUPID nvarchar(10));
Insert @HRMABSENCECODE(HRMABSENCECODEID, HRMABSENCECODEGROUPID) Values ('Bereave-P', 'Bereave');

Declare @HRMABSENCECODEGROUP Table (HRMABSENCECODEGROUPID nvarchar(10));
Insert @HRMABSENCECODEGROUP(HRMABSENCECODEGROUPID) Values ('Bereave');

DECLARE @EMPLID AS NVARCHAR(10)
DECLARE @ABSENCEDATESTART AS DATETIME
DECLARE @ABSENCEDATEEND AS DATETIME
DECLARE @ABSENCECODE AS NVARCHAR(10)

SET @EMPLID = '00005'
SET @ABSENCEDATESTART = '2010-01-01'
SET @ABSENCEDATEEND = '2012-01-01'
SET @ABSENCECODE = 'Bereave-P'


;With cteCalendar As
(Select c.dt, Row_Number() Over(Order By c.dt) As rn 
From dbo.Calendar c 
Where c.dt Between @ABSENCEDATESTART And @ABSENCEDATEEND And c.isWeekday = 1),
cteAbsense As
-- your original query starts here
(SELECT DISTINCT	a.EMPLID AS [Employee Number], 
		a.NAME AS [Employee Name], 
		e.HRMABSENCECODEGROUPID AS [Absence Group],
		d.HRMABSENCECODEID AS [Absence Code],
		c.TRANSDATE AS [Absence Date],
		Row_Number() Over (Partition By a.EMPLID, d.HRMABSENCECODEID Order By c.TRANSDATE) As rn
FROM	@EMPLTABLE a INNER JOIN
		@HRMABSENCETABLE b ON a.EMPLID = b.EMPLID INNER JOIN
		@HRMABSENCETRANS c ON b.HRMABSENCETABLEID = c.HRMABSENCETABLEID INNER JOIN
		@HRMABSENCECODE d ON c.HRMABSENCECODEID = d.HRMABSENCECODEID INNER JOIN
		@HRMABSENCECODEGROUP e ON d.HRMABSENCECODEGROUPID = e.HRMABSENCECODEGROUPID
WHERE 
a.EMPLID = @EMPLID AND
c.TRANSDATE BETWEEN @ABSENCEDATESTART AND @ABSENCEDATEEND AND
d.HRMABSENCECODEID = @ABSENCECODE AND
a.PROJPERIODID = 'Monthly')
-- your original query ends here
Select a.[Employee Number], a.[Employee Name], a.[Absence Group], a.[Absence Code],
  Min(a.[Absence Date]) As [Absence Start Date], Max(a.[Absence Date]) As [Absence End Date]
From cteCalendar c
Inner Join cteAbsense a On c.dt = a.[Absence Date]
Group By a.[Employee Number], a.[Employee Name], a.[Absence Group], a.[Absence Code], c.rn - a.rn
go

Tom

Answer 6

Cheers Tom. I'll check this out.