Click here to Skip to main content
15,883,883 members
Please Sign up or sign in to vote.
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
Updated 21-Jun-23 0:53am
v6
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

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;
 
Share this answer
 
v2
Comments
AndyMch 21-Jun-23 7:15am    
Good afternoon CHill60,

Thank you very much for helping out; I appreciate the response.

I have run the Q1 CTE in the answer you supplied, and I got this result set:

1234567 In 2023-05-20 11:22:24.000 Out 2023-05-20 12:35:30.000 < correct
1234567 In 2023-05-20 12:43:57.000 Out 2023-05-20 13:13:34.000 < should be a null for entry, and 12:43:57 should be an exit card tap
1234567 In 2023-05-20 16:24:58.000 Out 2023-05-20 16:34:36.000 < previous exit should be entry, and entry time on this row (16:24:58) should be exit
1234567 In 2023-05-20 20:03:34.000 Out 2023-05-20 21:25:56.000 < correct

I was after this result:

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 < null here as cardholder did not tap in
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 < null here as cardholder did not tap out
1234567 In 2023-05-20 20:03:34 Out 2023-05-20 21:25:56

I am about to run the first query that you supplied, and will get back to you once I figure out how to pivot the results.

Thanks again,

Andy
CHill60 21-Jun-23 7:23am    
You have to run all of the queries not just one by one. When I tested this I got exactly the results you said you wanted in your post.
CTE Q1 will not work unless you have also run CTE Q
AndyMch 21-Jun-23 7:37am    
Hi CHill60,

Ah okay, my apologies!

If I run the following code:

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

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;

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

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;

Then SSMS complains about incorrect syntax:

(8 rows affected)
Msg 102, Level 15, State 1, Line 68
Incorrect syntax near 'Q1'.

Completion time: 2023-06-21T12:32:01.2648510+01:00

It's probably expecting a comma to separate the two CTEs, so I add it:

,Q1 as

And after re-running the script it fails with a different error message:

(8 rows affected)
Msg 102, Level 15, State 1, Line 68
Incorrect syntax near ','.

Completion time: 2023-06-21T12:35:24.9412404+01:00

Q1 is on line 68.

Could you tell me what I am doing wrong, please?

Kind regards,

Andy
CHill60 21-Jun-23 9:04am    
Nope - it was expecting ;with in front of Q1
(my bad - I missed the ; off the previous code snippet)
AndyMch 21-Jun-23 9:23am    
Hi CHill60,

Thanks for getting back to me.

Do you mean adding changing the leading comma to a semi-colon, like this?

;Q1 as

I have tried that too, with the following result:

(8 rows affected)
Msg 102, Level 15, State 1, Line 68
Incorrect syntax near 'Q1'.

Completion time: 2023-06-21T14:20:16.8348634+01:00

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900