Post reply

Need to retrieve the bottom record for each employee

  • June 20, 2011 at 11:39 pm

    #252220

    I have a database of employee checks which has 4 relevant fields (ck#,CK Date, Employid, Ck amount). For each employee I want to retrieve the record that has the last "CK date".

    Thanks

  • June 20, 2011 at 11:55 pm

    #1341612

    Hope this helps. As you provided nothing to develop the code against I give no promises that it will work as you desire.

    with EmpChecks (

    CheckNumber,

    CheckDate,

    EmployeeId,

    CheckAmount,

    rownum

    ) as (

    select

    CheckNumber,

    CheckDate,

    EmployeeId,

    CheckAmount,

    row_number() over (order by EmployeeId asc, CheckDate desc partition by EmployeeId)

    from

    dbo.EmployeeChecks

    )

    select

    CheckNumber,

    CheckDate,

    EmployeeId,

    CheckAmount

    from

    EmpChecks

    where

    rownum = 1

    order by

    EmployeeId;

  • June 21, 2011 at 12:12 am

    #1341624

    Sorry - here is some sample data

    EmpID CK# CKDate Amount

    1 100 10/1/2010 $3500

    1 120 11/1/2010 $3500

    1 130 12/15/2010 $4000

    2 140 10/1/2010 $3500

    2 150 11/1/2010 $3500

    2 160 12/1/2010 $4000

    2 170 12/15/2010 $3500

    3 180 11/1/2010 $3700

    3 190 12/1/2010 $4100

    Essentially for each employee I want to retrieve the check that has the latest date.

    Thanks

  • June 21, 2011 at 12:15 am

    #1341628

    Code provided, you test it.

    It's late and I have to get up early for work this morning.

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

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