|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Friday, April 19, 2013 1:40 PM
Points: 434,
Visits: 291
|
|
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:
InId InDate OutId OutDate 15472 2010-06-11 13:05:58.000 15473 2010-06-11 15:45:33.000 15472 2010-06-11 13:05:58.000 14797 2010-03-23 13:31:54.000 14696 2010-03-18 13:11:18.000 15473 2010-06-11 15:45:33.000 14696 2010-03-18 13:11:18.000 14797 2010-03-23 13:31:54.000
What I want to get to is this:
InId InDate OutId OutDate 15472 2010-06-11 13:05:58.000 15473 2010-06-11 15:45:33.000 14696 2010-03-18 13:11:18.000 14797 2010-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
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943,
Visits: 8,227
|
|
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 Kent user group
|
|
|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Friday, April 19, 2013 1:40 PM
Points: 434,
Visits: 291
|
|
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.
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943,
Visits: 8,227
|
|
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 Kent user group
|
|
|
|