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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Absent 5 Business Days in a Row

Author  Topic 

Bill Z
Starting Member

27 Posts

Posted - 2014-06-09 : 15:02:41
I’m a frequent reader and browser but never felt I was good enough to offer a suggestion. I just have not been doing SQLs long enough.

Also, now, can’t seem to find a question and solution that resembles my particular problem of finding who has been absent 5 business days in a row. The table I have to work with has an entry for every absence (excused or not). Each entry has the day of an absence, the employee number and a code for excuses.

The two or more dates I have to compare are on different rows.

Using T-SQL 2008, the SQL query will be run every day after punches are posted against the table that holds the history of every absence for the past 9 years.

Somehow, I have to compare all of the absent dates for an employee and put them on a report if any of the absences they have are 5 business days or more along with the dates they missed. Yes, business days.

A sample of my data looks like this:
EMP_ID ABSENCE_DATE EXCUSE_CD
1875 2014-05-28 00:00:00.000 0
1875 2014-05-29 00:00:00.000 0
1875 2014-06-02 00:00:00.000 0
1875 2014-06-03 00:00:00.000 0
1875 2014-06-04 00:00:00.000 0
1875 2014-06-05 00:00:00.000 0
1875 2014-06-06 00:00:00.000 0
1875 2014-06-09 00:00:00.000 0
1879 2014-05-19 00:00:00.000 0
1879 2014-05-26 00:00:00.000 0
1879 2014-06-09 00:00:00.000 0
1899 2014-05-26 00:00:00.000 0
1899 2014-06-06 00:00:00.000 0
1903 2014-05-26 00:00:00.000 0
1903 2014-06-09 00:00:00.000 0
1915 2014-05-26 00:00:00.000 0
1915 2014-06-06 00:00:00.000 0
1915 2014-06-09 00:00:00.000 0
1918 2014-05-26 00:00:00.000 0
1918 2014-06-06 00:00:00.000 0
1918 2014-06-09 00:00:00.000 0
1921 2014-05-26 00:00:00.000 0
1921 2014-06-06 00:00:00.000 0
1921 2014-06-09 00:00:00.000 0
1928 2014-05-09 00:00:00.000 0
1928 2014-05-26 00:00:00.000 0
1928 2014-06-06 00:00:00.000 0
1970 2014-05-26 00:00:00.000 0
1970 2014-06-02 00:00:00.000 0
1970 2014-06-03 00:00:00.000 0
1970 2014-06-04 00:00:00.000 0
1970 2014-06-05 00:00:00.000 0
1970 2014-06-06 00:00:00.000 0
Using the above data, employee 1875 would have 6 consecutive days absent and employee 1970 would have 5 consecutive days absent. None of the others need be on the report.

Please Help.


_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-09 : 16:13:31
You would need a Calendar table that lists your holidays and weekends to do this computation. One could try to construct the logic to figure out the holidays and weekends, but it simply easier to create a calendar table. If you don't have one, and assuming you want to consider only weekends, create one like this:
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);
Once you have the calendar table, the following is an example of one way to do this computation. There are other ways, but most often the method I am showing below is the most efficient.
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
By the way, when you post a question, if you create the sample tables like I did above, that makes it easier for someone to respond and so you are likely to get faster and better responses.

If you want to consider non-weekend holidays, you will need to insert those into your calendar table as well.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-10 : 11:48:58
If you're going to use that specific method, you must be extremely accurate on how you load rows into your calendar table!
Go to Top of Page

Bill Z
Starting Member

27 Posts

Posted - 2014-06-10 : 13:29:27
Scott, Do you suggest another method of identifying weekends?

I'm still setting up the code to build the calendar table.

Just for fun and enlightenment, I am going to set up 2 calendars. One with just weekends an holidays (short list) and the other with only working days (long list). I plan on using joining then appropriately.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-10 : 13:45:11
To Scott's point, the method absolutely depends on the correct ordering of the dates. One way to make sure that is taken into account correctly is to do the numbering on the fly. Set up a calendar table that has all the days, have another column to indicate weekends, and perhaps another to indicate weekday holidays. Like this:
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.
Go to Top of Page

Bill Z
Starting Member

27 Posts

Posted - 2014-06-11 : 08:02:06
Seems there was some syntax issues that had to be overcome but it sort of works. It did find the 2 employees with absences over 5 business days. But I was looking for just 2 rows. What listed were each absence dates. I am looking for a summary of employee numbers and number of days absent. I added this at the end.

SELECT Distinct (PERSONID), ConsecutiveAbsences FROM cte2 WHERE ConsecutiveAbsences >= 5
order by PERSONID

Works now.

Thanks.

There seems to be a filter here at work and it will not let me post my code. Sorry.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-11 : 11:08:07
Filter out the rows that you don't want, e.g.
....
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);
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-11 : 11:10:28
or if you want the employee number and count,
....
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
;
Go to Top of Page

Bill Z
Starting Member

27 Posts

Posted - 2014-06-11 : 13:13:01
Here is the part of the code that loads up the temp calendar and how I match the dates to the absent days.
-- mark weekends
UPDATE #Calendar SET isWeekend = 1 WHERE DATEDIFF(dd,0,Dt)%7 IN (5,6);

-- mark days you want to indicate as holidays:
UPDATE #Calendar SET isHoliday = 1
WHERE Dt IN
('20140101', -- New year
'20141225', -- Christmas
'20141127', -- THANKSGIVING THURSDAY
'20141128', -- THANKSGIVING FRIDAY
'20140922', -- COLUMBUS DAY
'20140901', -- LABOR DAY
'20140704', -- INDEPENDANCE DAY
'20140703', -- INDEPENDANCE DAY
'20140526'); -- MEMORIAL DAY

-- 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
),
absence as (
SELECT PERSONNUM, PERSONID, ABSENCEDATE, EXCUSEDSW
FROM dbo.VP_ABSENCE
Where ABSENCEDATE > '2014-06-01 00:00:00.000'
and EXCUSEDSW = 0
--and PERSONNUM >= '18875' -- just gives a sub set to
--and PERSONNUM <= '18970' -- Semplify debugging
),

cte1 AS (
SELECT *, N-ROW_NUMBER() OVER (PARTITION BY PERSONNUM ORDER BY ABSENCEDATE) AS GroupNum
FROM WorkingDays, absence
where ABSENCEDATE = Dt

),

cte2 AS
(
SELECT *, COUNT(*) OVER (PARTITION BY PERSONNUM,GroupNum) AS ConsecutiveAbsences
FROM cte1
)

SELECT Distinct (c.PERSONNUM)as Employee_ID, p.PERSONFULLNAME, HOMELABORLEVELNM3 as location, c.ConsecutiveAbsences
FROM cte2 c, dbo.VP_ALLPERSONV42 p
WHERE c.PERSONNUM = p.PERSONNUM
and ConsecutiveAbsences >= 5
order by location, c.PERSONNUM

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-11 : 15:25:44
That looks like it would still give you multiple rows. What you want is probably this:
....
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
If you need to, you can add the FirstDayAbsent and LastDayAbsent as well to the final select.
Go to Top of Page
   

- Advertisement -