Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

First Absence of the Week in a Monthly Pay Period Expand / Collapse
Author
Message
Posted Monday, September 27, 2010 1:09 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 63, Visits: 245
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
Post #993944
Posted Monday, September 27, 2010 2:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 6,582, Visits: 8,859
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #994012
Posted Monday, September 27, 2010 8:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 63, Visits: 245
Thank you Wayne, I'll give it a go.

Much appreciate it.

Best Regards,

ZA
Post #994155
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse