Pairing of Records

Transact-SQL
1

Hi have door entry system and I have been asked to provide a report which displays the activity of people entering different building including their duration of stay in that building. the table below displays the raw data.

I have been trying to create a select statement that would display the paired records but my knowledge of sql is limited and its hard to find the a good example. in the table above the pairs are indicated by the colour. A person may enter and exit many time during the day and I want to get the duration as a separate line.

For the table above I would like the records paired like 1234 & 1239, 1235 & 1237, 1238 & 1241 etc. Record 1244 does not have a matching entry. Record 1240 & 1243 the person enter on 1 day and leaves the next.

For my output I would ideally like to get the following:-
ID IDEntry IDExit StaffNo EntryDate ExitDate Duration Building Department Status
1 1234 1239 567845 2016-08-05 08:15:56:345 2016-08-05 16:20:43:235 8hrs 5mins 54 32 Matched
2 1235 1235 1235 2016-08-05 08:20:34:360 2016-08-05 12:00:03:563 3hrs 40mins 20 43 Matched
3 1238 1241 453127 2016-08-05 13:10:33:260 2016-08-05 21:53:32:460 8hrs 43mins 20 43 Matched
3 1240 1243 234124 2016-08-05 17:20:23:151 2016-09-05 13:08:23:294 20hrs 26mins 30 33 Matched
4 1244 567845 2016-09-05 16:14:22:245 54 32 Orphaned

some criteria which I know will help - there must be and entry with an exit, the id will be sequential, the exit time is after the entry time etc.

I my not sure yet if I need to put the result into another table so if possible the select may need to be rapped in a insert. I have tried group by with counts but I found it matched the wrong entry exits

Many Thanks in advance

2

Can you try this code

select a.IDEntry, min(b.IDExit) as IDExit
    , a.StaffNo, a.EntryDate, min(b.ExitDate) as ExitDate
    , datediff('HH', a.EntryDate, min(b.ExitDate)) as Duration
    , a.Building, a.Department
    , case when min(b.IDExit) is null then 'Orphaned' else Matched' end as Status
from (select ID as IDEntry, StaffNo, activitydate as EntryDate, department, building 
    from table where entryexit = 'entry') as a
left outer join (select ID as IDExit, StaffNo, activitydate as ExitDate, department, building 
            from table where entryexit = 'exit') as b
on a.StaffNo = b.StaffNo
and a.IDEntry< b.IDExit
group by a.IDEntry,a.StaffNo, a.EntryDate, a.Building, a.Department
1 Like
3

Hi viggneshwar,

Thank you very much for this this worked fine.

Can I request 1 changes -

  • I have 2 additional tables that contain images the 1st is table contains a close up image of the person the second contains and a overview image of the person. Each image has the original ID of the activity is its ID please can the images be include in the select.

again many thanks in advance