Home Forums SQL Server 2005 Development Converting sequential time records into IN and OUT times RE: Converting sequential time records into IN and OUT times

  • March 24, 2014 at 8:25 pm

    #1699716

    pssudarshan (3/24/2014)

    Hi,

    We have a Time & Attendance application that stores the time punched by each employee in a sequential format. There is no indication as to whether the time is an "IN" time or "OUT" time. The application takes the first entry for the Employee /Day to be "IN", the next one to be "OUT" , the 3rd one to be "IN" and so on.

    I want to write a query to export records with the in and out times for each employee per day. How can this be done

    The best way to do it is to have someone modify the time-punch machine to identify the ins and outs.

    The second best way would be to use ROW_NUMBER() with a partition by employee and day... all the odd numbers would be "INs", and the even numbers would be "Outs".

    If you want a coded example, please see the first link under "Helpful Links" in my signature line below.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)