Good afternoon,
I have been struggling with this query for quite some time, and it has reached the point where I admit defeat and need to ask for help.
Essentially, the query I am working with deals with ID card transactions from one table, and all relevant entry card taps and exit card taps pertaining to each individual cardholder resides in this table.
What I would like is to display all card taps and time at the entry card reader and its corresponding exit card tap plus time that it took place for each day, with one pair of related entry and exit times on the same row.
There should be a null where there is no corresponding entry or exit card tap.
What I have tried:
The following code creates a table that contains a representation of the data I am working with:
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.#temptable', 'U') IS NOT NULL
DROP TABLE #temptable;
GO
CREATE TABLE #temptable ( [UniqueID] int, [DateTimeOfCardTap] datetime, [CardReaderLocation] nvarchar(255), [ReaderNumber] INT, [AccessAttemptResult] nvarchar(255), [CardNumber] int, [FirstName] nvarchar(40), [LastName] nvarchar(40) )
INSERT INTO #temptable ([UniqueID], [DateTimeOfCardTap], [CardReaderLocation], [ReaderNumber], [AccessAttemptResult], [CardNumber], [FirstName], [LastName])
VALUES
( 32042292, N'2023-05-20T11:22:24', N'In', 116, N'Access granted', 1234567, N'John', N'Another' ),
( 32042474, N'2023-05-20T12:35:30', N'Out', 117, N'Access granted', 1234567, N'John', N'Another' ),
( 32042523, N'2023-05-20T12:43:58', N'Out', 117, N'Access granted', 1234567, N'John', N'Another' ),
( 32042607, N'2023-05-20T13:13:34', N'In', 116, N'Access granted', 1234567, N'John', N'Another' ),
( 32043558, N'2023-05-20T16:24:58', N'Out', 117, N'Access granted', 1234567, N'John', N'Another' ),
( 32043564, N'2023-05-20T16:34:36', N'In', 116, N'Access granted', 1234567, N'John', N'Another' ),
( 32043835, N'2023-05-20T20:03:34', N'In', 116, N'Access granted', 1234567, N'John', N'Another' ),
( 32043945, N'2023-05-20T21:25:56', N'Out', 117, N'Access granted', 1234567, N'John', N'Another' )
And this is the (incorrect) query that interrogates the above table:
SELECT A.CardNumber,
A.[CardReaderLocation],
A.DateTimeOfCardTap,
B.[CardReaderLocation],
B.DateTimeOfCardTap
FROM #temptable AS A
LEFT JOIN (SELECT CardNumber,
DateTimeOfCardTap,
[CardReaderLocation],
ReaderNumber
FROM #temptable) AS B
ON A.CardNumber = B.CardNumber
AND A.DateTimeOfCardTap < B.DateTimeOfCardTap
AND B.ReaderNumber <> B.ReaderNumber
WHERE A.ReaderNumber = 116
The above query returns the following result set:
CardNumber CardReaderLocation DateTimeOfCardTap CardReaderLocation DateTimeOfCardTap
1234567 In 2023-05-20 11:22:24.000 Out 2023-05-20 12:35:30.000
1234567 In 2023-05-20 11:22:24.000 Out 2023-05-20 12:43:58.000
1234567 In 2023-05-20 11:22:24.000 Out 2023-05-20 16:24:58.000
1234567 In 2023-05-20 11:22:24.000 Out 2023-05-20 21:25:56.000
1234567 In 2023-05-20 13:13:34.000 Out 2023-05-20 16:24:58.000
1234567 In 2023-05-20 13:13:34.000 Out 2023-05-20 21:25:56.000
1234567 In 2023-05-20 16:34:36.000 Out 2023-05-20 21:25:56.000
1234567 In 2023-05-20 20:03:34.000 Out 2023-05-20 21:25:56.000
Notice how the time 11:22:24 is repeated across the first four rows and the last exit time of 21:25:56 is repeated for the last three rows.
The desired result set should look like this:
CardNumber CardReaderLocation DateTimeOfCardTap CardReaderLocation DateTimeOfCardTap
1234567 In 2023-05-20 11:22:24 Out 2023-05-20 12:35:30
1234567 In null Out 2023-05-20 12:43:58
1234567 In 2023-05-20 13:13:34 Out 2023-05-20 16:24:58
1234567 In 2023-05-20 16:34:36 Out null
1234567 In 2023-05-20 20:03:34 Out 2023-05-20 21:25:56
There is no entry card tap for the second row with an exit time of 12:43:58, and similarly, there is no corresponding exit time on row four with the entry time of 16:34:36.
What sort of query would achieve the desired result, please? I have tried using CTEs, subqueries as well as usage of the LAG and LEAD functions, but I don't seem to be having much luck.
Here is a variation of the above query using CTEs, based on a YouTube clip posted by the user SQL Server 101:
USE tempdb;
GO
IF OBJECT_ID(N'tempdb.dbo.#temptable') IS NOT NULL
DROP TABLE #temptable
CREATE TABLE #temptable (
[UniqueID] int,
[DateTimeOfCardTap] datetime,
[CardReaderLocation] nvarchar(255),
[ReaderNumber] int,
[AccessAttemptResult] nvarchar(255),
[CardNumber] int,
[FirstName] nvarchar(40),
[LastName] nvarchar(40)
)
INSERT INTO #temptable (
[UniqueID],
[DateTimeOfCardTap],
[CardReaderLocation],
[ReaderNumber],
[AccessAttemptResult],
[CardNumber],
[FirstName],
[LastName]
)
VALUES
( 32042292, N'2023-05-20T11:22:24', N'Ingress', 116, N'Success for [1234567]', 1234567, N'John', N'Another' ),
( 32042474, N'2023-05-20T12:35:30', N'Egress', 117, N'Success for [1234567]', 1234567, N'John', N'Another' ),
( 32042532, N'2023-05-20T12:43:57', N'Egress', 117, N'Success for [1234567]', 1234567, N'John', N'Another' ),
( 32042607, N'2023-05-20T13:13:34', N'Ingress', 116, N'Success for [1234567]', 1234567, N'John', N'Another' ),
( 32043558, N'2023-05-20T16:24:58', N'Egress', 117, N'Success for [1234567]', 1234567, N'John', N'Another'),
( 32043564, N'2023-05-20T16:34:36', N'Ingress', 116, N'Success for [1234567]', 1234567, N'John', N'Another' ),
( 32043835, N'2023-05-20T20:03:34', N'Ingress', 116, N'Success for [1234567]', 1234567, N'John', N'Another' ),
( 32043945, N'2023-05-20T21:25:56', N'Egress', 117, N'Success for [1234567]', 1234567, N'John', N'Another')
SELECT * FROM #temptable
;WITH myTable AS (
SELECT *,
CASE WHEN DATEPART(day, DateTimeOfCardTap) <> LAG(DATEPART(day, DateTimeOfCardTap), 1) OVER (ORDER BY CardNumber)
OR [CardReaderLocation] = 'Ingress' AND LAG([CardReaderLocation], 1) OVER (ORDER BY CardNumber) <> 'Ingress'
THEN 1 ELSE 0 END AS [PartitionStart]
FROM #temptable),
myTable2 AS (
SELECT *, SUM(PartitionStart) OVER (ORDER BY CardNumber) AS [PartitionNumber]
, CASE WHEN [CardReaderLocation] = 'Ingress' THEN DateTimeOfCardTap END AS [InTime]
, CASE WHEN [CardReaderLocation] = 'Egress' THEN DateTimeOfCardTap END AS [OutTime]
FROM myTable)
SELECT CardNumber,
PartitionNumber,
case when [CardReaderLocation] = 'Ingress' then 'Ingress' end as [CardReaderLocation],
InTime AS [InTimeStart],
case when [CardReaderLocation] = 'Egress' then 'Egress' end as [CardReaderLocation],
OutTime AS [OutTimeLast]
FROM myTable2
group by CardNumber, DateTimeOfCardTap, PartitionNumber, CardReaderLocation, InTime, OutTime
The result set obtained from the above query displays each card tap once, which is good, but the bad news is that any entry and corresponding exit card taps are not displayed on one row, and there are too many nulls:
CardNumber PartitionNumber CardReaderLocation InTimeStart CardReaderLocation OutTimeLast
1234567 2 Ingress 2023-05-20 11:22:24.000 NULL NULL
1234567 2 NULL NULL Egress 2023-05-20 12:35:30.000
1234567 2 NULL NULL Egress 2023-05-20 12:43:57.000
1234567 2 Ingress 2023-05-20 13:13:34.000 NULL NULL
1234567 2 NULL NULL Egress 2023-05-20 16:24:58.000
1234567 2 Ingress 2023-05-20 16:34:36.000 NULL NULL
1234567 2 Ingress 2023-05-20 20:03:34.000 NULL NULL
1234567 2 NULL NULL Egress 2023-05-20 21:25:56.000
Thanks in advance for your help with this matter,
AM