First Absence of the Week in a Monthly Pay Period

Question

Post reply

First Absence of the Week in a Monthly Pay Period

DiabloSlayer

SSCrazy

Points: 2232
More actions
September 27, 2010 at 1:09 pm

#241017

Hello Everyone,
I need some help in creating a SQL query to identify ONLY the first absence of the week in a monthly pay period so when I do my query for an employee for a month I should be able to flag it:
Week starts on Monday
Month is September 2010 (for example sake)
My table is called emp_absence and the columns are as follows:
Emp_No, Absence, AbsenceDate, PayPeriodStartDate, PayPeriodEndDate
Note: Absence column value '0' means no absence and '1' means absence
Example Scenario:
On first week an employee has an absence on Thursday (9/2/2010) and Friday (9/3/2010).
On second week this employee has an absence on Monday (9/6/2010).
On third week no absence.
On fourth week he has an absence on Tuesday (9/21/2010)and Friday (9/24/2010).
On fifth week no absence.
SQL Query
select Emp_No, Absence, AbsenceDate,
CASE Absence
WHEN 0 THEN
No_Absence_In_The_Week'
WHEN 1 THEN
'First_Absence_Of_The_Week'
END Absence_Flag
from emp_absence
where absencedate between PayPeriodStartDate and PayPeriodEndDate
and Absence = '1'
Expected Results:
Emp_No AbsenceDate Absence_Flag
--------------- ----------- ----------------------- -------------------------
Test, Emp 09-02 2010 First_Absence_Of_The_Week
Test, Emp 09-06 2010 First_Absence_Of_The_Week
Test, Emp 09-21 2010 First_Absence_Of_The_Week
Right now when I run this query it picks up all the absences in a week which is incorrect.
I appreciate all the help I can get to have a working query.
Best Regards,
ZA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

How's this?

-- See how this starts off with a table and data in it?

-- If you had provided us the data in this format,

-- it would have made things easier for all of the

-- volunteers on this site to help you out.

DECLARE @emp_absence TABLE (

Emp_No INT,

Absence BIT,

AbsenceDate DateTime,

PayPeriodStartDate DateTime,

PayPeriodEndDate DateTime);

INSERT INTO @emp_absence (Emp_No, Absence, AbsenceDate)

SELECT 1, 1, '9/2/2010' UNION ALL

SELECT 1, 1, '9/3/2010' UNION ALL

SELECT 1, 1, '9/6/2010' UNION ALL

SELECT 1, 1, '9/21/2010' UNION ALL

SELECT 1, 1, '9/24/2010';

WITH CTE AS

(

-- get the week #, and row number that starts over at 1 for each change in emp_no or week #

SELECT *,

WeekNo = DATEPART(week, AbsenceDate),

RN = ROW_NUMBER() OVER (PARTITION BY Emp_No, DATEPART(week, AbsenceDate) ORDER BY AbsenceDate)

FROM @emp_absence

)

-- only get the first absence of each week.

SELECT *

FROM CTE

WHERE RN = 1;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 2

Thank you Wayne, I'll give it a go.

Much appreciate it.

Best Regards,

ZA