How do I gather clock-in and clock-out card taps into one row and include nulls for missing entries?

Question

5.00/5 (1 vote)

See more:

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

Posted 19-Jun-23 6:23am

AndyMch

Updated 21-Jun-23 0:53am

v6

Add a Solution

Comments

[no name] 19-Jun-23 12:32pm

I'd need a lot more info to come up with a feasible solution, like: shift hours for each employee; "location"; number of times one can "clock in or out" during a shift; etc. Then I would make an attempt at matching "ins to outs" and "outs to ins", and flagging anything in between.

AndyMch 19-Jun-23 17:14pm

Good evening Gerry,

Thank you for the prompt response.

It's just one entrance and one exit that I need to worry about, thankfully. There are no shift times that need to be taken into account.

There are no restrictions on the amount of times one can clock in or out, either.

Please let me know if you require any more information.

Kind regards,

Andy

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2023-06-21T00:53:00

This is a variation of the classic "Islands and Gaps" problem, in this case we have gaps in the expected pattern of In, Out, In, Out, In etc etc.

To solve this I chose to create a "working table" which I first populated with the data available. Note I am also capturing the DateTimeOfCardTap as realdata, of which more later...

SQL

IF OBJECT_ID('tempdb.dbo.#worktable', 'U') IS NOT NULL
DROP TABLE #worktable;
GO
CREATE TABLE #worktable ( [CardNumber] int, [CardReaderLocation] nvarchar(255), [DateTimeOfCardTap] datetime, realdata datetime);
GO
insert into #worktable 
select [CardNumber], [CardReaderLocation], [DateTimeOfCardTap], [DateTimeOfCardTap] from #temptable;

I then used the LAG Window function to identify the gaps - these would be where the previous row's CardReaderLocation is the same as the current row. I spoofed the time of this imaginary event by taking away one second from the actual DateTimeOfCardTap. I insert these records into my working table

SQL

with Q as
(
	select [CardNumber]
		,[CardReaderLocation]
		,[DateTimeOfCardTap] 
		,LAG([CardReaderLocation]) over (partition by [CardNumber] order by [DateTimeOfCardTap]) as Prev
	from #temptable
)
insert into #worktable
select [CardNumber] 
		,CASE WHEN [CardReaderLocation] = 'In' THEN 'Out' ELSE 'In' END as [CardReaderLocation]
		,DATEADD(SECOND, -1, [DateTimeOfCardTap]) as [DateTimeOfCardTap]
		,null
from Q
where CardReaderLocation = Prev

My working table now looks like this

CardNumber	Location	DateTimeOfCardTap	realdata
1234567		In			2023-05-20 11:22:24	2023-05-20 11:22:24
1234567		Out			2023-05-20 12:35:30	2023-05-20 12:35:30
1234567		In			2023-05-20 12:43:57	NULL
1234567		Out			2023-05-20 12:43:58	2023-05-20 12:43:58
1234567		In			2023-05-20 13:13:34	2023-05-20 13:13:34
1234567		Out			2023-05-20 16:24:58	2023-05-20 16:24:58
1234567		In			2023-05-20 16:34:36	2023-05-20 16:34:36
1234567		Out			2023-05-20 20:03:33	NULL
1234567		In			2023-05-20 20:03:34	2023-05-20 20:03:34
1234567		Out			2023-05-20 21:25:56	2023-05-20 21:25:56

To get your expected results you could pivot this information or do the following

SQL

with Q1 as
(
	select [CardNumber]
	,'In' as [CardReaderLocation1]
	, [realdata] as [DateTimeOfCardTap1]
	,'Out' as [CardReaderLocation2]
	, LEAD([realdata],1) OVER (partition by [CardNumber] order by [DateTimeOfCardTap]) as [DateTimeOfCardTap2]
	, ROW_NUMBER() OVER (partition by [CardNumber] order by [DateTimeOfCardTap]) AS rn
	from #worktable
)
select [CardNumber]
	,[CardReaderLocation1],[DateTimeOfCardTap1]
	,[CardReaderLocation2],[DateTimeOfCardTap2]
from Q1
where rn % 2 = 1
order by rn;

Note the column names have 1 and 2 appended - if you try to use the same name you will get an error

Quote:
The column 'DateTimeOfCardTap' was specified multiple times for 'Q1'.

EDIT: The code in full

SQL

-- Create a working table and populate it with available data
IF OBJECT_ID('tempdb.dbo.#worktable', 'U') IS NOT NULL
DROP TABLE #worktable;
GO
CREATE TABLE #worktable ( [CardNumber] int, [CardReaderLocation] nvarchar(255), [DateTimeOfCardTap] datetime, realdata datetime);
GO
insert into #worktable 
select [CardNumber], [CardReaderLocation], [DateTimeOfCardTap], [DateTimeOfCardTap] from #temptable;

-- Fill in the gaps in the data
with Q as
(
	select [CardNumber]
		,[CardReaderLocation]
		,[DateTimeOfCardTap] 
		,LAG([CardReaderLocation]) over (partition by [CardNumber] order by [DateTimeOfCardTap]) as Prev
	from #temptable
)
insert into #worktable
select [CardNumber] 
		,CASE WHEN [CardReaderLocation] = 'In' THEN 'Out' ELSE 'In' END as [CardReaderLocation]
		,DATEADD(SECOND, -1, [DateTimeOfCardTap]) as [DateTimeOfCardTap]
		,null
from Q
where CardReaderLocation = Prev;

-- Extract the data in the required format
with Q1 as
(
	select [CardNumber]
	,'In' as [CardReaderLocation1]
	, [realdata] as [DateTimeOfCardTap1]
	,'Out' as [CardReaderLocation2]
	, LEAD([realdata],1) OVER (partition by [CardNumber] order by [DateTimeOfCardTap]) as [DateTimeOfCardTap2]
	, ROW_NUMBER() OVER (partition by [CardNumber] order by [DateTimeOfCardTap]) AS rn
	from #worktable
)
select [CardNumber]
	,[CardReaderLocation1],[DateTimeOfCardTap1]
	,[CardReaderLocation2],[DateTimeOfCardTap2]
from Q1
where rn % 2 = 1
order by rn;