How do I display clock-in and corresponding clock-out card taps on one row?

Transact-SQL
1

Good morning,

I have been scratching my head over this query for quite some time, and it has reached the point where I am just going in circles and have to finally admit defeat and ask for help.

The query that I am working with contains ID card transactions from one table, with all entry and exit card taps pertaining to each individual cardholder residing in this table.

What I would like is to display card taps and the timestamp from the entry card reader and its corresponding exit card tap plus timestamp, 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, and there exists just one point of entry and one point of exit.

There is no restriction on the number of times that someone can enter or leave the building, and there is no requirement to enforce shift patterns.

Here is the code that creates a temporary table containing a representation of the data I am working with:

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')

I would like the data to be returned looking 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 on 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, which is logically correct, as this person tailgated another person who was in front of them where the are nulls are recorded.

I have had a go at trying to create the query, and the closest that I was able to get to was by using the code as posted by a user on YouTube called SQL Server 101:

;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,
	   --cardreaderlocation,
	   case when [CardReaderLocation] = 'Ingress' and PartitionStart = 0 then 'Ingress' end as [CardReaderLocation],
	   InTime AS [InTimeStart], 
	   case when [CardReaderLocation] = 'Egress' and PartitionStart = 0 then 'Egress' end as [CardReaderLocation],
	   OutTime AS [OutTimeLast]
FROM myTable2
group by CardNumber, DateTimeOfCardTap, PartitionNumber, PartitionStart, CardReaderLocation, InTime, OutTime

Which returns this result set (apologies for the non-aligned formatting):

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	NULL	2023-05-20 13:13:34.000	NULL	NULL
1234567	2	NULL	NULL	Egress	2023-05-20 16:24:58.000
1234567	2	NULL	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

It's very close to what I would like to achieve in that all card taps appear just the once, but the entry and corresponding exit card taps do not line up on one row, and there are too many nulls where the correct number should be two (a null for the entry card tap on row two, and a null for the exit card tap on row four).

What sort of query would achieve the data returned in the first result set?

Thank you very much in advance for your help,

Andy

2

You first need to add the missing rows, then you can do what you want. eg

WITH PrevNext
AS
(
	SELECT CardNumber, UniqueID, DateTimeOfCardTap, CardReaderLocation
		,LAG(CardReaderLocation) OVER (PARTITION BY CardNumber ORDER BY UniqueID) AS PreCardReaderLocation
		,LEAD(CardReaderLocation) OVER (PARTITION BY CardNumber ORDER BY UniqueID) AS NextCardReaderLocation
	FROM #temptable
)
,AddMissingRows
AS
(
	SELECT CardNumber, UniqueID, DateTimeOfCardTap, CardReaderLocation
	FROM #temptable
	UNION ALL
	SELECT CardNumber, UniqueID, NULL
		,CASE
			WHEN CardReaderLocation = 'Ingress'
			THEN 'Egress'
			ELSE 'Ingress'
		END
	FROM PrevNext
	WHERE CardReaderLocation = 'Egress' AND PreCardReaderLocation <> 'Ingress'
		OR CardReaderLocation = 'Ingress' AND NextCardReaderLocation <> 'Egress'
)
,NextTimes
AS
(
	SELECT CardNumber, UniqueID, DateTimeOfCardTap, CardReaderLocation
		,LEAD(DateTimeOfCardTap) OVER (PARTITION BY CardNumber ORDER BY UniqueID, CardReaderLocation DESC) AS NextDateTimeOfCardTap
	FROM AddMissingRows
)
SELECT CardNumber
	,DateTimeOfCardTap AS InTime
	,NextDateTimeOfCardTap AS OutTime
FROM NextTimes
WHERE CardReaderLocation = 'Ingress';
1 Like
3

Good afternoon Ifor,

Your solution does exactly what it says on the tin!

I have been struggling with this problem for nearly two months. I know that it was a 'kinda sorta' gaps and islands problem with nulls thrown into the mix, but the exact query remained elusive to me.

A gentleman and a scholar.

Kind regards,

Andy