CREATE TABLE #Calendar(Dt DATE NOT NULL PRIMARY KEY CLUSTERED, N INT NOT NULL IDENTITY(1,1));

DECLARE @startDate DATE = '20140101', @endDate DATE = '20141231';
;WITH cte(Dt ) AS
(
	SELECT @startDate
	UNION ALL
	SELECT DATEADD(dd,CASE WHEN DATEDIFF(dd,0,Dt)%7 = 4 THEN 3 ELSE 1 END,Dt) FROM cte
	WHERE Dt < @endDate
)
INSERT INTO #Calendar SELECT Dt FROM cte OPTION (MAXRECURSION 0);

CREATE TABLE #tmp(EMP_ID int, absence_date DATE);
INSERT INTO #tmp VALUES (1,'20140602'),	(1,'20140603'),	(1,'20140604'),	(1,'20140605'),	(1,'20140606'),(1,'20140609'),
(2,'20140602'),	(2,'20140603'),	(2,'20140605'),	(2,'20140606'),(2,'20140609')	


;WITH cte AS
(
	SELECT
		*,
		N-ROW_NUMBER() OVER (PARTITION BY Emp_id ORDER BY absence_date) AS GroupNum
	FROM
		#Calendar c
		INNER JOIN #tmp T ON T.absence_date = c.Dt
),
cte2 AS
(
	SELECT *,COUNT(*) OVER (PARTITION BY Emp_id,GroupNum) AS ConsecutiveAbsences
	FROM cte
)
SELECT * FROM cte2 WHERE ConsecutiveAbsences >= 5

CREATE TABLE #Calendar(Dt DATE NOT NULL PRIMARY KEY CLUSTERED, isWeekend BIT, isHoliday BIT );

-- all the days
DECLARE @startDate DATE = '20140101', @endDate DATE = '20141231';
;WITH cte(Dt ) AS
(
	SELECT @startDate
	UNION ALL
	SELECT DATEADD(dd,1,Dt) FROM cte
	WHERE Dt < @endDate
)
INSERT INTO #Calendar SELECT Dt,0,0 FROM cte OPTION (MAXRECURSION 0);

-- mark weekends
UPDATE #Calendar SET isWeekend = 1 WHERE DATEDIFF(dd,0,Dt)%7 IN (5,6);

-- also mark any other day you want to indicate as holidays for example:
UPDATE #Calendar SET isHoliday = 1
WHERE Dt IN 
	('20140101',  -- New year
	'20141225')   -- Christmas
	
-- now use the #calendarTable like this:

;WITH WorkingDays AS
(
	SELECT Dt,
			ROW_NUMBER() OVER (ORDER BY Dt) AS N
	 FROM #Calendar WHERE isWeekend=0 AND isHoliday = 0
)
--- rest of your query here, but use the WorkingDays instead of #Calendar.

....
cte2 AS
(
	SELECT *,COUNT(*) OVER (PARTITION BY Emp_id,GroupNum) AS ConsecutiveAbsences,
	ROW_NUMBER() OVER(PARTITION BY  Emp_id,GroupNum ORDER BY absence_date ASC) AS N_ASC,
	ROW_NUMBER() OVER(PARTITION BY  Emp_id,GroupNum ORDER BY absence_date DESC) AS N_DESC
	
	FROM cte
)
SELECT * FROM cte2 WHERE ConsecutiveAbsences >= 5
AND (N_ASC=1 OR N_DESC=1);

....
cte2 AS
(
	SELECT *,COUNT(*) OVER (PARTITION BY Emp_id,GroupNum) AS ConsecutiveAbsences
	FROM cte
)
SELECT Emp_id,COUNT(*), MAX(absence_date), MIN(absence_date)
FROM cte2 
GROUP BY Emp_id,GroupNum
HAVING ConsecutiveAbsences >= 5;

....
cte2 AS
(
SELECT *, COUNT(*) OVER (PARTITION BY PERSONNUM,GroupNum) AS ConsecutiveAbsences
FROM cte1
),
cte3 AS
(
	SELECT PERSONNUM, COUNT(*) DaysAbsent, MAX(absence_date) LastDayAbsent, MIN(absence_date) FirstDayAbsent
	FROM cte2 
	GROUP BY Emp_id,GroupNum
	HAVING ConsecutiveAbsences >= 5
)

SELECT Distinct (c.PERSONNUM)as Employee_ID, p.PERSONFULLNAME, HOMELABORLEVELNM3 as location, c.DaysAbsent AS ConsecutiveAbsences 
FROM cte3 c, dbo.VP_ALLPERSONV42 p 
WHERE c.PERSONNUM = p.PERSONNUM
order by location, c.PERSONNUM