Comments by AndyMch (Top 5 by date)

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

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

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

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