Forum Discussion
If 2nd Date/Time Row, Remove Both
I'm pulling data from SQL for facility check-ins. I want only a list of people who are still in the building. In the attached screen shot, stationid 52 is a check-in station and stationid 192 is a ...
edhans Some memid's will check in and out multiple times per day. There shouldn't be anyone checking in one day and checking out the next.
Thanks for this! Some weirdness, though (and both of your solutions provided the exact same results):
- Not all memid’s were removed on scan at check-out stationid 192. See memid 76795. But memid 98033 was removed.
- The checkin date/time is different in the Query than what it actually is. The Table CHKINS is correct.
- Same for the stationid – Query is not correct, but table CHKINS is correct.
- Memid 104958 is not picking up any scan in the Query, but if I look at the raw data, is clearly scanning.
Also, and I apologize for not mentioning this earlier:
- Stationid 187 is an alternate initial entry to the building and should be treated the same as stationid 52.
- Stationid 56 is a secondary scan-in area of the building and should not be treated as a check-out.
In the data I’ve included below, the table names are in all caps in the heading.
| CHKINS checkin | QUERY1 checkin | QUERY1 chkinid | QUERY1 memid | STATIONS stationid | QUERY1 stationid | STATIONS stationname |
| 5/25/2020 7:52:39 AM | 5/21/2020 2:18:26 PM | 5438182 | 97640 | 187 | 52 | Ground Level Entry |
| 5/25/2020 12:08:48 PM | 5/21/2020 2:18:06 PM | 5438179 | 134806 | 52 | 52 | Main Check In |
| 5/25/2020 12:08:56 PM | 5/21/2020 2:18:14 PM | 5438180 | 76795 | 52 | 52 | Main Check In |
| 5/25/2020 12:09:09 PM | 5/21/2020 2:18:20 PM | 5438181 | 103055 | 52 | 52 | Main Check In |
| 5/25/2020 12:59:05 PM | 5/21/2020 2:18:06 PM | 5438179 | 134806 | 56 | 52 | Health & Wellness Check In |
| 5/25/2020 1:12:59 PM | 5/21/2020 2:18:14 PM | 5438180 | 76795 | 192 | 52 | Member Check-Out |