Match up sequential records

  • Hi folks,

    I am trying to match up pairs of records in a dataset that denote times at which people logged in and out of a system.

    The issue is the dataset doesn't include any fields which can be used to link pairs of records together. All that is available is a record id and a date/time.

    I therefore need to examine the data and pair up the 'in' and 'out' records based on the date/time field.

    Here is a sample of the data:

    InIdInDateOutIdOutDate

    154722010-06-11 13:05:58.000154732010-06-11 15:45:33.000

    154722010-06-11 13:05:58.000147972010-03-23 13:31:54.000

    146962010-03-18 13:11:18.000154732010-06-11 15:45:33.000

    146962010-03-18 13:11:18.000147972010-03-23 13:31:54.000

    What I want to get to is this:

    InIdInDateOutIdOutDate

    154722010-06-11 13:05:58.000154732010-06-11 15:45:33.000

    146962010-03-18 13:11:18.000147972010-03-23 13:31:54.000

    Here is my current code:

    SELECT MovementIn.movementId AS InId,

    MovementIn.date AS InDate,

    MovementOut.movementId AS OutId,

    MovementOut.date AS OutDate

    FROM dbo.DeviceMovement AS dm

    INNER JOIN dbo.Movement AS MovementIn

    ON dm.movementId = MovementIn.movementId

    LEFT OUTER JOIN (SELECT dm.deviceId,

    m.movementId,

    m.date

    FROM dbo.DeviceMovement dm

    INNER JOIN dbo.Movement m

    ON dm.movementId = m.movementId

    WHERE dm.deviceId = 22001

    AND m.sourceLocationId = 99) AS MovementOut

    ON dm.deviceId = MovementOut.deviceId

    --AND MovementIn.date < MovementOut.date

    WHERE dm.deviceId = 22001

    AND MovementIn.destinationLocationId = 99

    Please note that I am not able to modify the schema I am working against in this instance.

    TIA,

    Chris

  • untested but should be what you are after...

    with cteDeviceMovements

    as

    (

    SELECT dm.deviceId,

    m.movementId,

    m.date,

    Row_number() over (partition by dm.deviceId order by m.movementId)%2 as Rown

    FROM dbo.DeviceMovement dm

    INNER JOIN dbo.Movement m

    ON dm.movementId = m.movementId

    WHERE m.sourceLocationId = 99

    )

    Select *

    from cteDeviceMovements cross apply

    (Select top(1) * from cteDeviceMovements InnerDM

    where InnerDM.DeviceId = cteDeviceMovements.DeviceId

    and InnerDM.movementId > cteDeviceMovements.movementId) as NextDM

    where Rown = 1

    If you cant get it to work , please post full DDL and data in the form of INSERT's , thanks



    Clear Sky SQL
    My Blog[/url]

  • Dave,

    Nice solution. With a couple of tweaks to show the fields I need in the resultsret, that works perfectly.

    Thanks very much for your assistance. 🙂

  • Good stuff,

    just be sure sure that you understand the assumptions made in the code.

    an out will always follow an in

    the source data will always start with an in.



    Clear Sky SQL
    My Blog[/url]

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

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