Performance of SQL query

Performance of SQL query

I have to query a table with few millons of rows and I want to do it the
most optimized.
Lets supose that we want to controll the access to a movie theater with
multiples screening rooms and save it like this: AccessRecord (TicketId,
TicketCreationTimestamp, TheaterId, ShowId, MovieId, SeatId,
CheckInTimestamp)
To simplify, the 'Id' columns of the data type 'bigint' and the
'Timestamp' are 'datetime'. The tickets are sold at any time and the
people access to the theater randomly. And the primary key (so also
unique) is TicketId.
I want to get for each Movie and Theater and Show (time) the AccessRecord
info of the first and last person who accessed to the theater to see a
mov. If two checkins happen at the same time, i just need 1, any of them.
My solution would be to concatenate the PK and the grouped column in a
subquery to get the row:
select
AccessRecord.*
from
AccessRecord
inner join(
select
MAX(CONVERT(nvarchar(25),CheckInTimestamp, 121) +
CONVERT(varchar(25), tickets)) as MaxKey,
MIN(CONVERT(nvarchar(25),CheckInTimestamp, 121) +
CONVERT(varchar(25), tickets)) as MinKey
from
AccessRecord
group by
MovieId,
TheaterId,
ShowId
) as MaxAccess
on CONVERT(nvarchar(25),CheckInTimestamp, 121) + CONVERT(varchar(25),
tickets) = MaxKey
or CONVERT(nvarchar(25),CheckInTimestamp, 121) + CONVERT(varchar(25),
tickets) = MinKey
The conversion 121 is to the cannonical expression of datatime resluting
like this: aaaa-mm-dd hh:mi:ss.mmm(24h), so ordered as string data type it
will give the same result as it is ordered as a datetime.
As you can see this join is not very optimized, any ideas?