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