Home Forums SQL Server 2005 T-SQL (SS2K5) First Absence of the Week in a Monthly Pay Period RE: First Absence of the Week in a Monthly Pay Period

  • 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