question

LSC avatar image
LSC asked

Seeking help on a query to retrieve the entry and exit times for cardholders

Good afternoon,

I have been wrestling with this query for some time, but keep ending up in the same place.

The aim of the query is determine the time that a cardholder enters a building, followed by the time that same cardholder leaves the building. Additionally, this pair of entry and exit times should be shown on one row.

I have been able to construct the query, but where it falls over is if the cardholder does not tap their card to leave the building (i.e. they tailgate another separate cardholder who is in front of them at the exit point). If this is the case, then there should be a null value for that entry-exit time value pair.

This is the code to create the table that is a representation of the data I am working with:

CREATE TABLE #temptable (

[UniqueID] int,
[DateTimeOfCardTap] datetime,
[Location] nvarchar(255),
[ReaderNumber] int,
[AccessAttemptResult] nvarchar(255),
[CardNumber] int,
[FirstName] nvarchar(40),
[LastName] nvarchar(40)

)

INSERT INTO #temptable (

[UniqueID],
[DateTimeOfCardTap],
[Location],
[ReaderNumber],
[AccessAttemptResult],
[CardNumber],
[FirstName],
[LastName]

)

VALUES
( 32042292, N'2023-05-20T11:22:24', N'Entry', 116, N'Success for [1234567]', 1234567, N'John', N'Another' ),
( 32042474, N'2023-05-20T12:35:30', N'Exit', 117, N'Success for [1234567]', 1234567, N'John', N'Another' ),
( 32042607, N'2023-05-20T13:13:34', N'Entry', 116, N'Success for [1234567]', 1234567, N'John', N'Another' ),
( 32043558, N'2023-05-20T16:24:58', N'Exit', 117, N'Success for [1234567]', 1234567, N'John', N'Another'),
( 32043564, N'2023-05-20T16:34:36', N'Entry', 116, N'Success for [1234567]', 1234567, N'John', N'Another' ),
( 32043835, N'2023-05-20T20:03:34', N'Entry', 116, N'Success for [1234567]', 1234567, N'John', N'Another' ),
( 32043945, N'2023-05-20T21:25:56', N'Exit', 117, N'Success for [1234567]', 1234567, N'John', N'Another')

SELECT * FROM #temptable

-- drop table #temptable

And this is the resulting data in the temporary table:

32042292 2023-05-20 11:22:24.000 Entry 116 Success for [1234567] 1234567 John Another
32042474 2023-05-20 12:35:30.000 Exit 117 Success for [1234567] 1234567 John Another
32042607 2023-05-20 13:13:34.000 Entry 116 Success for [1234567] 1234567 John Another
32043558 2023-05-20 16:24:58.000 Exit 117 Success for [1234567] 1234567 John Another
32043564 2023-05-20 16:34:36.000 Entry 116 Success for [1234567] 1234567 John Another
32043835 2023-05-20 20:03:34.000 Entry 116 Success for [1234567] 1234567 John Another
32043945 2023-05-20 21:25:56.000 Exit 117 Success for [1234567] 1234567 John Another

Notice how rows five and six are both entry card taps, implying that this cardholder left the building between 16:34 and 20:03.

I have constructed this query:

SELECT [EntryTxns].[CardNumber],
   [EntryTxns].[Location] AS [Entry],
   [EntryTxns].[DateTimeOfCardTap] AS [EntryTime],
   (
    SELECT TOP(1) [ExitTxns1].[Location]
    FROM #temptable AS [ExitTxns1]
    WHERE [ExitTxns1].[CardNumber] = [EntryTxns].[CardNumber]
    AND [ExitTxns1].[DateTimeOfCardTap] > [EntryTxns].[DateTimeOfCardTap]
    AND [ExitTxns1].[ReaderNumber] = 117
   ) AS [Exit],
   (
    SELECT TOP(1) [ExitTxns2].[DateTimeOfCardTap]
    FROM #temptable AS [ExitTxns2]
    WHERE [ExitTxns2].[CardNumber] = [EntryTxns].[CardNumber]
    AND [ExitTxns2].[DateTimeOfCardTap] > [EntryTxns].[DateTimeOfCardTap]
    AND [Exittxns2].[ReaderNumber] = 117
   ) AS [ExitTime]
