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