I have a table that contains the following data:
ID Panel Date Source Event Event Type Card Number Card Alternate Number / CHUID Last Name First Name Message Notes External System ID
2 2022-03-28 11:00:11.000 Entrance Gate Granted Access Valid Credential 156733 NULL Phillips Jamie NULL NULL NULL
24 2022-03-28 14:00:25.000 Engineering Office Access Not Granted Invalid Credential 156733 NULL Phillips Jamie NULL NULL NULL
So far i've been able to write a query that pulls the first and last transaction for every date available in the table:
use TEST_DB
SELECT --[Panel Date],
--[Source],
--[Event],
--[Event Type],
[First Name],
[Last Name],
[Card Number],
[Card Alternate Number / CHUID],
--[Message],
--[Notes],
--[External System ID],
CONVERT(date, [Panel Date]) as [Event Date],
MIN([Panel Date]) as [First Transaction],
ISNULL(MAX([Panel Date]), DATEADD(MS, -2, CONVERT(DATETIME,DATEADD(D, 1, CONVERT(date, [Panel Date]))))) as [Last Transaction]
FROM ACEvents
where [card number] > 1
GROUP BY CONVERT(date, [Panel Date]), [Card Number], [Card Alternate Number / CHUID], [First Name], [Last Name], [Event], [Event Type]
ORDER BY CONVERT(date, [Panel Date]), [First Name], [Last Name], [Card Number], [Card Alternate Number / CHUID]
Which produces the following output:
First Name Last Name Card Number Card Alternate Number / CHUID Event Date First Transaction Last Transaction
Jamie Phillips 156733 NULL 2022-03-28 2022-03-28 11:00:11.000 2022-03-28 14:00:25.000
What I would like to achieve is logging the event transaction details for the first and last events of each day:
First Name Last Name Card Number Card Alternate Number / CHUID Event Date First Transaction Source Event Event Type Last Transaction Source Event Event Type
Jamie Phillips 156733 NULL 2022-03-28 2022-03-28 11:00:11.000 Entrance Gate Granted Access Valid Credential 2022-03-28 14:00:25.000 Engineering Office Access Not Granted Invalid Credential
Any help would be greatly appreciated