locked
Get IN/OUT Records RRS feed

  • Question

  • User-1319178603 posted

    Hi,

    I have a table with some data like,

    Name               DateTime

    John                 1/1/2017  8:45am ---In

    John                 1/1/2017  10:00am ---Out

    John                 1/1/2017   15:00pm ----In

    Andrew             1/1/2017   9:00am ---In

    Samantha          1/1/2017   9:15am ---In

    Andrew              1/1/2017   15:30pm --Out

    Samantha           1/1/2017   18:00pm --Out

    Based on the above records, i wish to separate the IN and OUT rows and insert into a temp table like this,

                            Odd                                     Even

    John               1/1/2017   8:45am                 1/1/2017  10:00am

    John               1/1/2017   15:00pm                NULL

    Andrew           1/1/2017   9:00am                 15:30pm

    Samantha        1/1/2017   9:15am                 18:00pm

    How can I achieve this using a simple ms sql query

    Thanks

    hahsm 

    Saturday, September 23, 2017 7:21 AM

All replies

  • User452040443 posted

    Hi,

    Try something like this:

    with CTE_RN as
    (
        select
            *,
            ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY [DateTime]) as RN
        from MyTable
    )
    
    select
        o.[Name],
        o.[DateTime] as [Odd],
        e.[DateTime] as [Even]
    from CTE_RN as o
    left join CTE_RN as e
        on 
            e.[Name] = o.[Name] and 
            e.[RN] = o.[RN] + 1
    where
        o.[RN] % 2 = 1

    Hope this help.

    Sunday, September 24, 2017 5:11 PM