FROM #temptable AS [EntryTxns]

WHERE [EntryTxns].[CardNumber] = 1234567

AND CONVERT(Date, [EntryTxns].[DateTimeOfCardTap]) = '20230520'
AND [EntryTxns].[ReaderNumber] = 116

ORDER BY [EntryTxns].[CardNumber],
         [EntryTxns].[DateTimeOfCardTap] ASC;

Which yields this result:

CardNumber Entry EntryTime               Exit ExitTime
1234567    Entry 2023-05-20 11:22:24.000 Exit 2023-05-20 12:35:30.000
1234567    Entry 2023-05-20 13:13:34.000 Exit 2023-05-20 16:24:58.000
1234567    Entry 2023-05-20 16:34:36.000 Exit 2023-05-20 21:25:56.000
1234567    Entry 2023-05-20 20:03:34.000 Exit 2023-05-20 21:25:56.000

Row three has the same exit time as on row four, where the logically correct result should be this:

CardNumber Entry EntryTime               Exit ExitTime
1234567    Entry 2023-05-20 11:22:24.000 Exit 2023-05-20 12:35:30.000
1234567    Entry 2023-05-20 13:13:34.000 Exit 2023-05-20 16:24:58.000
1234567    Entry 2023-05-20 16:34:36.000 null null
1234567    Entry 2023-05-20 20:03:34.000 Exit 2023-05-20 21:25:56.000

How can I obtain the above result? I have tried using the LEAD function for the exit portion of the code with no success, and even tried using CTEs, with no luck either.

Thanks in advance for your help.

sql query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered

One way to do it would be with LEAD, as you already tried. The solution below assumes that the piggybacking can happen only on exit, not on entry, though it would be easy enough to cover this scenario too if needs to be. The logic is as follows: for each row select the next card swipe (partitioned by card number of course), taking both the location and the time. If all is well then the "next" location will be different from current. If not then the piggybacking is detected. All that is left to do is filter out the rows to include only those where location is entry (now that the exit info is already captured on the same row). Here is the script:

select 
    t.CardNumber, t.[Location] [Entry], t.DateTimeOfCardTap EntryTime, 
    nullif(t.[Location], t.NextLocation) [Exit], 
    case when t.NextLocation = 'Exit' then t.NextTime else null end ExitTime
    from (
        select
            *, 
            lead([Location], 1) over (partition by CardNumber order by DateTimeOfCardTap) NextLocation,
            lead(DateTimeOfCardTap, 1) over (partition by CardNumber order by DateTimeOfCardTap) NextTime
            from #temptable
    ) t
    where t.[Location] = 'Entry'
    order by CardNumber, EntryTime;

By the way, it would be nice if the usage of T-SQL keywords as column names is avoided if at all possible. In this case, Location, Entry, Exit are all keywords, there is no good reason to use them as column names.

Hope this helps,


Oleg

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

LSC avatar image LSC commented ·

Good afternoon Oleg,

Your script hits the nail right on the head; this is exactly what I was after!

I did alter your code ever so slightly, as all locations were being returned as 'Entry', even for the exit card taps:

nullif(t.[Location], t.NextLocation) [Exit],
-- was changed to this:
case when [t].[Location] = [t].[NextLocation] then null else 'Exit' end as [Exit],

And thanks for the advice regarding the usage of T-SQL reserved words. The column names for Location, Entry and Exit in the production database are different - I used the reserved words in the sample code so as to help easily identify the data.

You are correct in that the script does not account for a scenario where the cardholder does not tap in. I did have a stab at modifying your script by using the LAG function in the correlated subquery to obtain the previous card tap (which I have omitted as it exceeds the maximum comment allowance of 1200 words), but fell at the first hurdle and ended up with a hopelessly incorrect result set.

Would you be kind enough to create an amended script that covers both missing entry and exit card taps, please?

Thanks in advance for your help,

Andrew

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.